SQL for DB2在Crystal Reports中不起作用

Question

我正试图在一个crystal报告中针对AS/400 db2数据库运行此SQL，我得到了一个错误

未能从数据库检索数据"详细信息42000 IBM ISeries ACCESS ODBC驱动程序DB2 UDB SQL0104令牌无效。有效令牌<语句结束>数据库供应商代码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) )

Answer 1

最后一个括号应该是有问题的。删除它，使您的代码看起来像这样：

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

我在AS/400 v6r1系统上用伪数据进行了测试。SQL将编译并生成没有问题的数据。希望CrystalReports在使用结果数据时不会有太大问题。我无法访问Crystal Reports来测试结果。

只有当你对未经请求的建议感兴趣时，我才提出一种稍微不同的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