IDT Parameters and Case Statments

[former_member965519]

I use derived tables exclusively and am trying to produce a runtime prompt the results in: caclulated start and end dates or ADHOC user entry of any start or end date.

Here is the code that worked in the UNV:

Start Date

and cd.insert_datetime >= CASE WHEN @Prompt('1.Select a Period Range','A',{'ADHOC Date Range','Prior Week Sat to Sun','Prior Month','If Not Adhoc you must still enter dates','but they are ignored.'},Mono,Free,persistent) = 'Prior Week Sat to Sun' then trunc(sysdate + 1, 'iw') - 8 when@Prompt('1.Select a Period Range','A',{'ADHOC Date Range','Prior Week Sat to Sun','Prior Month','If Not Adhoc you must still enter dates','but they are ignored.'},Mono,Free,persistent) = 'Prior Month' then TRUNC(LAST_DAY(ADD_MONTHS(sysdate, -2))) + 1 WHEN @Prompt('1.Select a Period Range','A',{'ADHOC Date Range','Prior Week Sat to Sun','Prior Month','If Not Adhoc you must still enter dates','but they are ignored.'},Mono,Free,persistent) = 'ADHOC Date Range' THEN cast( @Variable('2. Begin Date(MM/DD/YYYY)') as date) end

End Date

and cd.insert_datetime <= CASE WHEN @Prompt('1.Select a Period Range','A',{'ADHOC Date Range','Prior Week Sat to Sun','Prior Month','If Not Adhoc you must still enter dates','but they are ignored.'},Mono,Free,persistent) = 'Prior Week Sat to Sun' then trunc(sysdate + 1, 'iw') - 1 when@Prompt('1.Select a Period Range','A',{'ADHOC Date Range','Prior Week Sat to Sun','Prior Month','If Not Adhoc you must still enter dates','but they are ignored.'},Mono,Free,persistent) = 'Prior Month' then TRUNC(LAST_DAY(ADD_MONTHS(sysdate, -1))) WHEN @Prompt('1.Select a Period Range','A',{'ADHOC Date Range','Prior Week Sat to Sun','Prior Month','If Not Adhoc you must still enter dates','but they are ignored.'},Mono,Free,persistent) = 'ADHOC Date Range' THEN cast( @Variable('3.End Date(MM/DD/YYYY)') as date) end

Basically its a case statement:

Case when period_range = 'Prior Week" then sql for begin 1 week ago

when period_range = "Prior Month' then sql for begin date of prior month

when perio_range='Adhoc' user enters any date

With the IDT i am able to create the prompts but when i try and run the sql the data federater throws this error:

Error: [Data Federator Driver] [Server] Encountered "," at line 121, column 48. Was expecting one of: "and" ... "or" ... "in" ... "between" ... <COMPARISON_OPERATOR> ... "like" ... "not" ... "is" ... ")" ... "+" ... "-" ... "*" ... "/" ... "**" ... "//" ... "%" ... Cause of Error Encountered "," at line 121, column 48. Was expecting one of: "and" ... "or" ... "in" ... "between" ... <COMPARISON_OPERATOR> ... "like" ... "not" ... "is" ... ")" ... "+" ... "-" ...

here is the code in idt:

"derived table name"."received_date">= case when @Prompt(DatesGP)='Prior Week' then date_trunc('week', now())- INTERVAL '7days' when @Prompt(DatesGP)='Prior Month' then date_trunc('week', now())- INTERVAL '1days' when n@Prompt(DatesGP)='Adhoc' then cast( @Variable('2. Begin Date(MM/DD/YYYY)') as date) end

[TammyPowlas]

Thank you for your question; please note I changed the primary tag to Semantic Layer, as that is the tag used for IDT. Please be more careful when selecting tags in the future.