Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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
Not applicable

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

Try adding this:

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

Gysbert_Wassenaar
Not applicable

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

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


talk is cheap, supply exceeds demand
pgrenier
Not applicable

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

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