I want to be able to run query daily and declare (or set or Let) @pCurveDate@ be a specific date. I am getting the error message below.
"Must declare the scalar variable "@pCurveDate@"."
This is my script....
SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';
SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';
SET FirstWeekDay=6;
SET BrokenWeeks=1;
SET ReferenceDay=0;
SET FirstMonthOfYear=1;
SET CollationLocale='en-US';
Let @pCurveDate@='8/3/2016';
Curves:
Load*;
ODBC CONNECT TO [xxx data] (UserID is xxx, Password is "xxx");
SELECT RP.CurveDate,
C.CurveUID,
C.CurveName,
C.Symbol,
CT.CurveTypeUID,
CT.CurveType,
RskT.RiskTypeUID,
RskT.RiskType,
RP.RangeBegDate AS ForwardDate,
Value,
CASE RngT.RangeTypeUID
WHEN 0 THEN 'Monthly Forward'
ELSE RngT.RangeType
END AS Type,
RngT.RangeTypeUID,
RngT.RangeType
FROM enstep.Curves C
JOIN enstep.RiskTypes RskT ON RskT.RiskTypeUID = C.RiskTypeUID
JOIN enstep.CurveTypes CT ON CT.CurveTypeUID = C.CurveTypeUID
JOIN enstep.RangeTypes RngT ON RngT.RangeTypeUID = C.RangeTypeUID
JOIN enstep.RangePeriods RP on RP.CurveDate=@pCurveDate@ AND RP.RangeTypeUID=C.RangeTypeUID
LEFT OUTER JOIN enstep.CurveRangeValues CRV on CRV.CurveUID=C.CurveUID AND CRV.CurveDate = RP.CurveDate AND CRV.RangeSEQ = RP.RangeSEQ
where C.InputDerivedFlag = 'I'
AND C.RangeTypeUID not in (3, 19) --Not CurrentMonth or Realized
UNION ALL
SELECT @pCurveDate@ AS CurveDate,
C.CurveUID,
C.CurveName,
C.Symbol,
CT.CurveTypeUID,
CT.CurveType,
RskT.RiskTypeUID,
RskT.RiskType,
RP.CurveDate AS ForwardDate,
Value,
CASE RngT.RangeTypeUID
WHEN 0 THEN 'Daily'
ELSE RngT.RangeType
END AS Type,
RngT.RangeTypeUID,
RngT.RangeType
FROM enstep.Curves C
JOIN enstep.RiskTypes RskT ON RskT.RiskTypeUID = C.RiskTypeUID
JOIN enstep.CurveTypes CT ON CT.CurveTypeUID = C.CurveTypeUID
JOIN enstep.RangeTypes RngT ON RngT.RangeTypeUID = C.RangeTypeUID
LEFT OUTER JOIN enstep.RangePeriods RP on RP.CurveDate=enuit.FirstDayOfMonth(@pCurveDate@) AND RP.RangeTypeUID=C.RangeTypeUID
LEFT OUTER JOIN enstep.CurveRangeValues CRV on CRV.CurveUID=C.CurveUID AND CRV.CurveDate = RP.CurveDate AND CRV.RangeSEQ = RP.RangeSEQ
where C.InputDerivedFlag = 'I'
AND C.RangeTypeUID = 3 -- CurrentMonth
AND C.RiskTypeUID <> 8
UNION ALL
SELECT RP.CurveDate,
C.CurveUID,
C.CurveName,
C.Symbol,
CT.CurveTypeUID,
CT.CurveType,
RskT.RiskTypeUID,
RskT.RiskType,
RP.CurveDate AS ForwardDate,
Value,
CASE RngT.RangeTypeUID
WHEN 0 THEN 'Daily'
ELSE RngT.RangeType
END AS Type,
RngT.RangeTypeUID,
RngT.RangeType
FROM enstep.Curves C
JOIN enstep.RiskTypes RskT ON RskT.RiskTypeUID = C.RiskTypeUID
JOIN enstep.CurveTypes CT ON CT.CurveTypeUID = C.CurveTypeUID
JOIN enstep.RangeTypes RngT ON RngT.RangeTypeUID = C.RangeTypeUID
LEFT OUTER JOIN enstep.RangePeriods RP on RP.CurveDate=@pCurveDate@ AND RP.RangeTypeUID=C.RangeTypeUID
LEFT OUTER JOIN enstep.CurveRangeValues CRV on CRV.CurveUID=C.CurveUID AND CRV.CurveDate = RP.CurveDate AND CRV.RangeSEQ = RP.RangeSEQ
where C.InputDerivedFlag = 'I'
AND C.RangeTypeUID = 3 -- CurrentMonth
AND C.RiskTypeUID = 8;
