Oracle SQL: in 'WITH' statement, how to avoid empty result row in conditional aggregate function select from different table

Question

I have two tables:

CREATE TABLE Test_Persons_A (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255)
);

INSERT INTO Test_Persons_A
    (PersonID,LastName,FirstName)
    values(11,'LN_A1','FN_A1');

INSERT INTO Test_Persons_A
    (PersonID,LastName,FirstName)
    values(12,'LN_A2','FN_A2');

CREATE TABLE Test_Persons_B (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255)
);

INSERT INTO Test_Persons_B
    (PersonID,LastName,FirstName)
    values(21,'LN_B1','FN_B1');

INSERT INTO Test_Persons_B
    (PersonID,LastName,FirstName)
    values(22,'LN_B2','FN_B2');

commit;

I want to conditionally use aggregation function on only one of the tables with IF and UNION ALL within WITH clause (based on this post https://stackoverflow.com/a/51330601/2041023). but is there an easier way to do this, without having to add another SELECT wrapping around this one like the following?

---------- use IF and UNION ALL, aggregation function COUNT, but return two rows ----------
var TEST_TBL varchar2(20);
exec :TEST_TBL := 'test_persons_a';

with Test_count_id as (
      select COUNT(PersonID) as CNT_PID
      from Test_Persons_A
      where UPPER(:TEST_TBL) = 'TEST_PERSONS_A'
      union all
      select COUNT(PersonID) as CNT_PID
      from Test_Persons_B
      where UPPER(:TEST_TBL) = 'TEST_PERSONS_B'
     )

select * from Test_count_id

result:

   CNT_PID
----------
         2
         0

The inconvenience is even more obvious here:

---------- use IF and UNION ALL, aggregation function MAX, but return two rows, one of which is empty ----------
var TEST_TBL varchar2(20);
exec :TEST_TBL := 'test_persons_a';

with Test_max_id as (
      select MAX(PersonID) as Max_PID
      from Test_Persons_A
      where upper(:TEST_TBL) = 'TEST_PERSONS_A' 
      union all
      select MAX(PersonID) as Max_PID
      from Test_Persons_B
      where UPPER(:TEST_TBL) = 'TEST_PERSONS_B'
     )

select * from Test_max_id

result:

   MAX_PID
----------
        12
           (-- empty row)

So, how I can do this in a clean way?

EDIT: removed the extra question to avoid confusion

FURTHER EDIT: if I add another SELECT to wrap the SELECT ... UNION ALL ... SELECT, it kind of works, but like I said, I am hoping to avoid this ugliness:

---------- use IF and UNION ALL, and a wrapper, aggregation function MAX, but return one row ----------
var TEST_TBL varchar2(20);
exec :TEST_TBL := 'test_persons_a';

with Test_max_id1 as (
        select Max_PID from ( 
              select MAX(PersonID) as Max_PID
              from Test_Persons_A
              where UPPER(:TEST_TBL) = 'TEST_PERSONS_A' 
              union all
              select MAX(PersonID) as Max_PID
              from Test_Persons_B
              where UPPER(:TEST_TBL) = 'TEST_PERSONS_B'
        ) where Max_PID is NOT NULL
     )

select * from Test_max_id1

result:

   MAX_PID
----------
        12

score 1 · Answer 1 · answered Jul 18 '18 at 23:23

1

Yes, there is. Adapt this example as needed. The logic is to add an extra column to the UNION ALL, to keep track of the source table of each row; and use that in your condition.

with
  test_count_id as (
    select person_id, last_name, first_name, 'TEST_PERSONS_A' as source_table
      from test_persons_a
    union all
    select person_id, last_name, first_name, 'TEST_PERSONS_B'
      from test_persons_b
  )
select count(person_id)
from   test_count_id
where  upper(:test_tbl) = source_table
;

answered Jul 18 '18 at 23:23

thanks, it's a sound idea. But I think the full union all will have significant impact on performance, hence will perhaps not be acceptable to my specific case. – Stochastika Jul 18 '18 at 23:40
@Stochastika - I suggest you give it a try. The Oracle optimizer will push the predicate (the `WHERE` condition) to each member of the `UNION ALL`. It will not touch the base table for which the predicate becomes false. If you are not familiar with the concepts, just test it and you will see. – Jul 19 '18 at 00:18
@Stochastika - Another way to code the same idea is to have the `WHERE` clause in each branch of `UNION ALL` (and to do away with the extra column). `with test_count_id as ( select person_id from test_person_a where upper(:test_tbl) = 'TEST_PERSONS_A' union all ... ) select count(persion_id) from test_count_id`. The Optimizer will transform the query in my solution into this second version (in this Comment). You can write it directly this way if you prefer. – Jul 19 '18 at 00:28

sticky bit · Answer 2 · 2018-07-19T00:07:39.830

If I understood this right, your problem is, that your current queries return two rows. You only want one row.

You could do the UNION ALL first, also selecting a literal indicating the table the row comes from. Then filter on that indicator in the aggregation query.

WITH TEST_COUNT_ID
AS
(
SELECT COUNT(PERSONID) CNT_PID
       FROM (SELECT PERSONID,
                    'TEST_PERSONS_A' TBL
                    FROM TEST_PERSONS_A
             UNION ALL
             SELECT PERSONID,
                    'TEST_PERSONS_B' TBL
                    FROM TEST_PERSONS_B) X
       WHERE TBL = UPPER(:TEST_TBL)
)
SELECT *
       FROM TEST_COUNT_ID;

Another option would be cross joining both aggregating queries. Use a CASE to only output the value of the target table.

WITH TEST_COUNT_ID
AS
(
SELECT CASE UPPER(:TEST_TBL)
         WHEN 'TEST_PERSONS_A' THEN
           A.CNT_PID
         WHEN 'TEST_PERSONS_B' THEN
           B.CNT_PID
       END CNT_PID
       FROM (SELECT COUNT(PERSONID) CNT_PID
                    FROM TEST_PERSONS_A) A
            CROSS JOIN (SELECT COUNT(PERSONID) CNT_PID
                        FROM TEST_PERSONS_B) B
)
SELECT *
       FROM TEST_COUNT_ID;

Or you could use use a CASE to create an ordering value putting the row from the target table first. Use ROWNUM to select only the first row.

WITH TEST_COUNT_ID
AS
(
SELECT CNT_PID
       FROM (SELECT CNT_PID
                    FROM (SELECT COUNT(PERSONID) CNT_PID,
                          CASE UPPER(:TEST_TBL)
                            WHEN 'TEST_PERSONS_A' THEN
                             0
                           ELSE
                             1
                         END O
                         FROM TEST_PERSONS_A
                  UNION ALL
                  SELECT COUNT(PERSONID) CNT_PID,
                         CASE UPPER(:TEST_TBL)
                           WHEN 'TEST_PERSONS_B' THEN
                             0
                           ELSE
                             1
                         END O
                         FROM TEST_PERSONS_B) X
                  ORDER BY O) Y
       WHERE ROWNUM = 1
)
SELECT *
       FROM TEST_COUNT_ID;

From a performance perspective however you'd be best off to build the respective query in your application only selecting from the actual target table.

Seriously? You came up with the first solution on your own, 40 minutes after it was posted already? (It seems you didn't quite understand it though - why do you need the outermost `SELECT`?) — , Jul 19 '18 at 00:22
@mathguy: Yes I did. Sometimes the notification, that an answer was added, doesn't work and I had it open for a while. So I didn't even see your answer before I posted mine. No need to get angry, I don't need to copy from you. But I kept my answer as it significantly adds something, I assume. And I wrote it that way, so that all stuff happens within the CTE and only a `SELECT * FROM CTE` is needed, as I assumed that's how the OP wants it. Your right though, if that requirement is dropped, some levels of `SELECT` could be spared. I never claimed anything else. — sticky bit, Jul 19 '18 at 00:32

Oracle SQL: in 'WITH' statement, how to avoid empty result row in conditional aggregate function select from different table

2 Answers2