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
 

 

Distinct vendor number from 2 tables

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

New User


Joined: 10 Dec 2010
Posts: 96
Location: Massachusetts

PostPosted: Fri Feb 18, 2011 9:20 pm    Post subject: Distinct vendor number from 2 tables
Reply with quote

Code:

SELECT DISTINCT B.LEGACY_VEND_NR           
  FROM DAAVEND.VENDOR A,                   
       DAAVEND.PAYEE  B                     
  WHERE B.LEGACY_VEND_NR = A.LEGACY_VEND_NR;


I am trying to get just one vendor number from 2 tables. If it exists in just one table I need that in the result set. If the vendor number appears in both tables I just need one in the result set.
I don't think the code above will do that. I think he will just pick the ones in the PAYEE table.
We run Version 9 Release 1.
There is another application I will need this process for and once I get this licked I can wrassle the next one down no problem. Thanks !
Back to top
View user's profile Send private message

don.leahy

Active Member


Joined: 06 Jul 2010
Posts: 654
Location: Whitby, ON, Canada

PostPosted: Fri Feb 18, 2011 9:37 pm    Post subject:
Reply with quote

Your query as written will return a value only if both tables contain the same vendor number.

An Outer Join should do the trick. Or a Union.
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Fri Feb 18, 2011 9:50 pm    Post subject:
Reply with quote

Hi Martin,
It sounds like you are just looking for that Vendor number and it doesn't matter in which table it is found. In that case, you can something like this,

Code:
SELECT LEGACY_VEND_NR           
  FROM DAAVEND.VENDOR
Union
SELECT LEGACY_VEND_NR
  FROM DAAVEND.PAYEE


Thanks,
Sushanth
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Fri Feb 18, 2011 9:54 pm    Post subject:
Reply with quote

basic SQL : UNION


"Mainframe Skills: DB2, IDMS, SAP, Oracle " ? I don't think so
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 Identify top 30 big tables or indexes... ashek15 DB2 0 Fri Jun 16, 2017 10:01 am
No new posts SMTP the current generation number of... Jyothi Kulunde JCL & VSAM 4 Thu May 04, 2017 4:08 pm
No new posts Edit large number of datasets (QSAM) zh_lad TSO/ISPF 3 Tue Apr 04, 2017 6:08 pm
No new posts how to mask the phone number kumarinfy DB2 4 Mon Apr 03, 2017 5:23 pm
No new posts Data replication from multiple Db2 ta... kishpra DB2 9 Mon Mar 27, 2017 9:58 pm


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