Quick syntax question - 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)

Quick syntax question

Author

Topic

bszom
Starting Member

9 Posts

Posted - 2006-05-20 : 16:58:36

Is it permissable to do something like SUM(table1.column * table2.column)
in a select query with table joins?

I'm trying to do just that, but am getting following error message:
Server: Msg 8624, Level 16, State 3, Line 6
Internal SQL Server error.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-05-20 : 17:45:34

Yes it's ok.
Sounds like you may be getting a bug.
Do you have a derived table in a join?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

bszom
Starting Member

9 Posts

Posted - 2006-05-20 : 18:30:17

Nah, this is all it is:

DECLARE @rcptotcost smallmoney
DECLARE @Recipe_ID smallint

SELECT @Recipe_ID = 1
SELECT @rcptotcost = (SELECT SUM(c.quantity * pc.price) AS [total cost]
FROM recipe AS r INNER JOIN content AS c
ON r.recipe_id = c.recipe_id
INNER JOIN ingredient AS i
ON c.ingred_id = i.ingred_id
INNER JOIN pricechange AS pc
ON i.ingred_id = pc.ingred_id
AND pc.startdate = (SELECT MAX(startdate) FROM pricechange AS x WHERE x.ingred_id = pc.ingred_id)
WHERE r.recipe_id = @Recipe_ID)

It's just a snippet from a procedure.
I guess I should get the latest service packs and all that.... sigh. :P

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2006-05-20 : 19:26:13

You should always have the latest service packs.

That should be regardless of whether you encounter a bug or not.

Kewl. This produces the same error on my machine:


DECLARE @rcptotcost smallmoney
DECLARE @Recipe_ID smallint

DECLARE @recipe TABLE(
	recipe_id INT)

DECLARE @ingredient TABLE(
	ingred_id INT)

DECLARE @content TABLE(
	content_id INT,
	recipe_id INT,
	ingred_id INT,
	quantity INT)

DECLARE @pricechange TABLE(
	ingred_id INT,
	price MONEY,
	startdate DATETIME)

INSERT @recipe(recipe_id)
	SELECT 1 UNION ALL
	SELECT 2

INSERT @ingredient(ingred_id)
	SELECT 1 UNION ALL
	SELECT 2 UNION ALL
	SELECT 3 UNION ALL
	SELECT 4

INSERT @content(content_id, recipe_id, ingred_id, quantity)
	SELECT 1,1,1,5 UNION ALL
	SELECT 2,1,2,5 UNION ALL
	SELECT 3,1,3,5 UNION ALL
	SELECT 4,1,4,5 UNION ALL
	SELECT 5,2,1,5 UNION ALL
	SELECT 6,2,2,5 UNION ALL
	SELECT 7,2,3,4 UNION ALL
	SELECT 8,2,4,5

INSERT @pricechange(ingred_id, price, startdate)
	SELECT 1,.50,'01/01/2005' UNION ALL
	SELECT 2,3.50,'01/01/2005' UNION ALL
	SELECT 3,2.50,'01/01/2005' UNION ALL
	SELECT 4,5.50,'03/01/2005' UNION ALL
	SELECT 3,6.50,'04/01/2005' UNION ALL
	SELECT 4,7.50,'04/01/2005' UNION ALL
	SELECT 4,7.75,'04/02/2005'

SELECT @Recipe_ID = 1

SELECT @rcptotcost = (
	SELECT SUM(c.quantity * pc.price) AS [total cost]
	FROM 
		@recipe AS r 
		INNER JOIN @content AS c ON r.recipe_id = c.recipe_id
		INNER JOIN @ingredient AS i ON c.ingred_id = i.ingred_id
		INNER JOIN @pricechange AS pc ON i.ingred_id = pc.ingred_id
			AND pc.startdate = (
				SELECT MAX(startdate) 
				FROM @pricechange AS x 
				WHERE x.ingred_id = pc.ingred_id)
	WHERE r.recipe_id = @Recipe_ID)

I have SP4 on my machine.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2006-05-20 : 19:30:38

This DOES NOT return the internal error. It will give you the same results.


DECLARE @rcptotcost smallmoney
DECLARE @Recipe_ID smallint

DECLARE @recipe TABLE(
	recipe_id INT)

DECLARE @ingredient TABLE(
	ingred_id INT)

DECLARE @content TABLE(
	content_id INT,
	recipe_id INT,
	ingred_id INT,
	quantity INT)

DECLARE @pricechange TABLE(
	ingred_id INT,
	price MONEY,
	startdate DATETIME)

INSERT @recipe(recipe_id)
	SELECT 1 UNION ALL
	SELECT 2

INSERT @ingredient(ingred_id)
	SELECT 1 UNION ALL
	SELECT 2 UNION ALL
	SELECT 3 UNION ALL
	SELECT 4

INSERT @content(content_id, recipe_id, ingred_id, quantity)
	SELECT 1,1,1,5 UNION ALL
	SELECT 2,1,2,5 UNION ALL
	SELECT 3,1,3,5 UNION ALL
	SELECT 4,1,4,5 UNION ALL
	SELECT 5,2,1,5 UNION ALL
	SELECT 6,2,2,5 UNION ALL
	SELECT 7,2,3,4 UNION ALL
	SELECT 8,2,4,5

INSERT @pricechange(ingred_id, price, startdate)
	SELECT 1,.50,'01/01/2005' UNION ALL
	SELECT 2,3.50,'01/01/2005' UNION ALL
	SELECT 3,2.50,'01/01/2005' UNION ALL
	SELECT 4,5.50,'03/01/2005' UNION ALL
	SELECT 3,6.50,'04/01/2005' UNION ALL
	SELECT 4,7.50,'04/01/2005' UNION ALL
	SELECT 4,7.75,'04/02/2005'

SELECT @Recipe_ID = 1

SELECT @rcptotcost = (
	SELECT SUM((c.quantity * pc.price)) AS [total cost]
	FROM 
		@recipe AS r 
		INNER JOIN @content AS c ON r.recipe_id = c.recipe_id
		INNER JOIN @ingredient AS i ON c.ingred_id = i.ingred_id
		INNER JOIN (
			SELECT MAX(startdate) AS startdate, ingred_id
			FROM @pricechange
			GROUP BY ingred_id) dt ON i.ingred_id = dt.ingred_id
		INNER JOIN @pricechange AS pc ON dt.ingred_id = pc.ingred_id
			AND dt.startdate = pc.startdate
	WHERE r.recipe_id = @Recipe_ID)

SELECT @rcptotcost

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-05-20 : 19:33:29

It's probably the correlated subquery in an aggregate
try
SELECT @rcptotcost = (SELECT SUM(c.quantity * pc.price) AS [total cost]
FROM recipe AS r INNER JOIN content AS c
ON r.recipe_id = c.recipe_id
INNER JOIN ingredient AS i
ON c.ingred_id = i.ingred_id
INNER JOIN pricechange AS pc
ON i.ingred_id = pc.ingred_id
inner join (select ingred_id, startdate = MAX(startdate) from @pricechange group by ingred_id) a
on a.ingred_id = pc.ingred_id
and a.startdate = pc.startdate
WHERE r.recipe_id = @Recipe_ID)

where's that snipe icon?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2006-05-20 : 20:05:51