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 Go to next message
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 [message #429098 is a reply to message #429096] Mon, 02 November 2009 02:31 Go to previous messageGo to next message
manu_jariwala
Messages: 20
Registered: August 2005
Location: Surat
Junior Member

Hi,
Can u clear that why you are using union for same 2 query?

And 2nd thing, do u check your join statement? Is it ok for retrieve a record?

R'gards,
Manish Jariwala
Re: SQL Select statement - Returning Dummy row if no results (merged) [message #429105 is a reply to message #429096] Mon, 02 November 2009 02:57 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
def flag=0
col flag new_value flag
select <your select>, 1 flag 
from <table> <etc...>
/
select 'No result' from dual where &flag=0;

Regards
Michel
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 Go to previous messageGo to next message
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 #429110 is a reply to message #429109] Mon, 02 November 2009 03:18 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Why your front end is unable to display/return "'-', 0, 0, 0" when the query returns nothing?

Regards
Michel

[Updated on: Mon, 02 November 2009 03:18]

Report message to a moderator

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 Go to previous messageGo to next message
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 #429133 is a reply to message #429128] Mon, 02 November 2009 04:36 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
I don't understand how a front end is able to fetch and display from a query from first to last record and is not able to display another thing when there is no return?

If it really can't then you are stuck to do the work twice.

Regards
Michel
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 Go to previous message
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
Previous Topic: An interesting problem: same query doesn't running on different database.
Next Topic: Tbale with more than 2 foreign keys referencing to tables
Goto Forum:
  


Current Time: Sat Feb 15 10:50:40 CST 2025