Skip to content
Advertisement

Oracle UNION depending an IF conditon

I have to make a SQL query wraping all of theses statements:

First, I have a first SQL query like this:

SELECT COUNT(*) as NbOfEntries from table1 where colName = "some condition"

Then, I have another SQL statement like this:

SELECT 
  t2.col1 as col1, 
  t2.col2 as col2, 
  t3.col3 as col3 
FROM table2 t2 
  INNER JOIN table3 t3 
  ON t2.colName = t3.colName 
WHERE t2.colName = "some condition"

Finally, based on the first SQL statement, I have to add a row of fake data on the result of the second statement if NbOfEntries > 0. I have to add an UNION to the second SQL query like this:

SELECT 
  "Dumb string 1" as col1,
  "Dumb string 2" as col2, 
  "Dumb string 3" as col3
FROM DUAL -- or something like that, I don't know how it works

I thought about using UNION but, I don’t know how to use UNION with a condition. In addition, I do not know how to generate fake data with SELECT in Oracle, I have already looked at how DUAL works but I have not really been able to move forward.

Thank you.

EDIT: Sample data

If the first statement returns 0, I have these rows:

col1    col2  col3
A       AB    3
A       AC    7
A       AD    2

If the first statement returns > 0, I ihave to add one fake row to the result like this:

col1    col2  col3
A       AB    3
A       AC    7
A       AD    2
X       XX    0 // << Add this last row

Advertisement

Answer

You can use EXISTS like this:

SELECT 
  t2.col1 as col1, 
  t2.col2 as col2, 
  t3.col3 as col3 
FROM table2 t2 
  INNER JOIN table3 t3 
  ON t2.colName = t3.colName 
WHERE t2.colName = "some condition"
UNION ALL
SELECT 
  "Dumb string 1" as col1,
  "Dumb string 2" as col2, 
  "Dumb string 3" as col3
FROM DUAL
WHERE EXISTS (
  SELECT 1 from table1 
  where colName = "some condition"
)

There is no need to count the rows of table1.
EXISTS will return TRUE if there is 1 row that meets the condition.

User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement