Skip to Content
avatar image
Former Member

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

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.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

0 Answers