I have 2 tables: TAB1 incliudes PRIMARY_PROD_NO and ALT_PROD_NO
TAB2 has PROD_NO (as KEY) and PROD_DESCRIPTION.

How do I code a select statement that will give the result set of:

PRIMARY_PROD_NO, description, ALT_PROD_NO, Description?

Eachh Description relating to each Prod_no.

thank you
IPPOPP

Hi,

From what I can say it's a simple select, however to put a proper answer we need the tables and if there is a relation.

What I suggest is you use the sql assist function in the command center to help you to the needful.

Please post the above details and we can give you the sql statement.

Regards

Nitin.

Hi

Have you tried:

SELECT PRIMARY_PROD_NO, A.description, ALT_PROD_NO,
B.Description
FROM TAB1
INNER JOIN TAB2 A
ON PRIMARY_PROD_NO = A.PROD_NO
INNER JOIN TAB2 B
ON ALT_PROD_NO = B.PROD_NO

Another version:

SELECT PRIMARY_PROD_NO, (SELECT DESCRIPTION FROM TAB2 WHERE PROD_NO=PRIMARY_PROD_NO) AS PRIMARY_DESCRIPTION, ALT_PROD_NO, (SELECT DESCRIPTION FROM TAB2 WHERE PROD_NO=ALT_PROD_NO) AS ALT_DESCRIPTION FROM TAB1

Hi adam_ac

Your version is not possible for DB2 z/OS up to Version 7. V8 supports this syntax, but only if the select in the select list returns a scalar value.