ORACLE-SQL: Function, how to detect a null selection and return 0

user_11215814 0
Viewed 107

I have wrote the following function. But at the end I'm not able when count is empty to detect it and put in the result variable returning 0; I don't know what is wrong, maybe count(*) should be put by an external SELECT, no sure.

Running for example, random input:

SELECT is_department_active('44','222') from dual

I got is_department_active (null) but expected 0.

 CREATE OR REPLACE FUNCTION is_department_active (
        department_id   VARCHAR2,
        doc_id      VARCHAR2
    ) RETURN NUMBER IS
        result NUMBER(1);  
        cnt number(3);
    BEGIN
    result:=0;
        SELECT
            dep_active, count(*)
        INTO result,cnt
        FROM
            (
           SELECT
            grade,
            dep_active,  3 "priority number"
        FROM
            vrsc.inst_department
        WHERE
            af_feature_id = department_id
            AND grade = 'DOC'
            AND grade_field = doc_id
        UNION
        SELECT grade,
            dep_active,
            1 "priority number"
        FROM
            vrsc.inst_department
        WHERE
            af_feature_id = department_id
            AND grade = 'CUSTOMER'
            AND grade_field IN (
                SELECT
                    doc_cust_id
                FROM
                    customer_docs
                WHERE
                    doc_id = doc_id)
        UNION
        SELECT
            grade,
            dep_active,
            0 "priority number"
        FROM
            vrsc.inst_department
        WHERE
            af_feature_id = department_id
            AND grade = 'DEFAULT'
            AND GRADE_FIELD = 'DEFAULT'
        UNION
        SELECT
            grade, dep_active,
            2 "priority number"
        FROM
            vrsc.groups_docs left
            JOIN vrsc.inst_department ON grade = 'GROUP'
                                      AND grade_field = gs_group_id
                                      AND grade_sec_field = gs_doc_code
        WHERE
            gs_doc_id = doc_id
            AND af_feature_id = feature_id
        ORDER BY
            "priority number" DESC
            )
        WHERE
            ROWNUM = 1 group by dep_active;
            if cnt=0 then
            result:=0;
            end if;
    
        RETURN result;
    END;

I would like to take the first row wit priority 3, but if this query is null, empty then I can't return 0 as DEFAULT.

enter image description here

count function oracle sql
3 Answers

After you edited the question and included the whole code, there's nothing much I can say about it; I trust you that query you wrote actually decides whether department is active or not.

I'd suggest such a code - simplified - to decide the final result:

with temp as
  (select bunch of things you currently select)
select nvl(max(1), 0)
  into result
  from dual
  where exists (select null from temp);

No IFs, ROWNUMs, COUNTs ... as simple as that (while "bunch of things" is complex, though).

Or, put into the function:

create or replace function 
  is_department_active (department_id in varchar2, doc_id in varchar2)
  return number 
is
  result number(1);
begin
  with temp as(select grade,
                    dep_active,
                    3 "priority number"
             from vrsc.inst_department
             where af_feature_id = department_id
                   and grade = 'doc'
                   and grade_field = doc_id
             union
             select grade,
                    dep_active,
                    1 "priority number"
             from vrsc.inst_department
             where af_feature_id = department_id
                   and grade = 'CUSTOMER'
                   and grade_field in(select doc_cust_id
                                from customer_docs
                                where doc_id = doc_id
                                     )
             union
             select grade,
                    dep_active,
                    0 "priority number"
             from vrsc.inst_department
             where af_feature_id = department_id
                   and grade = 'DEFAULT'
                   and grade_field = 'DEFAULT'
             union
             select grade,
                    dep_active,
                    2 "priority number"
             from vrsc.groups_docs left
             join vrsc.inst_department on grade = 'GROUP'
                                          and grade_field = gs_group_id
                                          and grade_sec_field = gs_doc_code
             where gs_doc_id = doc_id
                   and af_feature_id = feature_id
             order by "priority number" desc)
  select nvl(max(1), 0)
  into result
  from temp
  where exists (select null from temp);

  return result;
end;

See if it helps.

user_90979060

Solved.

From my original function I substitute IF statement with EXCEPTION and removed the counter

--previous code  without IF   
RETURN result;
    
      EXCEPTION
        WHEN NO_DATA_FOUND
        THEN
        RETURN 0;
    END;
user_112158140

See the NVL function, and try this:

  if nvl(cnt,0) = 0 then
    result:=0;
  end if;
user_17455440
Related