Call Procedure from CASE/WHEN 1

[ponderena]

I need to call a procedure from a CASE-WHEN statement, from a script. Is this permissible? I want to do something like:

SQL SCRIPT:

set autoprint on
set linesize 80
set serveroutput on

SELECT type,
(COUNT(CASE WHEN (called_procedure(field1,field2) = 1)
THEN (CASE WHEN EXISTS (SELECT 1
FROM TABLE
WHERE ....)
THEN NULL ELSE 1 END)
ELSE 0 END)) as alias1,
......
FROM TABLE;

--------

CREATE OR REPLACE procedure pairs_comb (v_field1 IN VARCHAR2, v_field2 IN NUMBER)
AS
begin
.....
.....
end;

Could someone please give me an example of how this could be done?

Thanks

ponderena

 

[Dagon]

In principle, it's certainly possible, although you would have to a use a function rather than a procedure. As to whether it's possible in your case, that would depend on the precise requirements.

Here's a simple example that counts an input number when it's even and also the current day in the month is even.

create or replace function iseven(inputno number) return number is
begin
  if mod(inputno,2) = 0 then
     return 1;
  else
    return 0;
  end if;
end;
/

SELECT COUNT(CASE WHEN IsEven(inpnum) = 1 
                          THEN 
                               CASE WHEN (SELECT iseven(to_number(to_char(sysdate, 'DD')))  FROM dual) = 0
                                  THEN NULL 
                               ELSE 1
                               END 
                          ELSE
                              NULL
                          END)  as 
FROM 
(SELECT 1 as inpnum
from dual
union 
select 2 as inpunum
from dual);

For Oracle-related work, contact me through Linked-In.

 

[ponderena]

Thanks, Dagon, for the example.

Ponderena