 | |
Multiple selects in one
comp.databases.oracle.server comp.databases.oracle.server
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Multiple selects in one -
03-10-2010
, 03:49 PM
#2
| |||
| |||
|
|
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? |
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/...17329730362010
jg
--
@home.com is bogus.
http://guyharrison.squarespace.com/b...mware-esx.html
#3
| |||
| |||
|
|
On Mar 10, 1:49*pm, The Magnet <a... (AT) unsu (DOT) 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.co..._QUESTION_ID:1... jg -- @home.com is bogus.http://guyharrison.squarespace.com/b...y-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!
#4
| |||
| |||
|
|
On 11 Mar, 14:08, The Magnet <a... (AT) unsu (DOT) 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
#5
| |||
| |||
|
|
Sorry, version 10g R2. As for DDL / DML, not sure why that helps. * snip |
It helps because it will allow people to simply run the supplied
scripts to create the table and populate them without having to create
the test-case all by themselves.
HTH
-g
#6
| |||
| |||
|
|
On 11 Mar, 14:08, The Magnet <a... (AT) unsu (DOT) 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 |
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, status non_ut_status,
LEAD(subscription_id, 1, NULL) OVER (PARTITION BY
customer_id ORDER BY customer_id) ut_subscr_id,
LEAD(produst_id, 1, NULL) OVER (PARTITION BY customer_id
ORDER BY customer_id) ut_product_id,
LEAD(status, 1, 0) OVER (PARTITION BY customer_id ORDER
BY customer_id) ut_status,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY
customer_id) rnum
FROM (SELECT customer_id, subscription_id, produst_id, status
FROM subscriptions
WHERE customer_id = 565511633
AND produst_id <> 204
UNION
SELECT customer_id, subscription_id, produst_id, status
FROM subscriptions
WHERE customer_id = 565511633
AND produst_id = 204))
WHERE rnum = 1;
#7
| |||
| |||
|
|
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 |
that turns rows into columns. Oracle 11g introduced the PIVOT
operator, but it can be done with pre-11g versions as well by using a
GROUP BY with a bit cumbersome aggregate function expression.
Something like this should give you a starting point:
with subscriptions as (
select
12345 as CUSTOMER_ID
, 203 as PRODUCT_ID
, 6767 as SUBSCRIPTION_ID
, 2 as STATUS
from
dual
union all
select
12345 as CUSTOMER_ID
, 204 as PRODUCT_ID
, 6768 as SUBSCRIPTION_ID
, 1 as STATUS
from
dual
)
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
, max(decode(product_id, 204, null, subscription_id)) as
non_ut_subscr_id
, max(decode(product_id, 204, null, product_id)) as
non_ut_product_id
, max(decode(product_id, 204, null, status)) as
non_ut_status
, max(decode(product_id, 204, subscription_id, null)) as
ut_subscr_id
, max(decode(product_id, 204, product_id, null)) as
ut_product_id
, max(decode(product_id, 204, status, null)) as
ut_status
FROM
subscriptions
WHERE
customer_id = 12345
GROUP BY
customer_id
)
;
If you're interested what 11g offers, you can lookup the new PIVOT
syntax in this excellent article by OakTable fellow Adrian Billington:
http://www.oracle-developer.net/display.php?id=506
Regards,
Randolf
Oracle related stuff blog:
http://oracle-randolf.blogspot.com/
Co-author of the "OakTable Expert Oracle Practices" book:
http://www.apress.com/book/view/1430226684
http://www.amazon.com/Expert-Oracle-.../dp/1430226684
#8
| |||
| |||
|
|
On Mar 11, 3:51*pm, The Magnet <a... (AT) unsu (DOT) com> wrote: 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 I think what you're looking for is commonly called a PIVOT operation that turns rows into columns. Oracle 11g introduced the PIVOT operator, but it can be done with pre-11g versions as well by using a GROUP BY with a bit cumbersome aggregate function expression. Something like this should give you a starting point: with subscriptions as ( select * * * * *12345 as CUSTOMER_ID * * * *, 203 * as PRODUCT_ID * * * *, 6767 *as SUBSCRIPTION_ID * * * *, 2 * * as STATUS from * * * * *dual union all select * * * * *12345 as CUSTOMER_ID * * * *, 204 * as PRODUCT_ID * * * *, 6768 *as SUBSCRIPTION_ID * * * *, 1 * * as STATUS from * * * * *dual ) 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 * * * * * * * , max(decode(product_id, 204, null, subscription_id)) as non_ut_subscr_id * * * * * * * , max(decode(product_id, 204, null, product_id)) * * *as non_ut_product_id * * * * * * * , max(decode(product_id, 204, null, status)) * * * * *as non_ut_status * * * * * * * , max(decode(product_id, 204, subscription_id, null)) as ut_subscr_id * * * * * * * , max(decode(product_id, 204, product_id, null)) * * *as ut_product_id * * * * * * * , max(decode(product_id, 204, status, null)) * * * * *as ut_status * * * * FROM * * * * * * * * subscriptions * * * * WHERE * * * * * * * * customer_id = 12345 * * * * GROUP BY * * * * * * * * customer_id * * * * ) ; If you're interested what 11g offers, you can lookup the new PIVOT syntax in this excellent article by OakTable fellow Adrian Billington:http://www.oracle-developer.net/display.php?id=506 Regards, Randolf Oracle related stuff blog:http://oracle-randolf.blogspot.com/ Co-author of the "OakTable Expert Oracle Practices" book:http://www.apress.com/book/view/1430...Administration... |
Interesting query. It works. I never really understood the 'WITH'
queries. Reading on it did not really help either.
Thanks.
#9
| |||
| |||
|
|
On Mar 12, 6:19*am, Randolf Geist <mah... (AT) web (DOT) de> wrote: On Mar 11, 3:51*pm, The Magnet <a... (AT) unsu (DOT) com> wrote: 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 I think what you're looking for is commonly called a PIVOT operation that turns rows into columns. Oracle 11g introduced the PIVOT operator, but it can be done with pre-11g versions as well by using a GROUP BY with a bit cumbersome aggregate function expression. Something like this should give you a starting point: with subscriptions as ( select * * * * *12345 as CUSTOMER_ID * * * *, 203 * as PRODUCT_ID * * * *, 6767 *as SUBSCRIPTION_ID * * * *, 2 * * as STATUS from * * * * *dual union all select * * * * *12345 as CUSTOMER_ID * * * *, 204 * as PRODUCT_ID * * * *, 6768 *as SUBSCRIPTION_ID * * * *, 1 * * as STATUS from * * * * *dual ) 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 * * * * * * * , max(decode(product_id, 204, null, subscription_id)) as non_ut_subscr_id * * * * * * * , max(decode(product_id, 204, null, product_id)) * * *as non_ut_product_id * * * * * * * , max(decode(product_id, 204, null, status)) * * * * *as non_ut_status * * * * * * * , max(decode(product_id, 204, subscription_id, null)) as ut_subscr_id * * * * * * * , max(decode(product_id, 204, product_id, null)) * * *as ut_product_id * * * * * * * , max(decode(product_id, 204, status, null)) * * * * *as ut_status * * * * FROM * * * * * * * * subscriptions * * * * WHERE * * * * * * * * customer_id = 12345 * * * * GROUP BY * * * * * * * * customer_id * * * * ) ; If you're interested what 11g offers, you can lookup the new PIVOT syntax in this excellent article by OakTable fellow Adrian Billington:http://www.oracle-developer.net/display.php?id=506 Regards, Randolf Oracle related stuff blog:http://oracle-randolf.blogspot.com/ Co-author of the "OakTable Expert Oracle Practices" book:http://www.apress.com/book/view/1430...azon.com/Exper...... Interesting query. *It works. *I never really understood the 'WITH' queries. *Reading on it did not really help either. Thanks. |
is just a way of manufacturing whatever data you want, whether
literally as Randolf did, or from other table data, as in the article
he pointed at.
Not sure if the UNION part of your "beast" was exactly what you meant
to post, couldn't see the difference between =204 and <>204, maybe I'm
just cross-eyed.
jg
--
@home.com is bogus.
Certainly there are lying liars who lie about certs:
http://www.signonsandiego.com/news/2...ential-claims/
#10
| |||
| |||
|
|
On Mar 12, 8:29*am, The Magnet <a... (AT) unsu (DOT) com> wrote: On Mar 12, 6:19*am, Randolf Geist <mah... (AT) web (DOT) de> wrote: On Mar 11, 3:51*pm, The Magnet <a... (AT) unsu (DOT) com> wrote: 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 I think what you're looking for is commonly called a PIVOT operation that turns rows into columns. Oracle 11g introduced the PIVOT operator, but it can be done with pre-11g versions as well by using a GROUP BY with a bit cumbersome aggregate function expression. Something like this should give you a starting point: with subscriptions as ( select * * * * *12345 as CUSTOMER_ID * * * *, 203 * as PRODUCT_ID * * * *, 6767 *as SUBSCRIPTION_ID * * * *, 2 * * as STATUS from * * * * *dual union all select * * * * *12345 as CUSTOMER_ID * * * *, 204 * as PRODUCT_ID * * * *, 6768 *as SUBSCRIPTION_ID * * * *, 1 * * as STATUS from * * * * *dual ) 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 * * * * * * * , max(decode(product_id, 204, null, subscription_id)) as non_ut_subscr_id * * * * * * * , max(decode(product_id, 204, null, product_id)) * * *as non_ut_product_id * * * * * * * , max(decode(product_id, 204, null, status)) * * * * *as non_ut_status * * * * * * * , max(decode(product_id, 204, subscription_id, null)) as ut_subscr_id * * * * * * * , max(decode(product_id, 204, product_id,null)) * * *as ut_product_id * * * * * * * , max(decode(product_id, 204, status, null)) * * * * *as ut_status * * * * FROM * * * * * * * * subscriptions * * * * WHERE * * * * * * * * customer_id = 12345 * * * * GROUP BY * * * * * * * * customer_id * * * * ) ; If you're interested what 11g offers, you can lookup the new PIVOT syntax in this excellent article by OakTable fellow Adrian Billington:http://www.oracle-developer.net/display.php?id=506 Regards, Randolf Oracle related stuff blog:http://oracle-randolf.blogspot.com/ Co-author of the "OakTable Expert Oracle Practices" book:http://www.apress.com/book/view/1430...azon.com/Exper...... Interesting query. *It works. *I never really understood the 'WITH' queries. *Reading on it did not really help either. Thanks. I'm not sure if you are saying you understand it now, but basically it is just a way of manufacturing whatever data you want, whether literally as Randolf did, or from other table data, as in the article he pointed at. Not sure if the UNION part of your "beast" was exactly what you meant to post, couldn't see the difference between =204 and <>204, maybe I'm just cross-eyed. jg -- @home.com is bogus. Certainly there are lying liars who lie about certs:http://www.signonsandiego.com/news/2...sight-of-crede... |
Just trying to get in one record whether or not they have any product
<> 204 and if they have a product = 204. I figure the UNION will give
me max 2 records, and then I can use LEAD to get the values from the
second record into the first record.
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.