Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a big query that contains a PIVOT and several LEFT OUTER JOINS. I need to add a second LEFT OUTER JOIN to a table that is already joined in the query. When I do this the query errors and I'm not sure why.

It appears to me that the PIVOT does not like two left outer joins to the same table.

HERE is a SQL Fiddle Schema Build for reference I tried to create a minimal sample for this question, the SQL statement below will not run in SQL Fiddle however so I dont know how much help this is.

Here is the current query that works as expected.

DECLARE @cols AS NVARCHAR(MAX), @isnullcols AS NVARCHAR(MAX), @query  AS NVARCHAR(MAX), @startno as int,@endno as int;
Set @startno = 1;
Set @endno = 104;    

WITH DateRange(dt) AS
        (
            SELECT CONVERT(int, @startno) dt
            UNION ALL
            SELECT dt+1 FROM DateRange WHERE dt < @endno
        )
SELECT @cols = STUFF((SELECT ',' + QUOTENAME((SELECT CASE 
            WHEN dt <= 52 
               THEN (cast((DATEPART(yyyy, GetDate())-1) as varchar(4))+'-Week'+(cast(dt as varchar(3)))) 
               ELSE (cast((DATEPART(yyyy, GetDate())) as varchar(4))+'-Week'+(cast(dt-52 as varchar(3)))) 
       END)) FROM DateRange
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')
    OPTION (maxrecursion 0);


set @query = N'SELECT TOP 5 LIITM as ShortItemNumber, IBVEND as VendorNo, ABALPH as VendorName, IBBUYR as BuyerNo,  ' + @cols + '
FROM
    (
    SELECT LIITM, IBVEND, ABALPH, IBBUYR, ILTRQT, cast(DATEPART(yyyy, DATEADD(DAY, ILDGL % 1000, DATEADD(YEAR, ILDGL / 1000, -1))) as varchar(4))+''-Week''+cast(DATEPART(ww, DATEADD(DAY, ILDGL % 1000, DATEADD(YEAR, ILDGL / 1000, -1))) as varchar(2)) WeekNo 
    FROM F41021

    LEFT OUTER JOIN F4111
        ON F41021.LIITM = F4111.ILITM 

    LEFT OUTER JOIN F4102
    ON F41021.LIITM = F4102.IBITM AND F41021.LIMCU = F4102.IBMCU 

    LEFT OUTER JOIN vwF0101
    ON F4102.IBVEND = vwF0101.ABAN8 

    ) d
PIVOT
(
    SUM(ILTRQT)
    for WeekNo in (' + @cols + ')
) piv
GROUP BY LIITM, IBVEND, ABALPH, IBBUYR,   '+ @cols + ''

execute(@query)

Sample output is along the lines of this (heavily trimmed for readability):

ShortItemNumber   |   VendorNo   |   VendorName        |   BuyerNo   |   2012-Week1   |   2012-Week2   |   2012-Week3
-----------------------------------------------------------------------------------------------------------------------
140               |   32560      |   Bill's Auto Parts |   971494    |     NULL       |      NULL      |      NULL     
80049             |   32560      |   Bill's Auto Parts |   971494    |     NULL       |      NULL      |      NULL     
80071             |   32560      |   Bill's Auto Parts |   971494    |     NULL       |      NULL      |      NULL     
80072             |   32560      |   Bill's Auto Parts |   971494    |     NULL       |      NULL      |      NULL     

The second query, I add a second join to the vwF0101 table to retrieve another alpha name for a userId (IBBUYR).

DECLARE @cols AS NVARCHAR(MAX), @isnullcols AS NVARCHAR(MAX), @query  AS NVARCHAR(MAX), @startno as int,@endno as int;
Set @startno = 1;
Set @endno = 104;    

WITH DateRange(dt) AS
        (
            SELECT CONVERT(int, @startno) dt
            UNION ALL
            SELECT dt+1 FROM DateRange WHERE dt < @endno
        )
SELECT @cols = STUFF((SELECT ',' + QUOTENAME((SELECT CASE 
            WHEN dt <= 52 
               THEN (cast((DATEPART(yyyy, GetDate())-1) as varchar(4))+'-Week'+(cast(dt as varchar(3)))) 
               ELSE (cast((DATEPART(yyyy, GetDate())) as varchar(4))+'-Week'+(cast(dt-52 as varchar(3)))) 
       END)) FROM DateRange
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')
    OPTION (maxrecursion 0);


set @query = N'SELECT TOP 5 LIITM as ShortItemNumber, IBVEND as VendorNo, vendor.ABALPH as VendorName, IBBUYR as BuyerNo,  buyer.ABALPH, ' + @cols + '
FROM
    (
    SELECT LIITM, IBVEND, vendor.ABALPH, IBBUYR, buyer.ABALPH, ILTRQT, cast(DATEPART(yyyy, DATEADD(DAY, ILDGL % 1000, DATEADD(YEAR, ILDGL / 1000, -1))) as varchar(4))+''-Week''+cast(DATEPART(ww, DATEADD(DAY, ILDGL % 1000, DATEADD(YEAR, ILDGL / 1000, -1))) as varchar(2)) WeekNo 
    FROM JDE_Production.PRODDTA.F41021

    LEFT OUTER JOIN JDE_Production.PRODDTA.F4111
        ON JDE_Production.PRODDTA.F41021.LIITM = JDE_Production.PRODDTA.F4111.ILITM 

    LEFT OUTER JOIN JDE_Production.PRODDTA.F4102
    ON JDE_Production.PRODDTA.F41021.LIITM = JDE_Production.PRODDTA.F4102.IBITM AND JDE_Production.PRODDTA.F41021.LIMCU = JDE_Production.PRODDTA.F4102.IBMCU 

    LEFT OUTER JOIN JDE_Production.PRODDTA.vwF0101 as vendor
    ON JDE_Production.PRODDTA.F4102.IBVEND = vendor.ABAN8 

    LEFT OUTER JOIN JDE_Production.PRODDTA.vwF0101 as buyer
    ON JDE_Production.PRODDTA.F4102.IBBUYR = buyer.ABAN8

    ) d
PIVOT
(
    SUM(ILTRQT)
    for WeekNo in (' + @cols + ')
) piv
GROUP BY LIITM, IBVEND, vendor.ABALPH, IBBUYR, buyer.ABALPH,  '+ @cols + ''

execute(@query)

These are the error messages I received. It seems to me the PIVOT doesn't like me referencing the ABALPH column twice, even though I have it aliased separately.

Msg 8156, Level 16, State 1, Line 20
The column 'ABALPH' was specified multiple times for 'd'.
Msg 8156, Level 16, State 1, Line 24
The column 'ABALPH' was specified multiple times for 'piv'.
Msg 4104, Level 16, State 1, Line 25
The multi-part identifier "vendor.ABALPH" could not be bound.
Msg 4104, Level 16, State 1, Line 25
The multi-part identifier "buyer.ABALPH" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "vendor.ABALPH" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "buyer.ABALPH" could not be bound.
share|improve this question

1 Answer 1

up vote 2 down vote accepted

you just need to alias one of the select column

vendor.ABALPH as ABALPH, buyer.ABALPH as ABALPH_buyer

    declare @t table
    (
        id int
    )

    --this is valid query
    select id, id
    from 
    (select id from @t)qry

    --this is valid query
    select id
    from 
    (select id as id,id as id1 from @t)qry1

    --this is NOT valid query
    select id
    from 
    (select id,id from @t)qry1
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.