SQL for DB2 not working in Crystal Reports

Question

I am trying to run this SQL against the AS/400 db2 database in a crystal report, I am getting an error

FAILED TO RETREIVE DATA FROM THE DATABASE" Details 42000 IBM ISeries ACCESS ODBC Driver DB2 UDB SQL0104 Token Not valid. Valid tokens <End of Statement> Database Vendor code 104

(SELECT  LMLTPC, COALESCE(IRLOC1,'') as IRLOC1, COALESCE(IRLOC2,'')
 as IRLOC2, COALESCE(IRLOC3,'') as IRLOC3, IRPRT#, IRQOH#, IRWHS#,
 '' as IEPRT#,   '.00' as IEQOH#, '' as IELOC1, '' as IELOC2, '' as
 IELOC3, '' as IEWHS#
 FROM
 (SELECT LMLTPC, LMLOC1, LMLOC2, LMLOC3 FROM ASTDTA.ICLOCMLM WHERE
 LMLTPC='PAL') t1
 left outer join
 (SELECT IRLOC1, IRLOC2, IRLOC3, IRPRT#, IRQOH#, IRWHS# FROM
 ASTDTA.ICBLDTIR  ) t2
 On LMLOC1=IRLOC1 AND LMLOC2=IRLOC2 AND LMLOC3=IRLOC3 )
 UNION ALL
 (SELECT  ' ' as LMLTPC, ' ' as IRLOC1, ' ' as IRLOC2, ' ' as IRLOC3,
 '' as IRPRT#, '.00' as IRQOH#, '' as IRWHS#, IEPRT#, IEQOH#, IELOC1,
 IELOC2, IELOC3,
 IEWHS# FROM ASTDTA.ICBALMIE) )

The last closed-parenthesis does not have a matching open-parenthesis. That's the first thing I am noticing. Could you try removing that and attempting again? — zedfoxus
YEs I did that. Now there is a different error: SQL0199 Keyword Outer not expected UNION EXCEPT (DATABASE VENDOR CODE -199) — Pinchas K
Instead of using left OUTER join, could you try using left join? Alternately, try to utilize the re-written query in my answer and see if an error crops up. — zedfoxus
@WarrenT, yes, you are correct and I understand. With DB2/400 and different SQL IDEs I have come across bizarre errors in the past; so thought of having the OP try leaving the keyword OUTER out since SQL0199 was warning us. — zedfoxus

zedfoxus zedfoxus · Accepted Answer · 2013-08-06T15:39:55

The last end-parenthesis should be the offending one. Remove that so that your code will look like this:

...
...
IEWHS# FROM ASTDTA.ICBALMIE)

I tested with dummy data on an AS/400 v6r1 system. The SQL will compile and result in data without an issue. Hopefully Crystal Reports won't have much problem in using the resulting data. I don't have access to Crystal Reports to test the result.

Only if you are interested in unsolicited advise, I propose a slightly different way of writing the SQL:

SELECT
        LMLTPC,
        COALESCE(IRLOC1,'') as IRLOC1,
        COALESCE(IRLOC2,'') as IRLOC2,
        COALESCE(IRLOC3,'') as IRLOC3,
        IRPRT#,
        IRQOH#,
        IRWHS#,
        '' as IEPRT#,
        '.00' as IEQOH#,
        '' as IELOC1,
        '' as IELOC2,
        '' as IELOC3,
        '' as IEWHS#
 FROM ASTDTA.ICLOCMLM mlm
 left join ASTDTA.ICBLDTIR tir
        on mlm.LMLOC1 = tir.IRLOC1
        and mlm.LMLOC2 = tir.IRLOC2
        and mlm.LMLOC3 = tir.IRLOC3
where LMLTPC = 'PAL'

UNION ALL

SELECT  
    ' ' as LMLTPC, 
    ' ' as IRLOC1, 
    ' ' as IRLOC2, 
    ' ' as IRLOC3,
    '' as IRPRT#, 
    '.00' as IRQOH#, 
    '' as IRWHS#, 
    IEPRT#, 
    IEQOH#, 
    IELOC1,
    IELOC2, 
    IELOC3,
    IEWHS# 
FROM ASTDTA.ICBALMIE

SQL for DB2 not working in Crystal Reports

1 Answers