More slow cases

Peter Scott

Feb 17, 2006 • 1 min read

Yesterday I had a small rant about the use of case statements in where clauses. In a reply to this parkerpg suggested this was perhaps generated by the use of query environment such as Cognos. In this case we are not using Cognos but I think the principle holds good and we are seeing inappropriate use of ‘drag and drop’ (or is it droop?) reporting tools

Still, once you see one example you are on a roll and find other examples in slow running queries. Today’s example is

case when upper(( '160206' )) = 'TODAY' then trunc(sysdate) when upper(( '160206' )) = 'YESTERDAY' then trunc(sysdate) - 1 when length(trim(translate(( '160206' ),'+-.0123456789',' '))) is null then sysdate - to_number(( '160206' ))-1 else to_date(( '160206' )) end

as one of the date values in a between predicate. I can suspect that the constant ‘160206’ is supplied as an argument to a prompt window. So if the user enters TODAY it is translated to sysdate and so on.

Did you know that 160206 days ago it was 22nd June 1567? And that the user’s query brings back a tad too much data (well it would if it completed…)

Sign up for more like this.