Declare variable? Local global? Nested 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

SQL Server 2012 Forums

Transact-SQL (2012)

Declare variable? Local global? Nested select

Author

Topic

DeNam
Starting Member

15 Posts

Posted - 2013-11-02 : 11:23:26

Hi,

Why doesnt @dt variable get recognized in the nested select statement. See code below

DECLARE @dt varchar(50),
@BF_date date;
SET @dt='VFA_UPPF..res_bilfakta';
SET @BF_date=(Select DATEADD (D, -(DATEPART(d,convert(date,GETDATE()))), convert(date,GETDATE())));
select
RegNR_BF,
Regdat_BF,
Vehicle_age,
Bilpris_BF,
Bilpris_m_moms_BF,
FordCBR_BF,
Fabrikat_BF,
Brands,
Month_low
Month_high,
Residual_Value_low,
Residual_value_high,
convert(numeric(5,2), (((Residual_value_high-Residual_Value_low)/(Month_high-Month_low)) * (Vehicle_age-Month_low)) + Residual_Value_low)
AS Market_value_pct,
convert(numeric(16,2),((((Residual_value_high-Residual_Value_low)/(Month_high-Month_low))
* (Vehicle_age-Month_low)) + Residual_Value_low)/100 * Bilpris_m_moms_BF) as Market_value_BF,
@BF_date
as BF_date
from
(
select REGNR as RegNR_BF, Regdat as Regdat_BF,
DATEDIFF(MONTH, Regdat, @BF_date)
as Vehicle_age,
convert(int, Bilpris) as Bilpris_BF, FOrdCBR as FordCBR_BF, Fabrikat as Fabrikat_BF,
CASE WHEN FordCBR='PB' and Fabrikat='AU' THEN 2
WHEN FordCBR='PB' and Fabrikat='PO' THEN 3
WHEN FordCBR='PB' and Fabrikat IN ('VW', 'SE', 'SK') THEN 1
WHEN FordCBR='LB' and Fabrikat IN ('VW', 'SE', 'SK', 'PO', 'AU') THEN 5
ELSE 2 END as Brand_code,
convert(int, Bilpris) * 1.25 as Bilpris_m_moms_BF
from @dt as b1
where Regdat = (select MAX(Regdat) from @dt as b2 where b1.RegNR=b2.RegNR)
and FAKTDAT = (select MAX(faktdat) from @dt as b2 where b1.RegNR=b2.RegNR)
and Bilpris>0 and ISDATE(Regdat)=1
) as T1
left outer join VFA_UPPF..Market_values_v0_2 as T2 on T1.Vehicle_age between T2.Month_low and T2.Month_high
and T1.Brand_code=T2.[Brand code]

DeNam
Starting Member

15 Posts

Posted - 2013-11-02 : 12:06:24

Maybe I declared @dt incorrectly.

I want it to replace a tablename

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-02 : 13:18:45

you cant pass tablename from variable like this.For that you need to use dynamic sql

see

http://www.sommarskog.se/dynamic_sql.html#sp_executesql

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources