CASE statement in SQL

[baseballGuy]

Hi Tim,

Below example of the CASE statement in SQL:

select table_name,
CASE owner
WHEN 'SYS' THEN 'dbms_service.blah;'
WHEN 'SYSTEM' THEN 'dbms_service.DUD'
ELSE 'The owner is another value'
END
from all_tables;

1. I don't think we can have more than ONE condition in the WHEN, for example: WHEN 'SYSTEM' and 'TEST'. b/c my second and third condition, are the same so I need to add at least one more condition to differentiate the two, how can I do that?

2. how can I call or write the result on the dbms_service to a file and also use the SQLPLUS to execute the result.

thx

[Tim...]

Hi.

1) You need a searched case expression to use multiple conditions in the single decision:

http://www.oracle-base.com/articles/9i/ ... 9i.php#SCE

2) You can only call PL/SQL functions in SQL, not procedures. The return value of the function will be displayed as the column value.

Code: Select all

select table_name,
CASE
WHEN 'SYS'  and .... THEN dbms_service.function-name
WHEN 'SYSTEM'  and ... THEN another-function
ELSE 'The owner is another value'
END
from all_tables;