CODEWITHSUNDEEP
sqloracle-databasecase
punky
punky

Reputation: 153

Oracle SQL - implement logic based on CASE expression result

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?

Upvotes: 0

Views: 50

Answers (1)

MT0
MT0

Reputation: 167972

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

Upvotes: 1

Related Questions