IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Need some idea on how get a query


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
rajiv rengasamy

New User


Joined: 24 Sep 2008
Posts: 26
Location: Chennai

PostPosted: Fri Jan 17, 2014 4:01 pm
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
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
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: 275
Location: Mumbai

PostPosted: Mon Jan 20, 2014 12:48 pm
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
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

Moderator Emeritus


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

PostPosted: Tue Jan 21, 2014 7:28 pm
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts RC query -Time column CA Products 3
No new posts Dynamically pass table name to a sele... DB2 2
No new posts Query on edit primary command CLIST & REXX 5
No new posts Query on edit primary command CLIST & REXX 1
No new posts Issue with EXEC CICS QUERY SECURITY c... CICS 6
Search our Forums:

Back to Top