What’s that about then?
February 16th, 2006 by Peter Scott
At the risk of Tom Kyte telling me that I have given up thinking…
Any ideas why this appeared as part of a WHERE predicate (or more to the point why write it!)
…
AND
( case
when T.V_STATUS = ‘A’ then ‘Active’
when T.V_STATUS = ‘D’ then ‘Delisted’
else ‘Unknown Status’
end
) = ‘Active’ )
…
I might have said … AND T.V_STATUS = ‘A’
Extra information.
Just for fun I restricted the timespan of the query to a more sensible value and got the plan for the orignal and my new version. The original used star transforms and had a highly unlikely cardinality of 7! My version used mview rewrite and had a cardinality of 80,000. Run times were a bit different too; 2500 seconds for the case version and 245 seconds for mine!
And a later still update – there is posible performance gain for CASE to replace a NOT equals query
February 16th, 2006 at 5:59 pm
An FBI on that case statement? That’s the only functional reason for doing that. As for a more plausible explanation (in story format)
Query 1
SELECT
CASE
WHERE flag = 1 THEN ‘Active’
WHERE flag = 2 THEN ‘Inactive’
ELSE ‘Unknown’
END STATUS,
mrt.*
FROM MY_REPORT_TABLE mrt
Request 1
“Johnson, we need to make another report that summarizes our sales data for active accounts.”
Query 2
SELECT
SUM( sales )
FROM MY_REPORT_TABLE mrt
WHERE (
CASE
WHERE flag = 1 THEN ‘Active’
WHERE flag = 2 THEN ‘Inactive’
ELSE ‘Unknown’
END
) = ‘Active’
February 16th, 2006 at 7:09 pm
Bob
I can believe that – most likey lin fact!
February 16th, 2006 at 8:16 pm
You’re totally right – why are they using CASE when they could use DECODE? :)
February 16th, 2006 at 9:15 pm
I have seen this in an environment using Cognos. This was defined in the catalog initially to simplify the translation of a flag. Since it exists in the catalog, it can then be pulled in as a filter condition. Most of the time, the developers do not know what SQL gets generated when using these type of tools.
February 17th, 2006 at 9:28 pm
Isn’t is obvious?
They were using the rule based optmizer and wanted to avoid using an index on v_status!
;)