using a variable as part of field name in select

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

using a variable as part of field name in select

Author

Topic

rverdial
Starting Member

4 Posts

Posted - 2011-06-01 : 07:00:02

I have a variable that represents the month, lets call it @month. And depending on the month i have to choose a column in a select like p_01_x for @month=1, p_02_x for @month=2 and so on.
I'm using
DECLARE @MONTH AS VARCHAR(9)
SET @MONTH = (SELECT CASE WHEN RIGHT(VALUE,2)=1 THEN 'P_01_X'
WHEN RIGHT(VALUE,2)=2 THEN 'P_02_X' END AS COL
FROM TABLE)

SELECT TAX/@MONTH AS X
FROM...

ERROR:
Msg 245, Level 16, State 1, Line 6
Conversion failed when converting the varchar value 'P_01_X' to data type int.

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2011-06-01 : 07:15:06

what is the datatype of the column 'value' in the table?

rverdial
Starting Member

4 Posts

Posted - 2011-06-01 : 07:15:54

VARCHAR(255)

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2011-06-01 : 07:18:28

try this...
DECLARE @MONTH AS VARCHAR(9)
SET @MONTH = (SELECT CASE WHEN RIGHT(VALUE,2)='1' THEN 'P_01_X'
WHEN RIGHT(VALUE,2)='2' THEN 'P_02_X' END AS COL
FROM TABLE)

SELECT TAX/@MONTH AS X
FROM...

rverdial
Starting Member

4 Posts

Posted - 2011-06-01 : 07:30:31

That's not it. If i try
SELECT CASE WHEN @month=1 THEN TAX END AS X
it works

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-01 : 07:39:28

You are trying to divide the value in column TAX (which presumably is a numeric type) by a @month, which is of character type. The value in @month is 'P_01_X', which, obviously cannot be converted to a number.

Depending on whatyou are trying to do, this may be what you need:


declare @month int;
set @month = 2;
select
   case 
      when @month = 1 then P_01_X -- assuming P_01_X is the column name
      when @month = 2 then P_02_X
      ....
   end
from
   ...

rverdial
Starting Member

4 Posts

Posted - 2011-06-01 : 07:48:17

yes, P_01_X is a column name and i want it to be the same during each select.

declare @month int;
set @month = 2;
select
case
when @month <= 1 then P_02_X
when @month <= 2 then P_02_X
....
end
from
...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources