Skip to Content
0

Using @prompt in derive table in Data foundation layer of IDT

Nov 01, 2016 at 04:23 PM

77

avatar image
Former Member

Hello experts,

I am finding a difficulty to use a parameter (i.e. prompt) created in Data Foundation of IDT.

I want to create a derive table and want to use the prompt available in DF layer.

Below is the SQL for derive table;

SELECT      max(cast(TABLE.COLNAME AS DATE)) as MAX_DAY_DT 

FROM      TABLE

WHERE     (Cast(TABLE.COLNAME as DATE) = add_months(cast(trim (substring(cast(201512 AS CHAR(30)),1,4)) || '_' || trim( substring(cast(201512 AS CHAR(30)),5,2)) '_' || '01' as date),0)

Above SQL for derive table runs fine, however if I use @prompt instead of hard coded year month value I get error.

SELECT      max(cast(TABLE.COLNAME AS DATE)) as MAX_DAY_DT

FROM      TABLE

WHERE     (Cast(TABLE.COLNAME as DATE) = add_months(cast(trim (substring(cast(@prompt(Year_Month) AS CHAR(30)),1,4)) || '_' || trim( substring(cast(@prompt(Year_Month)  AS CHAR(30)),5,2)) '_' || '01' as date),0)

Year_Month is universe parameter present in Data foundation layer in IDT.

Please help me how to use this prompt created in IDT in my derive table.

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

0 Answers