Multiple selects in one
in [Oracle]
|
From: The Magnet on 10 Mar 2010 16:49 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?
From: joel garry on 10 Mar 2010 17:30 On Mar 10, 1:49 pm, The Magnet <a...(a)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 -- @home.com is bogus. http://guyharrison.squarespace.com/blog/2010/2/22/memory-management-for-oracle-databases-on-vmware-esx.html
From: The Magnet on 11 Mar 2010 09:08 On Mar 10, 4:30 pm, joel garry <joel-ga...(a)home.com> wrote: > On Mar 10, 1:49 pm, The Magnet <a...(a)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.htmlhttp://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1... > > jg > -- > @home.com is bogus.http://guyharrison.squarespace.com/blog/2010/2/22/memory-management-f... Yes, I was also looking at analytical functions. The query works fine if both conditions on both SELECT statements are positive, but if one is negative, the entire query fails. The actual query I've been trying to work with is: SELECT customer_id, non_ut_subscr_id, non_ut_product_id, non_ut_status, ut_subscr_id, ut_product_id, ut_status FROM (SELECT customer_id, subscription_id non_ut_subscr_id, produst_id non_ut_product_id, SUM(status) OVER (PARTITION BY customer_id) non_ut_status FROM subscriptions WHERE customer_id = p_customer_id AND produst_id <> 204), (SELECT customer_id, subscription_id ut_subscr_id, produst_id ut_product_id, SUM(status) OVER (PARTITION BY customer_id) ut_status FROM subscriptions WHERE customer_id = p_customer_id AND produst_id = 204); I want it all in one line. So, that is what I am trying to work with. I'm sure it can be done, just have to figure out the exact syntax. Thanks!
From: gazzag on 11 Mar 2010 09:21 On 11 Mar, 14:08, The Magnet <a...(a)unsu.com> wrote: > Yes, I was also looking at analytical functions. > > The query works fine if both conditions on both SELECT statements are > positive, but if one is negative, the entire query fails. > > The actual query I've been trying to work with is: > > SELECT customer_id, non_ut_subscr_id, non_ut_product_id, > non_ut_status, ut_subscr_id, ut_product_id, ut_status > FROM (SELECT customer_id, subscription_id non_ut_subscr_id, > produst_id non_ut_product_id, > SUM(status) OVER (PARTITION BY customer_id) > non_ut_status > FROM subscriptions > WHERE customer_id = p_customer_id > AND produst_id <> 204), > (SELECT customer_id, subscription_id ut_subscr_id, produst_id > ut_product_id, > SUM(status) OVER (PARTITION BY customer_id) ut_status > FROM subscriptions > WHERE customer_id = p_customer_id > AND produst_id = 204); > > I want it all in one line. So, that is what I am trying to work > with. I'm sure it can be done, just have to figure out the exact > syntax. > > Thanks! Why not do as Joel suggested and post some DDL to create the relevant objects and some DML to populate the tables with test data. I guarantee a far quicker response! Oracle version would be good too. HTH -g
From: The Magnet on 11 Mar 2010 09:51 On Mar 11, 8:21 am, gazzag <gar...(a)jamms.org> wrote: > On 11 Mar, 14:08, The Magnet <a...(a)unsu.com> wrote: > > > > > Yes, I was also looking at analytical functions. > > > The query works fine if both conditions on both SELECT statements are > > positive, but if one is negative, the entire query fails. > > > The actual query I've been trying to work with is: > > > SELECT customer_id, non_ut_subscr_id, non_ut_product_id, > > non_ut_status, ut_subscr_id, ut_product_id, ut_status > > FROM (SELECT customer_id, subscription_id non_ut_subscr_id, > > produst_id non_ut_product_id, > > SUM(status) OVER (PARTITION BY customer_id) > > non_ut_status > > FROM subscriptions > > WHERE customer_id = p_customer_id > > AND produst_id <> 204), > > (SELECT customer_id, subscription_id ut_subscr_id, produst_id > > ut_product_id, > > SUM(status) OVER (PARTITION BY customer_id) ut_status > > FROM subscriptions > > WHERE customer_id = p_customer_id > > AND produst_id = 204); > > > I want it all in one line. So, that is what I am trying to work > > with. I'm sure it can be done, just have to figure out the exact > > syntax. > > > Thanks! > > Why not do as Joel suggested and post some DDL to create the relevant > objects and some DML to populate the tables with test data. I > guarantee a far quicker response! Oracle version would be good too. > > HTH > -g Sorry, version 10g R2. As for DDL / DML, not sure why that helps. Just a table with a couple of records: SUBSCRIPTIONS ------------- CUSTOMER_ID 12345 PRODUCT_ID 203 SUBSCRIPTION_ID 6767 STATUS 2 CUSTOMER_ID 12345 PRODUCT_ID 204 SUBSCRIPTION_ID 6768 STATUS 1 Now, to get those into 1 display record: CUSTOMER_ID PRODUCT_ID SUBSCRIPTION_ID STATUS PRODUCT_ID SUBSCRIPTION_ID STATUS 12345 203 6767 2 204 6768 1 And if one side is not true: CUSTOMER_ID PRODUCT_ID SUBSCRIPTION_ID STATUS PRODUCT_ID SUBSCRIPTION_ID STATUS 12345 204 6768 1
|
Next
|
Last
Pages: 1 2 Prev: ORA-00470: LGWR process terminated with error Next: New Procedurs With Version# In Name |