A SELECT statement that assigns a value to a varia

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

General SQL Server Forums

New to SQL Server Programming

A SELECT statement that assigns a value to a varia

Author

Topic

stevenandler
Starting Member

42 Posts

Posted - 2013-11-12 : 19:10:53

I am trying to figure out a way to retrieve a field value and assign it to a local variable with out destroying the whole structure of my T-SQL statement.

Here is the code:
DECLARE @AVERAGE_WHOLESALE_PRICE VARCHAR(20)
DECLARE @ORDERBY VARCHAR(20)
SELECT TOP 1 @AVERAGE_WHOLESALE_PRICE = P.NPT_PRICEX,
CASE NPT_TYPE
WHEN '07' THEN 1
WHEN '09' THEN 2
WHEN '05' THEN 3
END ORDERBY
FROM OGEN.NDC_M_PRICE P
WHERE P.NDC = '00006494300'
AND P.NPT_PRICEX > 0
AND NPT_TYPE IN ('07', '09','05')
AND P.NPT_DATEC =
(SELECT MAX(NPT_DATEC) FROM OGEN.NDC_M_PRICE
WHERE NDC = P.NDC)
ORDER BY ORDERBY

The error message is
Msg 141, Level 15, State 1, Line 3
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

Any ideas on how to modify this statement?

Thank you.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-11-12 : 19:17:24

select @AVERAGE_WHOLESALE_PRICE = NPT_PRICEX
from (
SELECT TOP 1 P.NPT_PRICEX,
CASE NPT_TYPE
WHEN '07' THEN 1
WHEN '09' THEN 2
WHEN '05' THEN 3
END ORDERBY
FROM OGEN.NDC_M_PRICE P
WHERE P.NDC = '00006494300'
AND P.NPT_PRICEX > 0
AND NPT_TYPE IN ('07', '09','05')
AND P.NPT_DATEC =
(SELECT MAX(NPT_DATEC) FROM OGEN.NDC_M_PRICE
WHERE NDC = P.NDC)
ORDER BY ORDERBY
) t

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-11-12 : 19:18:47

[code]SELECT TOP 1
@AVERAGE_WHOLESALE_PRICE = P.NPT_PRICEX,
@ORDERBY =
CASE NPT_TYPE
WHEN '07' THEN 1
WHEN '09' THEN 2
WHEN '05' THEN 3
END ~~ORDERBY~~[/code]

KH
[spoiler]Time is always against us[/spoiler]

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-11-12 : 19:25:16

quote:

Originally posted by khtan

SELECT TOP 1 
@AVERAGE_WHOLESALE_PRICE = P.NPT_PRICEX,
@ORDERBY = 
CASE NPT_TYPE
WHEN '07' THEN 1
WHEN '09' THEN 2
WHEN '05' THEN 3
END ORDERBY

I'm not following your solution. Isn't the ORDERBY column needed to determine which row to grab for TOP 1?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

stevenandler
Starting Member

42 Posts

Posted - 2013-11-12 : 19:33:27

Thank you very much. I was able to use the first solution. The second solution works but doesn't allow me to perform a needed sort

ORDER BY ORDERBY

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-11-12 : 19:39:02

I believe this will do it too:

SELECT TOP 1 @AVERAGE_WHOLESALE_PRICE = P.NPT_PRICEX
FROM OGEN.NDC_M_PRICE P
WHERE P.NDC = '00006494300'
AND P.NPT_PRICEX > 0
AND NPT_TYPE IN ('07', '09','05')
AND P.NPT_DATEC =
(SELECT MAX(NPT_DATEC) FROM OGEN.NDC_M_PRICE
WHERE NDC = P.NDC)
ORDER BY CASE NPT_TYPE
WHEN '07' THEN 1
WHEN '09' THEN 2
WHEN '05' THEN 3
END

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-11-12 : 21:23:17

quote:
Originally posted by tkizer

quote:
Originally posted by khtan

SELECT TOP 1
@AVERAGE_WHOLESALE_PRICE = P.NPT_PRICEX,
@ORDERBY =
CASE NPT_TYPE
WHEN '07' THEN 1
WHEN '09' THEN 2
WHEN '05' THEN 3
END ~~ORDERBY~~

I'm not following your solution. Isn't the ORDERBY column needed to determine which row to grab for TOP 1?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

oh .. i missed that

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-11-12 : 21:34:54

it can be simplified to

DECLARE @AVERAGE_WHOLESALE_PRICE VARCHAR(20)
SELECT 	TOP 1 
	@AVERAGE_WHOLESALE_PRICE = P.NPT_PRICEX
FROM 	OGEN.NDC_M_PRICE P
WHERE 	P.NDC = '00006494300'
AND 	P.NPT_PRICEX > 0
AND 	NPT_TYPE IN ('07', '09','05')
ORDER BY NPT_DATEC DESC,
	CASE 	NPT_TYPE
		WHEN '07' THEN 1
		WHEN '09' THEN 2
		WHEN '05' THEN 3
		END

KH
[spoiler]Time is always against us[/spoiler]

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-11-13 : 13:27:04

As a side note to the OP, if you only need to assign one variable you can use the a SET statement instead. The difference is that is you accidently left off the TOP clause it will (more than likely) generate an error instead of assigning the last or a random row to your variable. For example:

SET @AVERAGE_WHOLESALE_PRICE = 
	(
		SELECT TOP 1 
			P.NPT_PRICEX
		FROM 	
			OGEN.NDC_M_PRICE P
			...
	)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources