Anyway to declare and use variables in a View? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Anyway to declare and use variables in a View?

Author

Topic

jcarrallo
Starting Member

35 Posts

Posted - 2014-09-12 : 14:15:25

Hello,
Thanks for helping.

I have a select statement that works fine. Now I need to create a view with this statement and I have problems with the variables that I need to declare.

Can anyone help? Is there a way to include the declare command in a view?
I can not use a stored procedure this time.
Many thanks,
Jay

This is the statement:
_____________________________________

DECLARE @FirstDate DATE,
@SecondDate DATE,
@ThirdDate DATE

SELECT @FirstDate = MAX(fecha_valor) FROM MPR_Historico_Posiciones
---SELECT @SecondDate = MAX(fecha_valor) FROM MPR_Historico_Posiciones WHERE fecha_valor<> @FirstDate
SELECT @SecondDate = dateadd(week,-1,max(fecha_valor)) FROM MPR_Historico_Posiciones
---SELECT @ThirdDate = dateadd(week,-2,max(fecha_valor)) FROM MPR_Historico_Posiciones

SELECT description,
CAST(SUM(Case fecha_valor when @FirstDate THEN valor_div_global ELSE 0 END) AS numeric(12, 3)) as col_1_Valor,
CAST(SUM(Case fecha_valor when @FirstDate THEN mar_price ELSE 0 END) AS numeric(12, 3)) as col_1_Precio,
CAST(SUM(Case fecha_valor when @FirstDate THEN plusvalia_global ELSE 0 END) AS numeric(12, 3)) as col_1_Result,
CAST(SUM(Case fecha_valor when @SecondDate THEN valor_div_global ELSE 0 END) AS numeric(12, 3)) as col_2_Valor,
CAST(SUM(Case fecha_valor when @SecondDate THEN mar_price ELSE 0 END) AS numeric(12, 3)) as col_2_Precio,
CAST(SUM(Case fecha_valor when @SecondDate THEN plusvalia_global ELSE 0 END) AS numeric(12, 3)) as col_2_Result

FROM MPR_Historico_Posiciones
GROUP BY description,symbol;

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-09-12 : 14:24:08

You can't use variables in a view. Why does it need to be a view?

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

jcarrallo
Starting Member

35 Posts

Posted - 2014-09-12 : 15:36:16

I see.
I need a view because I use alpha five for the front end to generate a grid, and with this type of statement Alpha Five would not be able to identify the various columns resulting from the statement.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources