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

Tue, 16 Aug 2016 22:28:32 GMT

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;

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

vishsaggi — Wed, 17 Aug 2016 15:37:26 GMT

Try adding this:

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

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

Gysbert_Wassenaar — Wed, 17 Aug 2016 15:41:34 GMT

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

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

pgrenier — Wed, 17 Aug 2016 15:44:42 GMT

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

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

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

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

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

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