Ambiguous Column name ??

  • CREATE TABLE #tempTotal

    (

    ID_PRODUCT int,

    NM_TOTAL int

    )

    INSERT INTO #tempTotal

    SELECT

    #tempRespPriceSummary.ID_PRODUCT,

    COUNT (#tempRespPriceSummary.ID_PRODUCT) AS NM_TOTAL

    FROM

    #tempRespPriceSummary WITH(NOLOCK)

    GROUP BY

    ID_PRODUCT

    this part of my procedure throws up Ambiguous column name error for ID_PRODUCT ? Please help ?

  • Try this...

    GROUP BY #tempRespPriceSummary.ID_PRODUCT

    If it was easy, everybody would be doing it!;)

  • Tried that, but I get the same error.

  • you'll need to post more context, because your insert statement does not produce an error.

    select 0 as ID_PRODUCT, 0 as NM_TOTAL into #tempTotal where 1 = 2

    select 0 as ID_PRODUCT into #tempRespPriceSummary

    -- your statement below

    insert into #tempTotal

    SELECT

    #tempRespPriceSummary.ID_PRODUCT,

    COUNT (#tempRespPriceSummary.ID_PRODUCT) AS NM_TOTAL

    FROM

    #tempRespPriceSummary WITH(NOLOCK)

    GROUP BY

    ID_PRODUCT

  • Please post atleast the suedo code to mimic your procedure, the content that you have posted could not be used for replicating the error that you are getting.

    sid_sarkar (4/8/2008)


    CREATE TABLE #tempTotal

    (

    ID_PRODUCT int,

    NM_TOTAL int

    )

    INSERT INTO #tempTotal

    SELECT

    #tempRespPriceSummary.ID_PRODUCT,

    COUNT (#tempRespPriceSummary.ID_PRODUCT) AS NM_TOTAL

    FROM

    #tempRespPriceSummary WITH(NOLOCK)

    GROUP BY

    ID_PRODUCT

    this part of my procedure throws up Ambiguous column name error for ID_PRODUCT ? Please help ?

    Prasad Bhogadi
    www.inforaise.com

  • What is the DDL for #tempRespPriceSummary table

    Vinoj

  • Are you certain that this is the section of your proc which is throwing the error? We cannot reproduce your error in this piece of code. Perhaps the ambiguity is earlier in your proc, when gathering into the #tempRespPriceSummary table? You could check any 'AS' names in the 'SELECT' clauses of those earlier 'INSERT INTO #tempRespPriceSummary' statements.

    I think that, if the column positions of the SELECT clause match the table being INSERT INTO'ed, you need not use 'AS'. Less typing is a good thing.

  • I'm seeing the same error message for the following code:

    CREATE TABLE #CDHToDelete

    (

    CDH_RecordID UNIQUEIDENTIFIER

    )

    INSERT #CDHToDelete

    SELECT CDH.CDH_RecordID

    FROM (IERP72..CommissionDefinitionHeader CDH

    INNER JOIN IERP72..CommissionDefinitionDetail CDD

    ON CDH.CDH_RecordID = CDD.CDD_CDH_RecordID)

    INNER JOIN IERP72..CommissionTransactions TRN

    ON CDD.CDD_RecordID = TRN.COT_CDD_RecordID

    WHERE TRN.COT_GLDate < @CutOffDate

    yet I can use this SELECT to insert all columns of CDH into a regular (not temp) table. I conclude this most have something to do with the temp table rather than the SELECT statement. Can anyone shed some light on this?

  • Piper Skip (4/21/2008)


    I'm seeing the same error message for the following code:

    CREATE TABLE #CDHToDelete

    (

    CDH_RecordID UNIQUEIDENTIFIER

    )

    INSERT #CDHToDelete

    SELECT CDH.CDH_RecordID

    FROM (IERP72..CommissionDefinitionHeader CDH

    INNER JOIN IERP72..CommissionDefinitionDetail CDD

    ON CDH.CDH_RecordID = CDD.CDD_CDH_RecordID)

    INNER JOIN IERP72..CommissionTransactions TRN

    ON CDD.CDD_RecordID = TRN.COT_CDD_RecordID

    WHERE TRN.COT_GLDate < @CutOffDate

    yet I can use this SELECT to insert all columns of CDH into a regular (not temp) table. I conclude this most have something to do with the temp table rather than the SELECT statement. Can anyone shed some light on this?

    the CDH alias is defined within a derived table. alias the derivation and you should be fine.

    INSERT #CDHToDelete

    SELECT CDH.CDH_RecordID

    FROM (IERP72..CommissionDefinitionHeader CDH

    INNER JOIN IERP72..CommissionDefinitionDetail CDD

    ON CDH.CDH_RecordID = CDD.CDD_CDH_RecordID) as CDH

    INNER JOIN IERP72..CommissionTransactions TRN

    ON CDD.CDD_RecordID = TRN.COT_CDD_RecordID

    WHERE TRN.COT_GLDate < @CutOffDate

  • I tried that option earlier; it yields the error "Incorrect syntax near the keyword 'as'.

  • Try it without the 'AS'.

  • Delete the parentheses (and the resulting derivision) from your FROM clause

    INSERT #CDHToDelete

    SELECT CDH.CDH_RecordID

    FROM IERP72..CommissionDefinitionHeader CDH

    INNER JOIN IERP72..CommissionDefinitionDetail CDD

    ON CDH.CDH_RecordID = CDD.CDD_CDH_RecordID

    INNER JOIN IERP72..CommissionTransactions TRN

    ON CDD.CDD_RecordID = TRN.COT_CDD_RecordID

    WHERE TRN.COT_GLDate < @CutOffDate

  • Neither suggestion works. The interesting (frustrating) thing about this is the fact that it fails only when included within a stored procedure. Running the two statements directly from query analyzer (after replacing @custoffdate with a literal date) functions perfectly. Go figure.

  • Piper Skip (4/21/2008)


    I'm seeing the same error message for the following code:

    CREATE TABLE #CDHToDelete

    (

    CDH_RecordID UNIQUEIDENTIFIER

    )

    INSERT #CDHToDelete

    SELECT CDH.CDH_RecordID

    FROM (IERP72..CommissionDefinitionHeader CDH

    INNER JOIN IERP72..CommissionDefinitionDetail CDD

    ON CDH.CDH_RecordID = CDD.CDD_CDH_RecordID)

    INNER JOIN IERP72..CommissionTransactions TRN

    ON CDD.CDD_RecordID = TRN.COT_CDD_RecordID

    WHERE TRN.COT_GLDate < @CutOffDate

    yet I can use this SELECT to insert all columns of CDH into a regular (not temp) table. I conclude this most have something to do with the temp table rather than the SELECT statement. Can anyone shed some light on this?

    I'm sorry Piper, I just glanced at this, saw parenthesis, and erroneously assumed you had a derived table (which you don't). What is the exact error message your are receiving (please include the column name).

  • Piper Skip (4/22/2008)


    Neither suggestion works. The interesting (frustrating) thing about this is the fact that it fails only when included within a stored procedure. Running the two statements directly from query analyzer (after replacing @custoffdate with a literal date) functions perfectly. Go figure.

    In that case, the outer stored procedure is almost certainly the cause of the problem. Please post that, if you can.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply