DB2/400 SQL Help please !

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 !

Before you pull your hair out try using iSeries Access (operation navigator). The SQL syntax is much more like SQL Server Query Analyser so your SQL Server query may work without modification.

Colin

ok - so I'm trying (a simpler query here)

Select count(*) from CRPDTA.F4102 where
(ibitm, iblitm) in (
SELECT IMITM, imlitm
FROM
CRPDTA.F4101 )

It don't work....

Is it not possible to do a select of two columns as a single variable ?

I tried this in iSeries navigator by the way - it gives the same error :


SQL State: 42601
Vendor Code: -104
Message: [SQL0104] Token , was not valid. Valid tokens: < > = <> <= !< !> != >= ¬< ¬> ¬= IN IS NOT LIKE BETWEEN. Cause . . . . . : A syntax error was detected at token ,. Token , is not a valid token. A partial list of valid tokens is < > = <> <= !< !> != >= ¬< ¬> ¬= IN IS NOT LIKE BETWEEN. This list assumes that the statement is correct up to the token. The error may be earlier in the statement, but the syntax of the statement appears to be valid up to this point. Recovery . . . : Do one or more of the following and try the request again: -- Verify the SQL statement in the area of the token ,. Correct the statement. The error could be a missing comma or quotation mark, it could be a misspelled word, or it could be related to the order of clauses. -- If the error token is <END-OF-STATEMENT>, correct the SQL statement because it does not end with a valid clause.

Hi Jon,

You can concatinate the fields and make them one.
For Example, To achieve

Select count(*) from CRPDTA.F4102 where (ibitm, iblitm) in (
SELECT IMITM, imlitm FROM CRPDTA.F4101 )

You can give following SQL statement.

Select count(*) from CRPDTA.F4102 where CONCAT(DIGITS(ibitm), iblitm) in (
SELECT CONCAT(DIGITS(IMITM), imlitm) FROM CRPDTA.F4101 )

Unfortunately I could not check the statement immediately for syntax as my connectivity with AS/400 is down today but I have used it perviously.

Please let me know your result
Regards
Anand

Hey Jon,

wouldn't this work?:
SELECT COUNT(1)
FROM CRPDTA/F4102 T0, CRPDTA/F4101 T1
WHERE T1.IMITM=T0.IBITM AND T1.IMLITM=T0.IBLITM;

Regards,
Ash

Pardon me if seem stupid, but what the heck are you trying to do? There shouldn't be any records in F4101 with a NULL item number.

Give this a try.

Select count(*) from CRPDTA.F4102 where

concat(char(ibitm), iblitm) in (

SELECT concat(char(IMITM), imlitm )

FROM

CRPDTA.F4101 ) ;

The second query is just an example of a query - the initial query is the main one. I'm trying to delete items out of the F4102 that have been populated there before by an eager conversion analyst, but have never been used. This query should actually reduce our F4102 by almost 2/3 - yes, someone decided to copy all items to all branches without thinking of the implications of performance with regards to processes like MRP !

Well - CONCAT seems to be the way to do things, so I'm going to attempt this today - thankyou everyone !

I think this is equivalent to what you initially posted. Run this is the Ops Nav SQL Tool and then go to lunch or something. It will run for a while. Good luck.

Select count(*) from CRPDTA.F4102 where

(CHAR(ibitm) concat iblitm concat iblitm concat ibstkt concat iblnty concat ibmcu)

in (

SELECT
DISTINCT(CHAR(IMITM) concat IMLITM concat IMSTKT concat IMLNTY concat 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 usually use EXISTS or NOT EXISTS predicate if I have more than one column

E.G

Select count(*) from CRPDTA/F4102 a where exists (


SELECT *

FROM
CRPDTA/F4101 b
where IMITM = a.ibitm
IMLITM = a.iblitm
IMSTKT = a.ibstkt
IMLNTt = a.iblnty
LIMCU = a.ibmcu)

That's pretty close, but I think you need to use the digits function instead of the char function.

The actual command I am using is :

(wish there was a CODE tag on this forum)



Select count(*) from CRPDTA.F4102 where CONCAT(CONCAT(CONCAT(CONCAT(Digits(IBITM),IBLITM),IBSTKT),IBLNTY),IBMCU)
in (SELECT DISTINCT
CONCAT(CONCAT(CONCAT(CONCAT(Digits(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 )

Gonna take a loooong time to run I expect. But it should then be easy to change this to a DELETE and remove all F4102 records that have no activity.

exactly. I also use || instead of concat for ease of looking at it
Select count(*) from CRPDTA.F4102 where Digits(IBITM)||IBLITM || IBSTKT || IBLNTY ||IBMCU
in (SELECT DISTINCT
Digits(IMITM) ||IMLITM || IMSTKT ||IMLNTY ||LIMCU ....


Jean Driscoll
AS/400(V5R2) Co-existent Xe SP22, Update 7/A73Cum12
WWW.JDETips.com

exactly. I also use || instead of concat for ease of looking at it
Select count(*) from CRPDTA.F4102 where Digits(IBITM)||IBLITM || IBSTKT || IBLNTY ||IBMCU
in (SELECT DISTINCT
Digits(IMITM) ||IMLITM || IMSTKT ||IMLNTY ||LIMCU ....

That was exactly what I was looking for Jean.

Thankyou.