Ambiguous column name 'ITEM' - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Ambiguous column name 'ITEM'

Author

Topic

shifis
Posting Yak Master

157 Posts

Posted - 2003-04-16 : 14:32:13

I am doing 2 temporal tables, one have the inventory per item at certain date, and the other the sale as waste quantity per item in a range of dates.
I need to join them but I get the next error in the last select:
"Ambiguous column name 'ITEM'"

set nocount on
DROP TABLE #TEMP_TABLE2

CREATE TABLE #TEMP_TABLE2( ITEM CHAR(32), SOH_QTY DECIMAL(13,4))
INSERT #TEMP_TABLE2
SELECT DISTINCT ITEM, SOH_QTY
FROM dbo.ICTRANS b1
WHERE (CONVERT(CHAR(10), TRANS_DATE, 101) + CAST(UPDATE_TIME AS CHAR(6)) =
(SELECT CONVERT(CHAR(10), MAX(TRANS_DATE), 101) + CAST(MIN(UPDATE_TIME) AS CHAR(6)) AS FECH_HORA
FROM dbo.ICTRANS
WHERE (TRANS_DATE <= CONVERT(DATETIME, '2002-01-31 00:00:00', 102)) AND (LOCATION = 'TJU01') AND (ITEM = b1.ITEM)
GROUP BY LOCATION, ITEM))

DROP TABLE #TEMP_TABLE

CREATE TABLE #TEMP_TABLE( ITEM CHAR(32), SaleAmount DECIMAL(13,4),WasteAmount DECIMAL(13,4))
SELECT ITEM,
sum(Case when REASON_CODE='VTCL' then QUANTITY else 0 end) as SaleAmount,
sum(Case when REASON_CODE='MERM' then QUANTITY else 0 end) as WasteAmount
FROM dbo.ICTRANS
WHERE (COMPANY = 2000) AND (LOCATION = 'TJU01') AND (TRANS_DATE BETWEEN CONVERT(DATETIME, '2002-01-01 00:00:00', 102) AND
CONVERT(DATETIME, '2002-01-31 00:00:00', 102)) AND (REASON_CODE = 'MERM' OR
REASON_CODE = 'VTCL')
GROUP BY ITEM

SELECT #TEMP_TABLE .ITEM , #TEMP_TABLE.SaleAmount ,#TEMP_TABLE .WasteAmount, #TEMP_TABLE2.SOH_QTY
FROM #TEMP_TABLE

LEFT OUTER JOIN
#TEMP_TABLE2 ON #TEMP_TABLE.ITEM = #TEMP_TABLE2.ITEM

ORDER BY ITEM

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-04-16 : 14:39:52

I think you need to put the alias in front of places where you refer to the item field. It looks like you use item in several places, but not every place you refer to it as the Alias.Item.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-16 : 14:51:23

First Forget tjhe fully qualified alias. Also it's your order by that's causing the problem. And formatting your code so it's easier to read helps. Everyone has their own style. Here's mine:


   SELECT   l.ITEM 
	  , l.SaleAmount 
	  , l.WasteAmount
	  , r.SOH_QTY 
     FROM   #TEMP_TABLE  l 
LEFT JOIN   #TEMP_TABLE2 r 
       ON   l.ITEM = r.ITEM 
 ORDER BY   l.ITEM

Brett

8-)

shifis
Posting Yak Master

157 Posts

Posted - 2003-04-16 : 14:54:39

I forgot to put #TEMP_TABLE in the ORDER BY line.

SELECT #TEMP_TABLE2.SOH_QTY ,#TEMP_TABLE .ITEM ,
#TEMP_TABLE.SaleAmount ,#TEMP_TABLE .WasteAmount
FROM #TEMP_TABLE

LEFT OUTER JOIN
#TEMP_TABLE2 ON #TEMP_TABLE.ITEM = #TEMP_TABLE2.ITEM

ORDER BY #TEMP_TABLE .ITEM

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-16 : 16:10:43

Also, why bother with the temp tables at all:

Give this a try. Let me know if it doesn't work

Thanks


SELECT L.ITEM, L.QUANTITY, R.SalesAmount, R.WasteAmount
  FROM (
	  SELECT 
	DISTINCT ITEM, SOH_QTY 
	    FROM dbo.ICTRANS b1 
	   WHERE (CONVERT(CHAR(10), TRANS_DATE, 101) + CAST(UPDATE_TIME AS CHAR(6)) = 
		 (SELECT CONVERT(CHAR(10), MAX(TRANS_DATE), 101) + CAST(MIN(UPDATE_TIME) AS CHAR(6)) AS FECH_HORA 
		    FROM dbo.ICTRANS 
		   WHERE (TRANS_DATE <= CONVERT(DATETIME, '2002-01-31 00:00:00', 102)) 
		     AND (LOCATION = 'TJU01') AND (ITEM = b1.ITEM) 
	        GROUP BY LOCATION, ITEM))
	) AS L 
LEFT JOIN (
	SELECT    ITEM 
		, sum(Case when REASON_CODE='VTCL' then QUANTITY else 0 end) as SaleAmount 
		, sum(Case when REASON_CODE='MERM' then QUANTITY else 0 end) as WasteAmount 
	   FROM   dbo.ICTRANS 
	   WHERE (COMPANY = 2000) AND (LOCATION = 'TJU01') 
	     AND (TRANS_DATE BETWEEN CONVERT(DATETIME, '2002-01-01 00:00:00', 102) 
	     AND  CONVERT(DATETIME, '2002-01-31 00:00:00', 102)) AND (REASON_CODE = 'MERM' OR REASON_CODE = 'VTCL') 
	GROUP BY ITEM 
	) AS R
ODER BY L.ITEM

Brett

8-)

shifis
Posting Yak Master

157 Posts

Posted - 2003-04-16 : 17:21:12

I receive a error message:

Server: Msg 156, Level 15, State 1, Line 23
Incorrect syntax near the keyword 'ORDER'.

Is there something missing in the LEFT JOIN?

SELECT L.ITEM, L.SOH_QTY, R.SalesAmount, R.WasteAmount
FROM (
SELECT
DISTINCT ITEM, SOH_QTY
FROM dbo.ICTRANS b1
WHERE (CONVERT(CHAR(10), TRANS_DATE, 101) + CAST(UPDATE_TIME AS CHAR(6)) =
(SELECT CONVERT(CHAR(10), MAX(TRANS_DATE), 101) + CAST(MIN(UPDATE_TIME) AS CHAR(6)) AS FECH_HORA
FROM dbo.ICTRANS
WHERE (TRANS_DATE <= CONVERT(DATETIME, '2002-01-31 00:00:00', 102))
AND (LOCATION = 'TJU01') AND (ITEM = b1.ITEM)
GROUP BY LOCATION, ITEM))
) AS L
LEFT JOIN (
SELECT ITEM
, sum(Case when REASON_CODE='VTCL' then QUANTITY else 0 end) as SalesAmount
, sum(Case when REASON_CODE='MERM' then QUANTITY else 0 end) as WasteAmount
FROM dbo.ICTRANS
WHERE (COMPANY = 2000) AND (LOCATION = 'TJU01')
AND (TRANS_DATE BETWEEN CONVERT(DATETIME, '2002-01-01 00:00:00', 102)
AND CONVERT(DATETIME, '2002-01-31 00:00:00', 102)) AND (REASON_CODE = 'MERM' OR REASON_CODE = 'VTCL')
GROUP BY ITEM
) AS R
ORDER BY L.ITEM

Edited by - shifis on 04/16/2003 17:30:56

shifis
Posting Yak Master

157 Posts

Posted - 2003-04-16 : 17:32:45

I don't put the ON L.ITEM = R.ITEM in the left join line , that was the error.
Finally afther that it works!!!!
Thanks a lot!!!

SELECT L.ITEM, L.SalesAmount, L.WasteAmount, R.SOH_QTY
FROM(
SELECT ITEM
, sum(Case when REASON_CODE='VTCL' then QUANTITY else 0 end) as SalesAmount
, sum(Case when REASON_CODE='MERM' then QUANTITY else 0 end) as WasteAmount
FROM dbo.ICTRANS
WHERE (COMPANY = 2000) AND (LOCATION = 'TJU01')
AND (TRANS_DATE BETWEEN CONVERT(DATETIME, '2002-01-01 00:00:00', 102)
AND CONVERT(DATETIME, '2002-01-31 00:00:00', 102)) AND (REASON_CODE = 'MERM' OR REASON_CODE = 'VTCL')
GROUP BY ITEM
) AS L
LEFT JOIN (
SELECT
DISTINCT ITEM, SOH_QTY
FROM dbo.ICTRANS b1
WHERE (CONVERT(CHAR(10), TRANS_DATE, 101) + CAST(UPDATE_TIME AS CHAR(6)) =
(SELECT CONVERT(CHAR(10), MAX(TRANS_DATE), 101) + CAST(MAX(UPDATE_TIME) AS CHAR(6)) AS FECH_HORA
FROM dbo.ICTRANS
WHERE (TRANS_DATE <= CONVERT(DATETIME, '2002-01-31 00:00:00', 102))
AND (LOCATION = 'TJU01') AND (ITEM = b1.ITEM)
GROUP BY LOCATION, ITEM))
) AS R ON L.ITEM = R.ITEM
ORDER BY L.ITEM

Edited by - shifis on 04/16/2003 17:38:14

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-16 : 23:23:44

WOW...my bad sorry, glad you got it. Hope you can help me out some day.

Good Luck

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-16 : 23:28:51

Also cool [duude]

Someone [graz] showed ne how to post code:

do:


...what your code is

and it formats like a glove

just an fyi....

good lucj

ps I still cant type on th e home 'puter

Brett

8-)

SamC
White Water Yakist

3467 Posts

Posted - 2003-04-17 : 07:21:02

quote:
Also cool [duude]

Someone [graz] showed ne how to post code:

do:

...what your code is

and it formats like a glove

preface code with do: or "..." for formatting?

Sam

0x307836383635364336433646

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-04-17 : 07:57:51

Sam, prefix code with [ code ] [ /code ] tags (without the spaces), real neat.

Brett, looks like you home computer really needs a hammer to "unstuck" some keys...

SamC
White Water Yakist

3467 Posts

Posted - 2003-04-17 : 08:27:02

Let's try code


select abc from def where jkl

select abc 
from def
where jkl

Just to see what's special about the above formating.

Ah ha! It double spaces the lines.

Sam

0x307836383635364336433646

Edited by - SamC on 04/17/2003 08:27:45

samsekar
Constraint Violating Yak Guru

437 Posts

Posted - 2003-04-17 : 09:09:22

What is your signature Sam

Just want to know..
select convert(nvarchar(4000),convert(varbinary(4000), '0x307836383635364336433646'))

Sekar
~~~~
Success is not a destination that you ever reach. Success is the quality of your journey.

SamC
White Water Yakist

3467 Posts

Posted - 2003-04-17 : 09:27:18

If I post the sig, I'll have to remove it as a sig.

It's a varchar conversion to binary, once or twice. It isn't hard to figure out.

Sam

0x307836383635364336433646

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-17 : 09:31:28

Damn computer is a brand new (basically) HP Pavillion....need to get a clunky IBM keyboard!!!

Sam: All I get from the sig is TAB chars??????

Brett

8-)

SamC
White Water Yakist

3467 Posts

Posted - 2003-04-17 : 14:44:07

The sig is a single word run through

CAST(@word as Varbinary)
Take the resulting string

CAST(result as Varbinary)

Sam

I used the word 'Hello'. I thought it would be easy to crack.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources