Home » SQL & PL/SQL » SQL & PL/SQL » SQL Select statement - Returning Dummy row if no results (merged) (Oracle 9i (9.2.0.8))
SQL Select statement - Returning Dummy row if no results (merged) [message #429096] |
Mon, 02 November 2009 02:20  |
hjoshi
Messages: 7 Registered: November 2009 Location: Sydney
|
Junior Member |
|
|
Hi,
Would thoroughly appreciate help with this one..
I currently have a bit of logic (with in a report) which is a large SQL statement. (I'm providing a 'dummy' example below)
SELECT a, b, c, d
FROM tablea, tableb, tablec
WHERE tablea.a = tableb.a
AND tablea.b = tablea.b
...
...
AND tablea.c = tablec.c
There are instances where this particular query does not return
any results. What I've been trying to do, is in those instances, to return a placeholder row ...
This is what I tried ..
SELECT a, b, c, d
FROM tablea, tableb, tablec
WHERE tablea.a = tableb.a
AND tablea.b = tablea.b
...
...
AND tablea.c = tablec.c
UNION
SELECT '-', 0, 0, 0 FROM DUAL WHERE NOT EXISTS
(
SELECT a, b, c, d
FROM tablea, tableb, tablec
WHERE tablea.a = tableb.a
AND tablea.b = tablea.b
...
...
AND tablea.c = tablec.c
)
The trouble with using this logic I found is that .. while it works, it's executing the logic twice ! .. and has performance impacts for particularly complex queries.
Basically, at this point, I would appreciate any help with putting together SELECT logic that does the same as what I've shown above ... i.e. return dummy row when nothing's being returned.
Constraints I have are ...
- It has to be in the form of a single SELECT statement
- PLSQL cannot be used within this reporting engine
- Using Oracle 9i
Would appreciate if anyone has insight into this. Thanks
Thanks,
Himanshu
|
|
|
|
|
Re: SQL Select statement - Returning Dummy row if no results (merged) [message #429109 is a reply to message #429105] |
Mon, 02 November 2009 03:14   |
hjoshi
Messages: 7 Registered: November 2009 Location: Sydney
|
Junior Member |
|
|
Thanks for your assistance
Manish - This was simply an option that I explored. The intention was to check within a single SELECT statement whether the same logic was returning results or not ... and if not, then to output a placeholder row.
Michel - Thanks for the suggestion. The only trouble is that it isn't a single SELECT statement ..which is what I need. The query is actually embedded as part of a struts XML template (defined as a 'datasource') .. and relies on being in the form a single SELECT statement. I don't have the flexibility to perform logic here oustide of the SELECT unfortunately.
|
|
|
|
Re: SQL Select statement - Returning Dummy row if no results (merged) [message #429128 is a reply to message #429110] |
Mon, 02 November 2009 04:30   |
hjoshi
Messages: 7 Registered: November 2009 Location: Sydney
|
Junior Member |
|
|
Michel,
The SQL I wrote (using the UNION) does return "'-', 0, 0, 0"
when no rows are returned.
The frontend layer doesn't have this functionality... hence having to do it via SQL query.
Unfortunately for complex queries.. using the UNION method wasn't very efficient since it was executing the same logic twice ! (once to display the data itself.. and once to check whether there are any results)
Basically, I'm asking if there's any other way or rewriting the SQL statement. (within a single SELECT statement and no PL/SQL logic)
If it's not possible then I'll have to look at alternates (none at this point~)
Cheers,
Himanshu
|
|
|
|
Re: SQL Select statement - Returning Dummy row if no results (merged) [message #429161 is a reply to message #429096] |
Mon, 02 November 2009 06:22  |
idris.ali
Messages: 34 Registered: June 2008 Location: Hyderabad
|
Member |
|
|
Hi,
Well you can have the query return the desired result without having the big(complex) query run twice by using some logic...
Ex
(Assuming your complex query is SELECT TNAME,TABTYPE,CLUSTERID, FROM tab WHERE ROWNUM < 1)
SELECT * FROM (
SELECT TNAME,TABTYPE,CLUSTERID,dummy,Count(*) over() cnt
FROM
(
SELECT TNAME,TABTYPE,CLUSTERID,'N' dummy FROM tab WHERE ROWNUM < 1
UNION ALL
SELECT '-','-',0,'Y' FROM dual
)
)
WHERE dummy = Decode(cnt,1,'Y','N')
But Again , As Michel suggested you should be handling this in your front end.. I am not into struts but i think there should be some way to do it.
Thanks,
Idris
|
|
|
Goto Forum:
Current Time: Sat Feb 15 10:50:40 CST 2025
|