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 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: 10201
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: 1278
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
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 SQL Query optimization. arunsoods DB2 12 Sun Sep 04, 2016 6:27 am
No new posts Query on BLSR Nileshkul JCL & VSAM 2 Sat Aug 13, 2016 5:18 am


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