0

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

3
Contributors
2
Replies
3
Views
7 Years
Discussion Span
Last Post by tesuji
0

Hi,

Row_number will give the consecutive numbers. But in this case, Union will always merge the results, so you will not get the sequential numbers.

To get this done, you can modify the query with combining the tables such as fn_receipt a, trn_invoice_history b, mst_expenditureinvoice c and mst_invoice in a single query (without union operator) something like below;

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, mst_invoice d 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) and (d.fyear_code=b.fyear_code and b.inv_no=d.inv_no)

The above query may not perfect (i believe you may need to use Outer joins). You can try this way.

Note:
Always use Code-tags while posting the queries.

Thank you.

Edited by mike_2000_17: Fixed formatting

0

Hello

I am not sure whether even and odd series of row numbers could help:

select -1+2*row_number() over (order by ID1 ASC) as rownumber, 'First Set' as whichSet, colA as "Data" from rowTest
union
select 2*row_number() over (order by ID2 DESC) as rownumber, 'Second Set' as whichSet, colB as "Data" from rowTest
order by rownumber;
/* result:
rownumber  whichSet    Data
---------------------------
1          First Set   51
2          Second Set  27
3          First Set   10
4          Second Set  25
5          First Set   20
6          Second Set  26
*/

In first select -1+2*row_number() calculates the odd series: 1, 3, 5 etc. 2*row_number() in 2nd select makes series 2, 4, 6 etc. Where the order by rownumber put them into series 1, 2, 3 etc.

Is that what you mean by consecutive record number?

(Note: If I change -1+2*row_number() to 2*row_number()-1 Sybase DB says there is illegal char '-' after (), I don't why. But -1+2*row_number() works fine.)

Addition: Instead of calculating even and odd series also this is possible by adding 1+count(*) of first set to row_number() of second set:

/* result:
rownumber  whichSet    Data
---------------------------
1          First Set   51
2          First Set   10
3          First Set   20
4          Second Set  27
5          Second Set  25
6          Second Set  26*/

-- tesu

Edited by tesuji: n/a

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.