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
 

 

Need some idea on how get a query

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

New User


Joined: 24 Sep 2008
Posts: 10
Location: Chennai

PostPosted: Fri Jan 17, 2014 4:01 pm    Post subject: Need some idea on how get a query
Reply with quote

Hi ,
Have a table of the below layout and for every plant there is a JAG and a TRD row.I need to find that site that has only JAG row and no TRD row.


Plant Div Code Colum1 Column2

AAAA JAG 123 456
AAAA TRD 345 789
BBBB JAG 567 1ab
BBBB TRD 890 ab4
CCCC JAG 12a xyz
CCCC TRD 34b 458
DDDD JAG 567b 406

So i my qurey should get the row DDDD (Site value).

I did this by using to qureys in a program
1))Get the JAG site from table
2)Check if there is already a TRD equivalent for this site in table for that site.

I am curious if this can be achieved in a single query please let know if any of you got some idea.

Thank you.
Back to top
View user's profile Send private message

saiprasadh

Active User


Joined: 20 Sep 2006
Posts: 154
Location: US

PostPosted: Sat Jan 18, 2014 4:29 am    Post subject:
Reply with quote

Rajiv,

Below query will satisfy your requirement.

Code:
select * from
table1 a
where
div_code = 'JAG'
and not exists
(
select 1 from
table1 b
where
    a.plant=b.plant
and b.div_code='TRD')
Back to top
View user's profile Send private message
sureshpathi10

Active User


Joined: 03 May 2010
Posts: 154
Location: Kuala Lumpur

PostPosted: Mon Jan 20, 2014 8:44 am    Post subject:
Reply with quote

Rajiv,

You may try this as well.

Code:

SELECT *
FROM TABLE A, TABLE B
WHERE A.PLANT = B.PLANT AND A.DIV = 'JAG' AND B.DIV <> 'TRD'
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Mon Jan 20, 2014 12:48 pm    Post subject:
Reply with quote

Hi Suresh,

I doubt your query will work as expected.

Your query will return the plant AAAA as well which is not required as per expected output?

Correct me if I am wrong.


Regards,
Chandan
Back to top
View user's profile Send private message
sureshpathi10

Active User


Joined: 03 May 2010
Posts: 154
Location: Kuala Lumpur

PostPosted: Mon Jan 20, 2014 1:11 pm    Post subject:
Reply with quote

Yes Chandan. Thanks for find out. It will fetch all 'JAG' records....
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Jan 21, 2014 7:28 pm    Post subject:
Reply with quote

Hello,

Before posting a "solution" you need to make sure it does what is requested - not just something you chose to do.
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
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
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts Problem in Running Query via JCL vickey_dw DB2 3 Tue Oct 18, 2016 11:11 pm
No new posts DB2 SQL help/idea/suggestion bshkris DB2 4 Thu Oct 06, 2016 3:04 pm
No new posts SQL Query optimization. arunsoods DB2 12 Sun Sep 04, 2016 6:27 am


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