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.