Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Need one-one query

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

New User


Joined: 04 Oct 2005
Posts: 31

PostPosted: Wed Mar 20, 2013 4:35 pm    Post subject: Need one-one query
Reply with quote

I have 2 Db2 tables. I need Query for One- one values. Data is given below

Input table 1:
-------------
Code:
Order               Order                   Prodcut
No                    Type                    No
------------         ------------           -------------------
2001                      1                        201
2002                      1                        201
2003                      1                        202
2004                      1                        203
2005                      1                        204
2006                      1                        205
2007                      1                        205


Input table 2:
-------------
Code:
Order No       Order Type        Product Name
----------        ------------         -------------------
2001                      1                       ART
2002                      1                       GRT
2003                      1                       MRT
2004                      1                       ABT
2005                      1                       CMT
2006                      1                       NPT
2007                      1                       DCP

Output should be like this
--------------------------

Code:
Order No       Order Type         Product No                Product Name
----------        ------------          -------------------        ----------------
2003                   1                        202                       MRT
2004                   1                        203                       ABT
2005                   1                        204                       CMT
Back to top
View user's profile Send private message

Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Wed Mar 20, 2013 6:21 pm    Post subject:
Reply with quote

I've edited your code to add the BBCode Tags, hopefully the alignment is what you wanted to show. Done that, please learn to use BBcode tags, next time you post something which need that.

About your question, possibly a JOIN and simple WHERE will help here - but why 2001, 2006 and 2007 were dropped from 'output' - because the third column was repeating in value for them in table 1?
Back to top
View user's profile Send private message
Keanehelp

New User


Joined: 27 May 2008
Posts: 70
Location: USA, CA.

PostPosted: Wed Mar 20, 2013 6:21 pm    Post subject:
Reply with quote

Not sure if I understood correctly.. Is it the exact Output you want it's just an xample...

I would could like

Select
A.Order No,
A.Order Type,
A.Product No,
B.Product Name

From Table 1 A, Table 2 B

Where A.Order No = B.Order No And
A.Oder Type = B.order Type


It will give you Output Like


2001 1 201 ART
2002 1 201 GRT
2003 1 202 MRT
2004 1 203 ABT
2005 1 204 CMT
2006 1 205 NPT
2007 1 205 DCP
Back to top
View user's profile Send private message
Keanehelp

New User


Joined: 27 May 2008
Posts: 70
Location: USA, CA.

PostPosted: Wed Mar 20, 2013 6:23 pm    Post subject:
Reply with quote

Sorry, I did not notice the date of the Post.... icon_redface.gif
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Wed Mar 20, 2013 6:26 pm    Post subject:
Reply with quote

Keanehelp wrote:
Sorry, I did not notice the date of the Post.... icon_redface.gif
What's wrong in that? Though the timestamp for our posts match but thread is current!
Back to top
View user's profile Send private message
naveensrimf

New User


Joined: 04 Oct 2005
Posts: 31

PostPosted: Wed Mar 20, 2013 7:13 pm    Post subject:
Reply with quote

My Output have only these values

2003 1 202 MRT
2004 1 203 ABT
2005 1 204 CMT
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10375
Location: italy

PostPosted: Wed Mar 20, 2013 7:20 pm    Post subject: Reply to: Need one-one query
Reply with quote

so You gave a visual rule ...
translate that to an algorithmic one icon_evil.gif
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Wed Mar 20, 2013 7:27 pm    Post subject:
Reply with quote

Possibly this what OP is looking for
Anuj Dhawan wrote:
About your question, possibly a JOIN and simple WHERE will help here - but why 2001, 2006 and 2007 were dropped from 'output' - because the third column was repeating in value for them in table 1?
but OP has not confirmed it yet.
Back to top
View user's profile Send private message
naveensrimf

New User


Joined: 04 Oct 2005
Posts: 31

PostPosted: Thu Mar 21, 2013 11:56 am    Post subject: Reply to: Need one-one query
Reply with quote

if product Number is repeated then no need to consider 2001, 2006 and 2007 records.

Could you please give same query for this.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Thu Mar 21, 2013 2:29 pm    Post subject:
Reply with quote

If 'Repeating' means occurs more than 1 time
Code:
From
(Select Product_no from Tab1 group by Product_no having count(*) = 1) X
join Tab1 A on A.product_no = X.product_no
join Tab2 B on A.Order_No   = B.Order_No and A.Order_Type = B.order_Type

or
Code:
From Tab1 A
join Tab2 B on A.Order_No   = B.Order_No and A.Order_Type = B.order_Type
where not exists
(Select Product_no from Tab1 X where X.product_no = A.product_no and
   not (X.Order_No   = A.Order_No and X.Order_Type = A.order_Type )
   )
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 query to fetch record which has only ... maxsubrat DB2 12 Mon Dec 11, 2017 5:03 pm
No new posts How to query MAXGENS of an existing P... Chuchulo Hatyzak JCL & VSAM 4 Wed Nov 22, 2017 10:23 pm
No new posts Native SQL Query kishpra DB2 1 Wed Nov 22, 2017 8:38 pm
No new posts Query on IEFBR14 with GDG Ashishpanpaliya JCL & VSAM 4 Tue Nov 07, 2017 8:34 pm
No new posts Query on XMITIP abdulrafi All Other Mainframe Topics 1 Wed Oct 25, 2017 6:54 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us