SQL Server 2008 Specified column was specified mul

问题:

The below code throws the following exception. How can I fix this?

Msg 8156, Level 16, State 1, Line 17
The column 'id' was specified multiple times for 'QISproduct'.

Query:

SELECT 
    g.artcode
    , sum(g.aantal)
    , i.class_01
    , i.Isstockitem
FROM 
    gbkmut AS g
INNER JOIN 
    items AS i ON i.itemcode = g.artcode
INNER JOIN 
    (SELECT 
         QISP.id
         , QISprocess.nml
         , QISeventlog.id
         , QISeventlog.dtsample
         , QISproduct.nms
         , QISbatchlog.nm
         , QIStestlog.idvariable
         , QIStestlog.no
         , QISshortnote.ds
         , gewicht = CASE QIStestlog.IDvariable
                        WHEN '139'
                           THEN QIStestlog.no
                     END
         , aantal = CASE QIStestlog.IDvariable
                       WHEN '234'
                          THEN QIStestlog.no
                    END
         , siloleeg = CASE QIStestlog.idvariable
                         WHEN '23'
                            THEN CASE QIStestlog.no
                                    WHEN '10'
                                       THEN 'Ja'
                                       ELSE 'Nee'
                                 END
                      END
         , QISvariable.nml
         , gl.nm
         , QISprocess.id
     FROM 
         QIC.Vobra_new2.dbo.production AS QISP
     INNER JOIN 
         QIC.Vobra_new2.dbo.process AS QISprocess ON QISP.idprocess = QISProcess.id
     INNER JOIN 
         QIC.Vobra_new2.dbo.product AS QISproduct ON QISP.idproduct = QISproduct.id
     INNER JOIN 
         QIC.Vobra_new2.dbo.batchlog AS QISbatchlog ON QISP.idbatch = QISbatchlog.id
     INNER JOIN 
         QIC.Vobra_new2.dbo.eventlog AS QISeventlog ON QISeventlog.idproduction = QISP.id
                                                    AND QISeventlog.idbatch = QISbatchlog.id
     INNER JOIN 
         QIC.Vobra_new2.dbo.testlog AS QIStestlog ON QIStestlog.idevent = QISeventlog.id
     LEFT OUTER JOIN 
         QIC.Vobra_new2.dbo.shortnote AS QISshortnote ON QISshortnote.id = QIStestlog.no
                                                      AND QIStestlog.idvariable = '144'
     INNER JOIN 
         QIC.Vobra_new2.dbo.variable AS QISvariable ON QISvariable.id = QIStestlog.idvariable
     LEFT OUTER JOIN 
        QIC.Vobra_new2.dbo.vvarxproc AS vvp ON vvp.idvariable = QISvariable.id
                                            AND vvp.idprocess = QISP.idprocess
     LEFT OUTER JOIN 
         QIC.Vobra_new2.dbo.attribute AS QISattribute ON QISattribute.id = vvp.idattribute
     LEFT OUTER JOIN 
         QIC.Vobra_new2.dbo.grade AS QISgrade ON QISgrade.id = QISattribute.idgrade
     LEFT OUTER JOIN 
         QIC.Vobra_new2.dbo.gradelevel AS gl ON gl.idgrade = QISattribute.idgrade
                                             AND gl.nlevel = QIStestlog.no
     WHERE 
         QISbatchlog.nm NOT LIKE 'V%'
         AND QISP.dtstart > '2017-01-01'
         AND QISP.dtstart < '2017-01-19'
         AND QISP.idprocess IN ('12', '13', '14', '15', '16', '17', '18', '41')
         AND QIStestlog.idvariable IN ('234', '139', '128')
    ) QISproduct ON g.artcode = QISproduct.nms
WHERE 
    g.bkjrcode > '2015'
    AND g.reknr IN (3000, 3010, 3020)
    AND g.aantal > 0
    AND g.warehouse IN ('1', '9')
    AND g.datum >= '2017-01-01'
    AND g.oorsprong = 'R'
    AND g.kstplcode <> 'VPR'
GROUP BY 
    g.artcode, i.Class_01, i.IsStockItem

回答1:

