Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Help Needed in this Query

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
itjagadesh

New User


Joined: 05 Dec 2006
Posts: 89
Location: chennai

PostPosted: Fri Dec 12, 2008 8:13 am    Post subject: Help Needed in this Query
Reply with quote

Hi Team,
i have following requirement below,

Table1 contains invoiceid and date.
Table2 contains invoiceid and status.
Requirement --
Requirement is to match invoiceid in TABLE1 with TABLE2 and STATUS CODE =' A' AND STATUS CODE NOT EQUAL TO 'C ' OR 'D'

TABLE1

INVOICE ID DATE
1 2000
2 2001
4 2002

TABLE2

INVOICE ID STATUSCODE

1 A
1 B

2 A
2 B
2 C

3 A
3 B
3 D

4 A
4 D

OUTPUT
INVOICE ID DATE
1 2000

In above example,for invoice id '2' and '4' , status code = 'C' or 'D'.So it should not come in output.

can anyone please give me the query

Thanks
Jagadesh
Back to top
View user's profile Send private message

Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2180
Location: @my desk

PostPosted: Fri Dec 12, 2008 8:43 am    Post subject:
Reply with quote

itjagadesh,

This might be of some help to you(untested).

Code:
SELECT T1.INVOICE-ID,T1.DATE
  FROM TABLE1 T1
      ,TABLE2 T2
 WHERE T1.INVOICE-ID = T2.INVOICE-ID
   AND NOT EXISTS
       (SELECT 1
          FROM TABLE2 T3
         WHERE T3.INVOICE-ID = T1.INVOICE-ID
           AND T3.STATUSCODE IN ('C','D'))
Back to top
View user's profile Send private message
itjagadesh

New User


Joined: 05 Dec 2006
Posts: 89
Location: chennai

PostPosted: Fri Dec 12, 2008 9:14 am    Post subject: Reply to: Help Needed in this Query
Reply with quote

Hi Arun,

Thanks for your quick response.
But my requirement is to select STATUS CODE = 'A' also .

Requirement --
Requirement is to match invoiceid in TABLE1 with TABLE2 and STATUS CODE =' A' AND STATUS CODE NOT EQUAL TO 'C ' OR 'D'


Thanks
Jagadesh
Back to top
View user's profile Send private message
abhishekmdwivedi

New User


Joined: 22 Aug 2006
Posts: 95
Location: india

PostPosted: Fri Dec 12, 2008 9:27 am    Post subject: Reply to: Help Needed in this Query
Reply with quote

Hi,

I think below code will result in the desired output : ( Ustested )

Code:

Select a.invoice_id,a.invoice_dt from table1 a, table2 b
where a.invoice_id = b.invoice_id
      and b.invoice_st = 'A'
      and not exists (Select 1 from table2 c
                      where a.invoice_id = c.invoice_id
                            and c.invoice_st in ('C','D'));
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2180
Location: @my desk

PostPosted: Fri Dec 12, 2008 9:43 am    Post subject:
Reply with quote

Quote:
Thanks for your quick response.
But my requirement is to select STATUS CODE = 'A' also .
I missed it. But you can modify it to add that condition.
Code:
SELECT T1.INVOICE-ID,T1.DATE
  FROM TABLE1 T1
      ,TABLE2 T2
 WHERE T1.INVOICE-ID = T2.INVOICE-ID
   AND T2.STATUSCODE = 'A'
   AND NOT EXISTS
       (SELECT 1
          FROM TABLE2 T3
         WHERE T3.INVOICE-ID = T1.INVOICE-ID
           AND T3.STATUSCODE IN ('C','D'))
Back to top
View user's profile Send private message
jitu852

New User


Joined: 30 Apr 2007
Posts: 27
Location: Hyderabad

PostPosted: Sun Dec 14, 2008 11:12 pm    Post subject: Reply to: Help Needed in this Query
Reply with quote

Hi,
if iam right no need of sub query it is simply
select columns,
from table1 t1,table2 t2
where t1.column1 =t2.column1
and t2.status = 'a'
Back to top
View user's profile Send private message
Rajesh Midde99

New User


Joined: 04 Dec 2008
Posts: 11
Location: Bangalore,India

PostPosted: Mon Dec 15, 2008 3:47 pm    Post subject: Reply to: Help Needed in this Query
Reply with quote

Hi

It will be help ful to others if the topic initiator responds if he is satisfied with the response and let every body know if the query is working or not ?? Please close the query with a response.

Regards
Rajesh
Back to top
View user's profile Send private message
itjagadesh

New User


Joined: 05 Dec 2006
Posts: 89
Location: chennai

PostPosted: Mon Dec 15, 2008 4:01 pm    Post subject: Reply to: Help Needed in this Query
Reply with quote

Hi All,
Thanks for your help.
Arun Query working fine.

Rajesh Sorry i was out of office.Not able to reply.


Thanks a lot to all.
Jagadesh
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Join in SQL Query vickey_dw DB2 2 Tue Jan 17, 2017 12:15 am
This topic is locked: you cannot edit posts or make replies. MQ setup query mohitsaini Java & MQSeries 4 Sun Jan 08, 2017 1:53 am
No new posts DB2 query Using Recursion, Converting... smilewithashu2 DB2 1 Tue Jan 03, 2017 12:50 pm
No new posts SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us