Oracle Case Statement Query

I am working on an Oracle Query to find if Diagnostic Pack is installed. Query is working fine.

Select case when currently_used = 'TRUE' then 1
else 0
end DIAGNOSTICS_package
from dba_feature_usage_statistics
where name like '%Diagnostic Pack%'
and CURRENTLY_USED = 'TRUE'

In the Output I need to add another Column Installed. So if currently_used = ‘TRUE’ I need the output to be displayed as below.

DIAGNOSTICS_package INSTALLED
1                   YES

I am running into an issue with the INSTALLED Column in the case statement. Please advise.

Thanks

Answer

The simplest answer to your question is, just add another CASE expression for the second column:

Select case when currently_used = 'TRUE' then 1
else 0
end DIAGNOSTICS_package,
case when currently_used = 'TRUE' then 'YES' else 'NO' end INSTALLED
from dba_feature_usage_statistics
where name like '%Diagnostic Pack%'
and CURRENTLY_USED = 'TRUE'

I don’t really get why you want two columns that tell you the same thing in different ways.

I also don’t get why you need a CASE statement at all, since you have and CURRENTLY_USED = 'TRUE' as part of the query conditions.

But maybe there is a larger context where these things make sense.