Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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;

3 Replies
vishsaggi
Champion III
Champion III

Try adding this:

RP.CurveDate = '$(@pCurveDate@)'

Gysbert_Wassenaar

Replace @pCurveDate@ in the sql statements with '$(@pCurveDate@)'


talk is cheap, supply exceeds demand
pgrenier
Partner - Creator III
Partner - Creator III

Hello Tim,

You simply forgot to use the dollar sign expansion around your variable.

Also, don't forget that the resulting is a string, so apostrophes are necessary as Vish recommends in his answer.

Basically, in all your select statements, replace the occurrences of @pCurveDate@ by '$(@pCurveDate@)' and it should work fine.

Regards,

Philippe