Skip to content

Instantly share code, notes, and snippets.

@phantom42
Created March 6, 2014 16:58
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save phantom42/9394278 to your computer and use it in GitHub Desktop.
Save phantom42/9394278 to your computer and use it in GitHub Desktop.
working CASE statement within a WHERE clause
DECLARE
v_filter varchar2(20) := 'All';
v_count number := 0 ;
BEGIN
dbms_output.enable(100000) ;
DBMS_output.put_line('running') ;
SELECT count(1) into v_count
FROM personnel p
WHERE NVL(P.TERMINATED_FLAG,'N') = 'N'
AND CASE WHEN (v_filter = 'Federal') THEN
DECODE(P.PERSONNEL_TYPE_ID,1,1,0)
WHEN (v_filter = 'Non-Federal') THEN
DECODE(p.personnel_type_id,1,0,1)
ELSE
1
END = 1
;
DBMS_output.put_line(v_count) ;
DBMS_output.put_line('finished') ;
END ;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment