PLSQL Block, is it poossible to Ifnull this statement?

Question

I have a PL/SQL block that queries from an empty table. I was wondering is there any way to convert a blank result into 0? or at least us the ifnull function to this query?

Here is the code

declare 
p_entity_id varchar2(14) := 10100033904;
date_format varchar2(10) := 'MM/DD/YYYY';
v_mapcount integer;
v_compcount integer;
v_qa_comp_date varchar2(12) := 'MM/DD/YYYY';


begin 

SELECT SUM(total),SUM(complete),TO_CHAR(MAX(compDate),date_format) INTO v_mapCount,v_CompCount,v_qa_comp_date  FROM
(
SELECT COUNT(barcode) as total, SUM(DECODE(qa_comp,NULL,0,1)) complete ,MAX(qa_comp) compDate  from bbsp_boundary  WHERE upper(cycle1) = 'Y' AND  entity_id = p_entity_id AND DELETED_DATE IS NULL
    UNION ALL
SELECT COUNT(barcode) as total, SUM(DECODE(qa_comp,NULL,0,1)) complete ,MAX(qa_comp) compDate  from bbsp_feature WHERE upper(cycle1) = 'Y' AND  entity_id = p_entity_id AND DELETED_DATE IS NULL
    UNION ALL
SELECT COUNT(barcode) as total, SUM(DECODE(qa_comp,NULL,0,1)) complete ,MAX(qa_comp) compDate  from bbsp_ADDRESS WHERE upper(cycle1) = 'Y' AND  entity_id = p_entity_id AND DELETED_DATE IS NULL
    UNION ALL
SELECT COUNT(barcode) as total, SUM(DECODE(qa_comp,NULL,0,1)) complete ,MAX(qa_comp) compDate from bbsp_CORRECTION WHERE upper(cycle1) = 'Y' AND  entity_id = p_entity_id AND DELETED_DATE IS NULL
    UNION ALL
SELECT COUNT(barcode) as total, SUM(DECODE(qa_comp,NULL,0,1)) complete ,MAX(qa_comp) compDate  from bbsp_point_landmark WHERE upper(cycle1) = 'Y' AND  entity_id = p_entity_id AND DELETED_DATE IS NULL
    UNION ALL
SELECT COUNT(barcode) as total, SUM(DECODE(qa_comp,NULL,0,1)) complete ,MAX(qa_comp) compDate  from bbsp_area_landmark WHERE upper(cycle1) = 'Y' AND  entity_id = p_entity_id AND DELETED_DATE IS NULL
) ;

end;

Possible duplicate of [oracle equivalent of mysql ifnull (no case, no if)](http://stackoverflow.com/questions/31409990/oracle-equivalent-of-mysql-ifnull-no-case-no-if) — trincot, Mar 09 '16 at 18:29
the `SELECT` statement has two columns, so what do you mean with NVL() that? — trincot, Mar 09 '16 at 18:52

score 1 · Answer 1 · answered Mar 09 '16 at 23:20

If all of your tables are empty, then no row is returned, hence there is no count to return. You can reset those variables to zero with an if .. then ... end, or add the following right after the select statement:

   v_mapcount       := COALESCE (v_mapcount, 0);
   v_compcount      := COALESCE (v_compcount, 0);
   v_qa_comp_date   := COALESCE (v_qa_comp_date, 0);

Coalesce returns the first value if it is not null, otherwise it returns the second value.

score 0 · Answer 2 · edited Mar 10 '16 at 00:14

0

Try nvl() function

e.g: nvl(sum(total),0)

edited Mar 10 '16 at 00:14

sqluser

5,502
7
36
50

answered Mar 09 '16 at 18:30

Alex

21
2

PLSQL Block, is it poossible to Ifnull this statement?

2 Answers2