Oracle SQL - implement logic based on CASE expression result

Question

I have following query which returns me of percentage of rows with at least one NULL in any of columns:

SELECT
(SUM(CASE WHEN tablea.test IS NULL OR tableb.test IS NULL THEN 1 ELSE NULL END)/7000)*100) "at least one NULL (%)"
FROM tablea
FULL OUTER JOIN tableb
ON
tablea.test = tableb.test
WHERE ROWNUM < 7000;

Query works fine to me and I am getting valid result.

But I need to do further action in tablec, based on percentage calculated in my SELECT statement. If percentage of rows with NULL is below 30% I need to insert "YES" string inside table "tablec" column "resultcol", how can I implement such a logic?

Is it for example possible to store SELECT statement result in some temporary variable which will be used in another SQL query?

use a WITH statement or just take the select above and make into an inline view then reference from your 2nd select. There are no "variables" in SQL, unless you use pl/sql. — OldProgrammer
– OldProgrammer, Commented Jan 21, 2022 at 15:02
Is it possible to use WITH together with UPDATE table? As I said in below post, I need to update value in existing table row — punky
– punky, Commented Jan 24, 2022 at 11:09

MT0 · Accepted Answer · 2022-01-24 11:14:22Z

You can use:

INSERT INTO tablec (resultcol)
SELECT 'YES'
FROM   tablea
       FULL OUTER JOIN tableb
       ON tablea.test = tableb.test
WHERE  ROWNUM < 7000
HAVING COUNT(CASE WHEN tablea.test IS NULL OR tableb.test IS NULL THEN 1 END) 
        < COUNT(*) * 0.3;

db<>fiddle here

Update:

If you want to insert a row if it does not exist or change the row if it does exist then:

MERGE INTO tablec dst
USING (
  SELECT CASE
         WHEN EXISTS (
                SELECT 1
                FROM   tablea
                       FULL OUTER JOIN tableb
                       ON tablea.test = tableb.test
                WHERE  ROWNUM < 7000
                HAVING COUNT(CASE WHEN tablea.test IS NULL OR tableb.test IS NULL THEN 1 END) 
                         < COUNT(*) * 0.3
              )
         THEN 'YES'
         ELSE 'NO'
         END AS resultcol
  FROM   DUAL
) src
ON (1 = 1)
WHEN NOT MATCHED THEN
  INSERT (resultcol) VALUES (src.resultcol)
WHEN MATCHED THEN
  UPDATE SET resultcol = src.resultcol;

Or, to just update it:

UPDATE tablec
SET resultcol = CASE
                WHEN EXISTS (
                       SELECT 1
                       FROM   tablea
                              FULL OUTER JOIN tableb
                              ON tablea.test = tableb.test
                       WHERE  ROWNUM < 7000
                       HAVING COUNT(
                                CASE
                                WHEN tablea.test IS NULL
                                OR   tableb.test IS NULL
                                THEN 1
                                END
                              ) < COUNT(*) * 0.3
                     )
                THEN 'YES'
                ELSE 'NO'
                END;

db<>fiddle here

I wrongly interpreted problem, I already have table and column and I need to change column value from NO to YES. And as far I can see HAVING doesnt work with UPDATE?

Collectives™ on Stack Overflow

Oracle SQL - implement logic based on CASE expression result

1 Answer 1

Update:

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Update:

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related