SQL Error [42972]: [SQL0338] JOIN predicate or MERGE ON clause not valid

Question

SQL Error [42972]: [SQL0338] JOIN predicate or MERGE ON clause not valid.

I'm trying to run the query below, but an error occurs in the JOIN in the table DB170P. Can you help me?

SELECT ACBAC AS "CBUYER"
,      HBAC AS "BORDER"
,      IHTRCD AS "TRANS"
,      IHRVCD AS "REV"
,      DATE(CONCAT(CONCAT(CONCAT(CONCAT(IHTRMM,'/'),IHTRDD),'/'),IHTRCC*100+IHTRYY)) AS "RECD"
,      VADFCD AS "DF"
,      HVEND  AS "VENC"
,      VSNAME AS "VNAME"
,      IHSRNO AS "SER"
,      DATE(PHCUMM||'/'||PHCUDD||'/'||(PHCUCC*100+PHCUYY)) "CUST_DATE"
,      PHBLNO AS "B/L"
,      IHPLST AS "NF"
,      IHORNO AS "PO"
,      IHPNO  AS "PN"
,      MODEL
,      (CASE WHEN CONCAT(IHTRCD, IHRVCD) IN('D20','D50T','D50Y','E30') THEN IHQTY ELSE -IHQTY END) AS "QTY"
,      IHUNMS AS "UN"
,      HPOCST AS "COST"
,      DBCRCY AS "CURRENCY"
,      (HPOCST*(CASE WHEN CONCAT(IHTRCD, IHRVCD) IN('D20','D50T','D50Y','E30') THEN IHQTY ELSE -IHQTY END)) AS "TOTAL"
--,      CASE WHEN VADFCD='D' THEN 1 ELSE DB170P.DBRATE END AS "EXRAT"
--,      (HPOCST*(CASE WHEN VADFCD='D' THEN 1 ELSE DB170P.DBRATE END)) AS "CLC"
--,      (HPOCST*(CASE WHEN CONCAT(IHTRCD, IHRVCD) IN('D20','D50T','D50Y','E30') THEN IHQTY ELSE -IHQTY END)*(CASE WHEN VADFCD='D' THEN 1 ELSE DB170P.DBRATE END)) AS "TOTALLC"
FROM (SELECT * FROM IC090P UNION SELECT * FROM IC090B)AAA 
LEFT OUTER JOIN (SELECT * FROM PC090P, PC100P WHERE PIBLNO=PHBLNO )X ON TRIM(PIIVNO)=TRIM(IHPLST)  
,DB070P , IC010P
LEFT OUTER JOIN 
               (SELECT * FROM DB160P)DB160P 
               ON HCRCD=DB160P.DBCRCD
LEFT OUTER JOIN
               (SELECT * FROM DB170P)DB170P
               on (db160p.DBCRCD=DB170P.DBCRCD
               AND DATE(CONCAT(CONCAT(CONCAT(CONCAT(DBEFMM,'/'),DBEFDD),'/'),
               DBEFCC*100+DBEFYY))=DATE((DAYS(DATE(PHCUMM||'/'||PHCUDD||'/'||(PHCUCC*100+PHCUYY)))-2)))

Answer 1

This LEFT OUTER JOIN (SELECT * FROM DB170P)DB170P is very strange syntax...
LEFT OUTER JOIN DP170P is all you really need.

You're likely getting an error trying to convert to date data types in the join to DB170P.

Have you looked in the job log for additional error information?

I'd suggest

• moving from Nested Table Expressions (NTE) to Common Table Expressions (CTE)
• build a User Defined Function (UDF) to handle the date conversion (or download the open source iDate)
• do the date conversions via UDF in the CTEs
• use explicit join
• usually a good idea to not use SELECT * even if you really need all the columns

Here's what a CTE version might start like...

with AAAA as (SELECT trim(IHPLST) as lastInvNbr
             FROM IC090P 
             UNION 
             SELECT trim(IHPLST) 
             FROM IC090B)
 , X as (SELECT trim(PIIVNO) as InvNbr 
         FROM PC090P 
         join PC100P 
           on PIBLNO=PHBLNO
         )
select * 
from aaaa join X on aaaa.lastInvNbr = x.InvNo

Beside being reusable, CTE make it easy to slap a SELECT * FROM mycte so that you can see what intermediate results as you're building up the statement.