Home » SQL & PL/SQL » SQL & PL/SQL » SQl Query
SQl Query [message #198104] Sat, 14 October 2006 23:20 Go to next message
masskuth
Messages: 5
Registered: July 2006
Junior Member
I am trying to find a solution in SQL for a table as below


I am looking to get the result in such a way that if there is any record in 'CORR_ACC_NO' with ITEM_QUEUE = 'C' then list all the records having the same order id and and any other records in the same MATCH_NO.


I know I can do it in PL/SQL. I would like to know if there is any possibilities in using SQL.

CORR_ACC_NO,VALUE_DATE,ORDER_ID,AMOUNT,MATCH_NO,ITEM_QUEUE,ITEM_STATUS,LS_TYPE
1002,09-OCT-06,9000,1500,1,N,O,S
1002,09-OCT-06,9559,1550,0,C,O,L
1002,08-OCT-06,9559,1000,1,N,O,L
1003,08-OCT-06,9559,1000,2,N,O,L
1003,08-OCT-06,9550,1000,3,N,O,L
1003,08-OCT-06,9551,1000,0,C,O,L
1003,08-OCT-06,9551,1000,4,N,O ,L



So I would like to get the result as follow. Since there is a record with ITEM_QUEUE = 'C' in CORR_ACC_NO 1002 and 1003

CORR_ACC_NO,VALUE_DATE,ORDER_ID,AMOUNT,MATCH_NO,ITEM_QUEUE,ITEM_STATUS,LS_TYPE
1002,09-OCT-06,9000,1500,1,N,O,S
1002,08-OCT-06,9559,1000,1,N,O,L
1003,08-OCT-06,9551,1000,4,N,O,L

Please let me know .

Thanks
Raks
Re: SQl Query [message #198122 is a reply to message #198104] Sun, 15 October 2006 07:37 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Try a WHERE order_id IN () sub-query.

Ross Leishman
Re: SQl Query [message #198146 is a reply to message #198104] Sun, 15 October 2006 19:11 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
the_nemi wrote on Mon, 16 October 2006 01:50
try distinct clause on the value_date column.

--Vikram.

Look, I know my advice was pretty brief (I was trying to impart the gift of research on the OP) but at least it wasn't random.

How exactly does a DISTINCT value_date help?

Ross Leishman
Re: SQl Query [message #198154 is a reply to message #198104] Sun, 15 October 2006 20:50 Go to previous messageGo to next message
masskuth
Messages: 5
Registered: July 2006
Junior Member
Hi,

Thanks for reply but if I do the query

select * from item where order_id in (select order_id from item where item_queue = 'C') I will get the following records

CORR_ACC_NO,VALUE_DATE,ORDER_ID,AMOUNT,MATCH_NO,ITEM_QUEUE,ITEM_STATUS,LS_TYPE
1002,08-OCT-06,9559,1000,1,N,O,L
1003,08-OCT-06,9551,1000,4,N,O,L


But I would also like to get the first record from the table as you see the MATCH_NO of that record is same as the MATCH_NO from the result of the above query.

CORR_ACC_NO,VALUE_DATE,ORDER_ID,AMOUNT,MATCH_NO,ITEM_QUEUE,ITEM_STATUS,LS_TYPE
1002,09-OCT-06,9000,1500,1,N,O,S
1002,08-OCT-06,9559,1000,1,N,O,L
1003,08-OCT-06,9551,1000,4,N,O,L


Please let me know

Thanks





[Updated on: Sun, 15 October 2006 20:52]

Report message to a moderator

Re: SQl Query [message #198160 is a reply to message #198154] Sun, 15 October 2006 21:59 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Sooooo, nest it another level

WHERE match_id IN (
      SELECT ...
      ...
      WHERE order_id IN (...)
)


Ross Leishman
Previous Topic: multiple NOT LIKE clause's
Next Topic: change columns into rows n rows into columns
Goto Forum:
  


Current Time: Sun Dec 11 06:06:13 CST 2016

Total time taken to generate the page: 0.11621 seconds