The computed query aliased to QISProduct contains id column from two tables i.e. QISEventLog and QISProcess. So rename those columns to different names. Updated query

        SELECT g.artcode
            ,sum(g.aantal)
            ,i.class_01
            ,i.Isstockitem
        FROM gbkmut AS g
        INNER JOIN items AS i ON i.itemcode = g.artcode
        INNER JOIN (
            SELECT QISP.id
                ,QISprocess.nml AS Processnml
                ,QISeventlog.id AS EventLogId
                ,QISeventlog.dtsample
                ,QISproduct.nms
                ,QISbatchlog.nm AS batchnm
                ,QIStestlog.idvariable
                ,QIStestlog.no
                ,QISshortnote.ds
                ,gewicht = CASE QIStestlog.IDvariable
                    WHEN '139'
                        THEN QIStestlog.no
                    END
                ,aantal = CASE QIStestlog.IDvariable
                    WHEN '234'
                        THEN QIStestlog.no
                    END
                ,siloleeg = CASE QIStestlog.idvariable
                    WHEN '23'
                        THEN CASE QIStestlog.no
                                WHEN '10'
                                    THEN 'Ja'
                                ELSE 'Nee'
                                END
                    END
                ,QISvariable.nml variablenml
                ,gl.nm AS glnm
                ,QISprocess.id AS ProcessId
            FROM QIC.Vobra_new2.dbo.production AS QISP
            INNER JOIN QIC.Vobra_new2.dbo.process AS QISprocess ON QISP.idprocess = QISProcess.id
            INNER JOIN QIC.Vobra_new2.dbo.product AS QISproduct ON QISP.idproduct = QISproduct.id
            INNER JOIN QIC.Vobra_new2.dbo.batchlog AS QISbatchlog ON QISP.idbatch = QISbatchlog.id
            INNER JOIN QIC.Vobra_new2.dbo.eventlog AS QISeventlog ON QISeventlog.idproduction = QISP.id
                AND QISeventlog.idbatch = QISbatchlog.id
            INNER JOIN QIC.Vobra_new2.dbo.testlog AS QIStestlog ON QIStestlog.idevent = QISeventlog.id
            LEFT JOIN QIC.Vobra_new2.dbo.shortnote AS QISshortnote ON QISshortnote.id = QIStestlog.no
                AND QIStestlog.idvariable = '144'
            INNER JOIN QIC.Vobra_new2.dbo.variable AS QISvariable ON QISvariable.id = QIStestlog.idvariable
            LEFT JOIN QIC.Vobra_new2.dbo.vvarxproc AS vvp ON vvp.idvariable = QISvariable.id
                AND vvp.idprocess = QISP.idprocess
            LEFT JOIN QIC.Vobra_new2.dbo.attribute AS QISattribute ON QISattribute.id = vvp.idattribute
            LEFT JOIN QIC.Vobra_new2.dbo.grade AS QISgrade ON QISgrade.id = QISattribute.idgrade
            LEFT JOIN QIC.Vobra_new2.dbo.gradelevel AS gl ON gl.idgrade = QISattribute.idgrade
                AND gl.nlevel = QIStestlog.no
            WHERE QISbatchlog.nm NOT LIKE 'V%'
                AND QISP.dtstart > '2017-01-01'
                AND QISP.dtstart < '2017-01-19'
                AND QISP.idprocess IN (
                    '12'
                    ,'13'
                    ,'14'
                    ,'15'
                    ,'16'
                    ,'17'
                    ,'18'
                    ,'41'
                    )
                AND QIStestlog.idvariable IN (
                    '234'
                    ,'139'
                    ,'128'
                    )
            ) QISproduct ON g.artcode = QISproduct.nms
        WHERE g.bkjrcode > '2015'
            AND g.reknr IN (
                3000
                ,3010
                ,3020
                )
            AND g.aantal > 0
            AND g.warehouse IN (
                '1'
                ,'9'
                )
            AND g.datum >= '2017-01-01'
            AND g.oorsprong = 'R'
            AND g.kstplcode <> 'VPR'
        GROUP BY g.artcode
            ,i.Class_01
            ,i.IsStockItem

回答2:

, QISbatchlog.nm And , gl.nm Have same column name You can add as to change colum name , gl.nm as col1

回答3:

You have multiple issues in your query. You are populating following columns with same column name in your inner query. Use Unique name using AS alias in inner query.

QISP.id
QISeventlog.id
QISprocess.id

gl.nm
QISbatchlog.nm

QISprocess.nml
QISvariable.nml

It is mandatory to have unique column name return by select list in sql.