Re: Multiple selects in one
From: joel garry <joel-garry_at_home.com>
Date: Wed, 10 Mar 2010 14:30:23 -0800 (PST)
Message-ID: <ef800507-93b6-4fec-bd83-463947c789f2_at_w9g2000prb.googlegroups.com>
On Mar 10, 1:49 pm, The Magnet <a..._at_unsu.com> wrote:
> I'm trying to write a query that will combine multiple rows from the
> same table into one row. That can be done with each column being a
> SELECT statement.
>
> However, in my case each column has different criteria and I'm running
> into a problem where if any of the criteria from any of the queries is
> false, the entire query returns nothing.
>
> SELECT customer_id, customer_name, new_order_id, old_order_id.........
> FROM (SELECT customer_id, customer_name, new_order_id
> FROM......
> WHERE........),
> (SELECT old_order_id
> FROM......
> WHERE........)
>
> Basically I'm applying separate criteria to each column. How can I
> allow any of the column queries to be false and just return NULL or
> whatever instead of the entire query failing?
Date: Wed, 10 Mar 2010 14:30:23 -0800 (PST)
Message-ID: <ef800507-93b6-4fec-bd83-463947c789f2_at_w9g2000prb.googlegroups.com>
On Mar 10, 1:49 pm, The Magnet <a..._at_unsu.com> wrote:
> I'm trying to write a query that will combine multiple rows from the
> same table into one row. That can be done with each column being a
> SELECT statement.
>
> However, in my case each column has different criteria and I'm running
> into a problem where if any of the criteria from any of the queries is
> false, the entire query returns nothing.
>
> SELECT customer_id, customer_name, new_order_id, old_order_id.........
> FROM (SELECT customer_id, customer_name, new_order_id
> FROM......
> WHERE........),
> (SELECT old_order_id
> FROM......
> WHERE........)
>
> Basically I'm applying separate criteria to each column. How can I
> allow any of the column queries to be false and just return NULL or
> whatever instead of the entire query failing?
It would be easier to work out if you supplied some create statements/ test data, but maybe FULL OUTER JOIN as a self-join will get you there. See FOJ examples in docs. There's probably a nested table, associative array or similar PL solution, too, and maybe an analytic over a UNION ALL way. http://boneist-oracle.livejournal.com/5996.html http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:17329730362010
jg
-- _at_home.com is bogus. http://guyharrison.squarespace.com/blog/2010/2/22/memory-management-for-oracle-databases-on-vmware-esx.htmlReceived on Wed Mar 10 2010 - 16:30:23 CST