SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Declare variable? Local global? Nested select
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

DeNam
Starting Member

Sweden
15 Posts

Posted - 11/02/2013 :  11:23:26  Show Profile  Reply with Quote
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

Sweden
15 Posts

Posted - 11/02/2013 :  12:06:24  Show Profile  Reply with Quote
Maybe I declared @dt incorrectly.

I want it to replace a tablename
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/02/2013 :  13:18:45  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000