April 8, 2008 at 6:15 am
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 ?
April 8, 2008 at 6:21 am
Try this...
GROUP BY #tempRespPriceSummary.ID_PRODUCT
If it was easy, everybody would be doing it!;)
April 8, 2008 at 6:29 am
Tried that, but I get the same error.
April 8, 2008 at 6:36 am
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
April 8, 2008 at 8:56 am
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
April 8, 2008 at 2:16 pm
What is the DDL for #tempRespPriceSummary table
Vinoj
April 9, 2008 at 10:57 am
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.
April 21, 2008 at 8:03 pm
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?
April 21, 2008 at 8:38 pm
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
April 22, 2008 at 11:01 am
I tried that option earlier; it yields the error "Incorrect syntax near the keyword 'as'.
April 22, 2008 at 11:05 am
Try it without the 'AS'.
April 22, 2008 at 11:11 am
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
April 22, 2008 at 11:47 am
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.
April 22, 2008 at 2:13 pm
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).
April 22, 2008 at 2:16 pm
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