Prev: debug sql, Watch, Command Window, see cotent
Next: Need Help for Query on Summing Prev Row Value and used for calculation
From: visweswaran28 via SQLMonster.com on 3 Aug 2010 01:19
Hi,

select row_number() over(order by b.inv_no) as sl_no,b.fyear_code,b.inv_no,b.
inv_date,b.service_nature,b.inv_total,c.bal_amount,b.client_code,b.
amt_received from fn_receipt a, trn_invoice_history b, mst_expenditureinvoice
c where a.client_id=b.client_code and a.client_name=b.client_name and a.
location=b.client_location and a.receiptno=35 and b.fully_recived not in ('Y')
and b.amt_balance>0 and (c.fyear_code=b.fyear_code and b.inv_no=c.inv_no)
union
select row_number() over(order by b.inv_no) as sl_no,b.fyear_code,b.inv_no,b.
inv_date,b.service_nature,b.inv_total,c.bal_amount,b.client_code,b.
amt_received from fn_receipt a, trn_invoice_history b, mst_invoice c where a.
client_id=b.client_code and a.client_name=b.client_name and a.location=b.
client_location and a.receiptno=35 and b.fully_recived not in ('Y') and b.
amt_balance>0 and (c.fyear_code=b.fyear_code and b.inv_no=c.inv_no)
Above query will return set of values. Now I want to get consecutive record
number. How can I achieve

--
Message posted via http://www.sqlmonster.com

From: John Bell on 3 Aug 2010 02:27
On Tue, 03 Aug 2010 05:19:49 GMT, "visweswaran28 via SQLMonster.com"
<u60654(a)uwe> wrote:

>select row_number() over(order by b.inv_no) as sl_no,b.fyear_code,b.inv_no,b.
>inv_date,b.service_nature,b.inv_total,c.bal_amount,b.client_code,b.
>amt_received from fn_receipt a, trn_invoice_history b, mst_expenditureinvoice
>c where a.client_id=b.client_code and a.client_name=b.client_name and a.
>location=b.client_location and a.receiptno=35 and b.fully_recived not in ('Y')
>and b.amt_balance>0 and (c.fyear_code=b.fyear_code and b.inv_no=c.inv_no)
>union
>select row_number() over(order by b.inv_no) as sl_no,b.fyear_code,b.inv_no,b.
>inv_date,b.service_nature,b.inv_total,c.bal_amount,b.client_code,b.
>amt_received from fn_receipt a, trn_invoice_history b, mst_invoice c where a.
>client_id=b.client_code and a.client_name=b.client_name and a.location=b.
>client_location and a.receiptno=35 and b.fully_recived not in ('Y') and b.
>amt_balance>0 and (c.fyear_code=b.fyear_code and b.inv_no=c.inv_no)
>Above query will return set of values. Now I want to get consecutive record
>number. How can I achieve

Try:


SELECT row_number() over(order by inv_no) as sl_no,

fyear_code,inv_no,inv_date,service_nature,inv_total,bal_amount,client_code,amt_received
FROM (
select b.fyear_code,b.inv_no,b.inv_date,
b.service_nature,b.inv_total,
c.bal_amount,b.client_code,b.amt_received
from fn_receipt a
INNER JOIN trn_invoice_history b
ON a.client_id=b.client_code
and a.client_name=b.client_name
AND a.location=b.client_location
and a.receiptno=35
and b.fully_recived <> 'Y'
and b.amt_balance>0
INNER JOIN mst_expenditureinvoice c
ON (c.fyear_code=b.fyear_code
and b.inv_no=c.inv_no)
UNION
select b.fyear_code,b.inv_no,b.inv_date,
b.service_nature,b.inv_total,
c.bal_amount,b.client_code,b.amt_received
from fn_receipt a
INNER JOIN trn_invoice_history b
ON a.client_id=b.client_code
and a.client_name=b.client_name
and a.location=b.client_location
and a.receiptno=35
and b.fully_recived <> 'Y'
and b.amt_balance>0
INNER JOIN mst_invoice c
ON (c.fyear_code=b.fyear_code and b.inv_no=c.inv_no)
) a

John
From: Eric Isaacs on 3 Aug 2010 02:42
UNION the two sets first in a CTE, then do the ROW_NUMBER when
selecting from that CTE.

http://msdn.microsoft.com/en-us/library/ms190766.aspx

-Eric Isaacs
 | 
Pages: 1
Prev: debug sql, Watch, Command Window, see cotent
Next: Need Help for Query on Summing Prev Row Value and used for calculation