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?
Advertisement
Answer
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