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

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

- Advertisement -