3 Replies Latest reply: Aug 17, 2016 11:41 AM by Gysbert Wassenaar RSS

    SQL query.  How do I set a variable to replace Declare?

    Tim Lathrop

      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;