SQL Server Forums
Profile | Register | 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
 New Topic  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
37142 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
17650 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
37142 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
37142 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
17650 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
17650 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  
 New 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.06 seconds. Powered By: Snitz Forums 2000