Mixing fields and variables in select statements

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

SQL Server Development (2000)

Mixing fields and variables in select statements

Author

Topic

Adde
Starting Member

9 Posts

Posted - 2005-09-13 : 01:32:50

Is there any way to mix variables and fields in a select statement in SQL Server 2000?

SELECT
x,
@y = (z / 100),
x * @y as TotValue
...
FROM
...
WHERE
...
GROUP BY
DateName(Year, Field.Date)

I've got some select statements with complex calculations, calculations that I only want to type once. Because I use group by, I cannot find out how to manage this without combining both variables and fields in the same query.

If I only had to calculate a total value, then its easy to use a variable that I put in a new select statement after the first calc has been exec.

Thanks in advance,
Adde

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-13 : 01:40:55

No you cannot use both column name and variable name in a single select statement
Instead you can use Derived column as something like this


Select *, (x * mycol) as TotValue from
(
	SELECT 	x, (z / 100) as mycol,
	...
	FROM
	...
	WHERE
	...
	GROUP BY DateName(Year, Field.Date)
) T

Madhivanan

Failing to plan is Planning to fail

Adde
Starting Member

9 Posts

Posted - 2005-09-13 : 02:05:57

Great! Thanks for your help!
/Adde

magesh
Starting Member

23 Posts

Posted - 2005-09-13 : 07:45:26

Hi,

u can use variables and columns together.
and also u can use variables and columns together in calculations.

but u cant assign a value to a variable in select statement.

Magesh

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources