Home » SQL & PL/SQL » SQL & PL/SQL » Coalesce multiple columns (merged)
Coalesce multiple columns (merged) [message #392956] Thu, 19 March 2009 16:09 Go to next message
psk1
Messages: 30
Registered: August 2008
Location: Seattle, WA
Member
Say I have the following piece of SQL:

SELECT a1.pid FROM
    (SELECT * FROM table
     WHERE c = 5) a1,
    (SELECT * FROM table
     WHERE c = 6) a2,
    (SELECT * FROM table
     WHERE c = 7) a3;


Now, this query will only return results when column c has rows with values 5,6 and 7 in it. That is, if any of the sub-queries return 0 rows, the entire query will return 0 rows even if there is a row in table where c = 5.

My question is, is there a way to modify this query so that if there is a row where c=5, it will return results regardless of whether c=6 or c=7 exist?

And as a disclaimer, I know this is a ridiculous looking query. You might be tempted to ask why I do the other sub-queries when I'm only selecting from the first one. The reason is that this is only a small part of a much larger query where I may select the other results as well. This fragment just illustrates the point succinctly.
Re: Select from subqueries [message #392958 is a reply to message #392956] Thu, 19 March 2009 16:40 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Just specify outer joins where needed(+). Also be careful to include joins between the tables to avoid cartesean products...

SELECT a1.x FROM
    (SELECT object_name x FROM user_objects where object_type='TABLE') a1,
    (SELECT table_name x FROM user_tables where 1 > 0) a2,  -- no rows select here
    (SELECT table_name x FROM cat where table_type='TABLE') a3
where a1.x = a2.x (+)
and a1.x = a3.x (+);
Re: Select from subqueries [message #393047 is a reply to message #392958] Fri, 20 March 2009 02:28 Go to previous messageGo to next message
xpact83
Messages: 225
Registered: October 2008
Location: philippines
Senior Member
why dont you use coalesce to know the first sub query that will not return null... i think you can also use decode
Coalesce multiple columns [message #393192 is a reply to message #392956] Fri, 20 March 2009 12:26 Go to previous messageGo to next message
psk1
Messages: 30
Registered: August 2008
Location: Seattle, WA
Member
Hi,

The normal way to do a coalesce might be like this:

SELECT 
  COALESCE(
   (SELECT a FROM table WHERE b=1),
   (SELECT a FROM table WHERE b=2)) a,
  COALESCE(
   (SELECT b FROM table WHERE b=1),
   (SELECT b FROM table WHERE b=2)) b,
  COALESCE(
   (SELECT c FROM table WHERE b=1),
   (SELECT c FROM table WHERE b=2)) c,
FROM dual;


Is there a simple way of achieving the same functionality for multiple columns instead of doing coalesce on each of the columns. In other words, I'm looking to do the following:

SELECT COALESCE(
   (SELECT a,b,c FROM table WHERE b=1),
   (SELECT a,b,c FROM table WHERE b=2))
FROM dual;


Re: Coalesce multiple columns [message #393197 is a reply to message #393192] Fri, 20 March 2009 13:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You don't need to start a new topic to continue the same question.

Regards
Michel
Re: Coalesce multiple columns [message #393198 is a reply to message #393197] Fri, 20 March 2009 13:16 Go to previous message
psk1
Messages: 30
Registered: August 2008
Location: Seattle, WA
Member
Fair enough.

I considered appending it to the previous thread, but figured I might get scolded for going off topic.

I just figured that this question, while spawned from the previous one, could be answered independently. That is, nothing in this question depended on information from the previous one.

In any case, thanks for merging them.

P.S.: If you look at my history thus far (since August of last year), you may notice that all of my question actually pertain to one single query Smile
Previous Topic: Declaring Variable in Basic PL/SQL...
Next Topic: Problem retrieving substring from a CLOB variable
Goto Forum:
  


Current Time: Sat Dec 03 07:47:08 CST 2016

Total time taken to generate the page: 0.10425 seconds