altquark
Legendary Poster
Maybe some mega-sql-heads will help me with a query I have !
I know this works in SQL Server and Oracle - I am trying (without much success and with very overheated brain) to work out how to convert the following SQL Statement into DB2/400 terms :
Select count(*) from CRPDTA/F4102 where
(ibitm,
iblitm,
iblitm,
ibstkt,
iblnty,
ibmcu)
in (
SELECT DISTINCT
IMITM,
IMLITM,
IMSTKT,
IMLNTY,
LIMCU,
FROM
CRPDTA/F4101 F4101
LEFT OUTER JOIN CRPDTA/F4111 F4111
ON
F4101.IMITM = F4111.ILITM
LEFT OUTER JOIN CRPDTA/F4211 F4211
ON
F4101.IMITM = F4211.SDITM
LEFT OUTER JOIN CRPDTA/F42119 F42119
ON
F4101.IMITM = F42119.SDITM
LEFT OUTER JOIN CRPDTA/F4311 F4311
ON
F4101.IMITM = F4311.PDITM
LEFT OUTER JOIN CRPDTA/F43199 F43199
ON
F4101.IMITM = F43199.OLITM
LEFT OUTER JOIN CRPDTA/F4801 F4801
ON
F4101.IMITM = F4801.WAITM
LEFT OUTER JOIN CRPDTA/F3002 F3002
ON
F4101.IMITM = F3002.IXITM
LEFT OUTER JOIN CRPDTA/F41021 F41021
ON
F4101.IMITM = F41021.LIITM
LEFT OUTER JOIN CRPDTA/F4141 F4141
ON
F4101.IMITM = F4141.PJITM
WHERE
F4211.SDLITM IS NULL
AND F42119.SDLITM IS NULL
AND PDLITM IS NULL
AND OLLITM IS NULL
AND WALITM IS NULL
AND PJLITM IS NULL
AND IXKITL IS NULL
AND IMLNTY NOT IN( 'B', 'D', 'F', 'IC', 'J', 'J1', 'N', 'N1', 'N2', 'N3', 'N4', 'N5', 'N6', 'T', 'RR', 'X' )
AND LIPQOH = 0
AND LIPBCK = 0
AND LIPREQ = 0
AND LIQWBO = 0
AND LIHCOM = 0
AND LIPCOM = 0
AND LIFUN1 = 0
AND LIQOWO = 0
)
I hope that comes out ok.
In effect, I can't seem to do a select...where...in and use more than one column ?
Help - please !
I know this works in SQL Server and Oracle - I am trying (without much success and with very overheated brain) to work out how to convert the following SQL Statement into DB2/400 terms :
Select count(*) from CRPDTA/F4102 where
(ibitm,
iblitm,
iblitm,
ibstkt,
iblnty,
ibmcu)
in (
SELECT DISTINCT
IMITM,
IMLITM,
IMSTKT,
IMLNTY,
LIMCU,
FROM
CRPDTA/F4101 F4101
LEFT OUTER JOIN CRPDTA/F4111 F4111
ON
F4101.IMITM = F4111.ILITM
LEFT OUTER JOIN CRPDTA/F4211 F4211
ON
F4101.IMITM = F4211.SDITM
LEFT OUTER JOIN CRPDTA/F42119 F42119
ON
F4101.IMITM = F42119.SDITM
LEFT OUTER JOIN CRPDTA/F4311 F4311
ON
F4101.IMITM = F4311.PDITM
LEFT OUTER JOIN CRPDTA/F43199 F43199
ON
F4101.IMITM = F43199.OLITM
LEFT OUTER JOIN CRPDTA/F4801 F4801
ON
F4101.IMITM = F4801.WAITM
LEFT OUTER JOIN CRPDTA/F3002 F3002
ON
F4101.IMITM = F3002.IXITM
LEFT OUTER JOIN CRPDTA/F41021 F41021
ON
F4101.IMITM = F41021.LIITM
LEFT OUTER JOIN CRPDTA/F4141 F4141
ON
F4101.IMITM = F4141.PJITM
WHERE
F4211.SDLITM IS NULL
AND F42119.SDLITM IS NULL
AND PDLITM IS NULL
AND OLLITM IS NULL
AND WALITM IS NULL
AND PJLITM IS NULL
AND IXKITL IS NULL
AND IMLNTY NOT IN( 'B', 'D', 'F', 'IC', 'J', 'J1', 'N', 'N1', 'N2', 'N3', 'N4', 'N5', 'N6', 'T', 'RR', 'X' )
AND LIPQOH = 0
AND LIPBCK = 0
AND LIPREQ = 0
AND LIQWBO = 0
AND LIHCOM = 0
AND LIPCOM = 0
AND LIFUN1 = 0
AND LIQOWO = 0
)
I hope that comes out ok.
In effect, I can't seem to do a select...where...in and use more than one column ?
Help - please !