How to get consecutive record number in two select statement

[visweswaran2830]

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

[OraMaster]

Try executing this.

select row_number() over(order by invdtl.sl_no) srno,invdtl.fyear_code,invdtl.inv_no,invdtl.inv_date,invdtl.ature,
invdtl.inv_total,invdtl.bal_amount,invdtl.client_code,invdtl.amt_received
from (select row_number() over(order by b.inv_no) as sl_no,b.fyear_code,b.inv_no,b.inv_date,b.service_n ature,
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_n ature,
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)) invdtl