db2 sqlstate 42972错误

Question

当我试图执行这个sql查询时，我得到了这个错误

INSERT INTO
            AGG_QUALITY
    SELECT
            QIF.DATEDM_ID, 
            'Status' AS BREAKDOWN_TYPE, 
            HCR.VALUE, 
            QIF.SCANDEFINITION_ID, 
            QIF.QUALITYISSUE_VALUE, 
            COUNT(QIF.QUALITYISSUEFACT_ID), 
            COUNT(QRF.QUALITYRESOLUTIONFACT_ID)

    FROM
            QUALITYISSUEFACT QIF,
            HUBCODERECORD HCR,
            ITEMSTATUS ITS
    LEFT JOIN
            QUALITYRESOLUTIONFACT QRF
    ON
            QIF.QUALITYISSUEFACT_ID = QRF.QUALITYISSUEFACT_ID
    WHERE
            QIF.DATEDM_ID > startDateDMId 
            AND     QIF.DATEDM_ID <= endDateDMId    
            AND HCR.CODE = ITS.H_STATUS_TYPE
            AND QIF.DIMENSION_ROM_PK = ITS.ITEMMASTER_ID
    GROUP BY
            QIF.DATEDM_ID, HCR.VALUE, QIF.SCANDEFINITION_ID, QIF.QUALITYISSUE_VALUE
    ;

SQL已将该命令作为DB21034E语句处理，因为它不是有效的命令行处理器命令。在SQL处理过程中，它返回: SQL0338N与JOIN运算符关联的ON子句或MERGE语句中的ON子句无效。线路NUMBER=31。SQLSTATE=42972

Answer 1

尝试像下面这样使用内部连接：

INSERT INTO
            AGG_QUALITY
    SELECT
            QIF.DATEDM_ID, 
            'Status' AS BREAKDOWN_TYPE, 
            HCR.VALUE, 
            QIF.SCANDEFINITION_ID, 
            QIF.QUALITYISSUE_VALUE, 
            COUNT(QIF.QUALITYISSUEFACT_ID), 
            COUNT(QRF.QUALITYRESOLUTIONFACT_ID)

    FROM
            QUALITYISSUEFACT QIF
            INNER JOIN
            HUBCODERECORD HCR
            ON QIF.DIMENSION_ROM_PK = ITS.ITEMMASTER_ID
            INNER JOIN
            ITEMSTATUS ITS
            ON QIF.DIMENSION_ROM_PK = ITS.ITEMMASTER_ID
    LEFT JOIN
            QUALITYRESOLUTIONFACT QRF
    ON
            QIF.QUALITYISSUEFACT_ID = QRF.QUALITYISSUEFACT_ID
    WHERE
            QIF.DATEDM_ID > startDateDMId 
            AND     QIF.DATEDM_ID <= endDateDMId    
    GROUP BY
            QIF.DATEDM_ID, HCR.VALUE, QIF.SCANDEFINITION_ID, QIF.QUALITYISSUE_VALUE
    ;

看看有没有帮助

Answer 2

这里也有同样的错误。(注意，我的Table1没有行)

在我这边，我可以通过将额外的(逗号分隔的) table2更改为真正的内部联接来解决它。

示例来自

select xxx from Table1 t1, Table2 t2 
where (t2.col1 = t1.col1) and (t1.col2 = "whatever")

至

select xxx from Table1 t1
INNER JOIN Table2 t2 ON (t2.col1 = t1.col1)
where (t1.col2 = "whatever")

希望这对其他人有帮助。( DB2 9.7上出现问题)