PL/SQL: IF or CASE statement to check two variables in a single row and return a third variable?-CodePudding

I need PL/SQL code to scan a table (tied to page 5 in Apex), see if a row with two variables (a specific ID and a specific number between 1-3) exists, and return a third variable from that row into an Oracle Apex item (this is a dynamic action on page 10) if it does. I can do this with a SELECT statement and it works properly:

select VARIABLE_3 into :P10_ITEM from TABLE where (ID = :P10_ID and NUM = '1');

But I can't use that statement alone because if the row doesn't exist, it'll throw an error about missing data. So I need this as an IF or CASE. But when I try to write it as an IF or CASE instead, it returns nothing.

    if (:P10_ID = :P5_ID and :P5_NUM = '1') then
       select VARIABLE_3 
       into :P10_ITEM 
       from TABLE 
       where (ID = :P10_ID and NUM = '1');
    end if;

Sample data would be like (all columns are NUMBER types):

ID ... NUM ...  VARIABLE_3
-------------------------
10 ...  1  ...  23
10 ...  2  ...  24
11 ...  1  ...  25
11 ...  2  ...  26
11 ...  3  ...  27
12 ...  1  ...  28

It validates fine and throws no errors, but it doesn't give any result either. If I add an else :P10_ITEM := '0'; then it will return 0, so the submit/return is fine and I know the dynamic action is running. Any advice?

CodePudding user response：

Regarding the 1st SELECT statement you posted and your comments about it:

But I can't use that statement alone because if the row doesn't exist, it'll throw an error about missing data.

There is a simple way out of it - aggregate function.

For example, Scott's dept table:

SQL> select * From dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

This is your current query, and it ends up with no_data_found:

SQL> select dname from dept where deptno = 12 and loc = 'DALLAS';

no rows selected

Or, in PL/SQL (which is what Apex uses):

SQL> declare
  2    l_dname dept.dname%type;
  3  begin
  4    select dname
  5      into l_dname
  6      from dept
  7      where deptno = 12
  8        and loc = 'DALLAS';
  9  end;
 10  /
declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 4


SQL>

But, if you e.g. MAX it (the column), then the result is NULL, no error any more:

SQL> select max(dname) from dept where deptno = 12 and loc = 'DALLAS';

MAX(DNAME)
--------------

    
SQL> declare
  2    l_dname dept.dname%type;
  3  begin
  4    select max(dname)
  5      into l_dname
  6      from dept
  7      where deptno = 12
  8        and loc = 'DALLAS';
  9  end;
 10  /

PL/SQL procedure successfully completed.

SQL>

See? Now just check variable's value and do whatever you want to do.

In your case:

SELECT MAX(variable_3)
  INTO :P10_ITEM
  FROM some_table
 WHERE (    id = :P10_ID
        AND num = '1');
        
IF :P10_ITEM IS NOT NULL THEN 
   ... do something
ELSE
   ... do something else
END IF;