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
1 1874
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
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Adam Nemitoff |
last post by:
Is is possible to construct a SELECT statement that contains a WHERE
clause that uses the value from a column in the "next" row?
ie. given a table with a single field named "myField" with the...
|
by: ghasem |
last post by:
Dear mySQL community,
I have once again turned to the user groups for a problem I cannot
solve myself! Atually, I have read all I can from the newsgroups on
this but I could not solve it myself....
|
by: Irwinsp |
last post by:
Hi All,
I have a subform where the user can enter data. I would like a field to
default to the record number. How can I do this? (It can not be an auto
number field for other reasons.). Thanks....
|
by: John Ortt |
last post by:
> I have a table of dates in ascending order but with varying intervals. I
> would like to create a query to pull out the date (in field 1) and then
pull
> the date from the subsequent record...
|
by: chaticathe |
last post by:
I am trying to determine the record number of a record in Oracle Forms so I can navigate to that record. I am using the code below but it only gives me the record number of the record I am currently...
|
by: indrag91 |
last post by:
hai,
I want to get the record number in access using select statement. please help. Thanks in advance.
indra.G
|
by: anthonyjm |
last post by:
I need to run a report for each record returned from a SQL Select statement. The report changes for each record, so they need to be run separately.
So something like this (I have no idea what the...
|
by: BlackMustard |
last post by:
hi all,
i am currently using the following union select statement to select records from two of my tables to a gridview in asp.net:
SELECT ConcertName AS Name, ConcertDate AS Date, ConcertTime AS...
|
by: kxyz |
last post by:
Hello everyone,
I need help with a stored procedure or two.
My stored procedures are supposed to check if a certain record exists. If it does exist, then I select everything from that row, as...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |