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.