i am having some issues converting some passthrough code from Oracle to Snowflake for date and time intervals.
The original code form oracle passthrough is this:
and (( o.order_timestamp >= trunc(to_date(%str(%'&Startdate%')),'ddmonyyyy') and
o.order_timestamp < to_date(to_char(to_date(%str(%'&Startdate%'),'ddmonyyyy'),'yyyymmdd')||decode(%str(%'&interval%')||'00','240000','235959',%str(%'&interval%')||'00'),'yyyymmddhh24miss') or
o.order_timestamp >= trunc(to_date(%str(%'&Startdate%')),'ddmonyyyy')-364 and
o.order_timestamp < to_date(to_char(to_date(%str(%'&Startdate%'),'ddmonyyyy')-364,'yyyymmdd')||decode(%str(%'&interval%')||'00','240000','235959',%str(%'&interval%')||'00'),'yyyymmddhh24miss')))
i took of the trunc statements like this:
and (( o.order_timestamp >= (to_date(%str(%'&Startdate%')),'ddmonyyyy') and
o.order_timestamp < to_date(to_char(to_date(%str(%'&Startdate%'),'ddmonyyyy'),'yyyymmdd')||decode(%str(%'&interval%')||'00','240000','235959',%str(%'&interval%')||'00'),'yyyymmddhh24miss') or
o.order_timestamp >= (to_date(%str(%'&Startdate%')),'ddmonyyyy')-364 and
o.order_timestamp < to_date(to_char(to_date(%str(%'&Startdate%'),'ddmonyyyy')-364,'yyyymmdd')||decode(%str(%'&interval%')||'00','240000','235959',%str(%'&interval%')||'00'),'yyyymmddhh24miss
The parameter code looks like this:
call symput("Startdate",put("&START_DATE"d,yymmdd10.));
call symput("Begindate",put("&START_DATE"d,yymmdd10.));
call symput("Enddate",put("&START_DATE"d,yymmdd10.));
When i ran the project with the modified date code, i received no errors and a blank page.
From there i tried my simple date code just to see if was that part of the code was truly the issue:
and o.order_timestamp >= dateadd(dd,-0,%str(%')&Startdate%str(%'))
and o.order_timestamp <= dateadd(dd,+1,%str(%')&Startdate%str(%'))
When i replaced the date code with this it worked perfectly and i was able to see all my orders for the previous day. The issue is those two DECODE statements are required here. There are several other pieces to this code and i am doing the conversion for them but i can't figure out the right syntax to make it work. I don't likely care what i use in the end as long as it gives me the interval data.
Can someone please help with the syntax... i am been working on this for 3 hours now and am just beating my head.
Thanks,
Dean
... View more
