on 11-01-2016 4:23 PM
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.
User | Count |
---|---|
86 | |
10 | |
10 | |
9 | |
7 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.