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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 A SELECT statement that assigns a value to a varia
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

stevenandler
Starting Member

USA
42 Posts

Posted - 11/12/2013 :  19:10:53  Show Profile  Reply with Quote
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

USA
38200 Posts

Posted - 11/12/2013 :  19:17:24  Show Profile  Visit tkizer's Homepage  Reply with Quote
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/
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17689 Posts

Posted - 11/12/2013 :  19:18:47  Show Profile  Reply with Quote
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



KH
Time is always against us

Go to Top of Page

tkizer
Almighty SQL Goddess

USA
38200 Posts

Posted - 11/12/2013 :  19:25:16  Show Profile  Visit tkizer's Homepage  Reply with Quote
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/
Go to Top of Page

stevenandler
Starting Member

USA
42 Posts

Posted - 11/12/2013 :  19:33:27  Show Profile  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
38200 Posts

Posted - 11/12/2013 :  19:39:02  Show Profile  Visit tkizer's Homepage  Reply with Quote


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/
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17689 Posts

Posted - 11/12/2013 :  21:23:17  Show Profile  Reply with Quote
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
Time is always against us

Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17689 Posts

Posted - 11/12/2013 :  21:34:54  Show Profile  Reply with Quote
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
Time is always against us

Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 11/13/2013 :  13:27:04  Show Profile  Reply with Quote
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
			...
	)

Edited by - Lamprey on 11/13/2013 13:28:33
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.15 seconds. Powered By: Snitz Forums 2000