Hi,

I am using this query. It returns information on existing orders the
customer has, and ranks them based on the status.

We have 25 products defined in a separate table. Rather than having
to keep doing a COUNT to check for a given product, I am hoping to
somehow modify this query such that it returns a record for ALL
products. If the customer does not have that product, it will give
NULL values for the status column. right now I cannot find any way to
do that join, I know it is some outer join.

It may be a WITH query, which I always have trouble creating, but
maybe someone can help, I've been at this for a few days.

So, for a customer with 4 orders, this query returns 4 records. I'd
like it to return 25 (total orders in products table) and just a NULL
or 'N' for products the customer does not have.


SELECT email, customer_id, subproduct_id, description, status, rnum,
status_rank
FROM (SELECT email, customer_id, subproduct_id, status, rnum,
status_rank, description
FROM (SELECT email, c.customer_id, subproduct_id, status,
description,
CASE status
WHEN 'Active' THEN 1
WHEN 'Pending' THEN 2
WHEN 'Complete' THEN 3
WHEN 'Cancelled' THEN 4
END status_rank,
ROW_NUMBER() OVER (PARTITION BY subproduct_id
ORDER BY subproduct_id) rnum
FROM customer c, customer_account ca,
customer_address cd, customer_order_vw co
WHERE c.customer_id = ca.customer_id(+)
AND c.customer_id = cd.customer_id(+)
AND c.customer_id = co.customer_id(+)
AND c.customer_id = 312039939)
ORDER BY status_rank, rnum)
WHERE rnum = 1;

On Feb 15, 10:32*am, The Magnet <a... (AT) unsu (DOT) com> wrote:
How about some DDL and sample data to create these structures?

HTH -- Mark D Powell --

On Mar 29, 11:28*am, ExecMan <artme... (AT) yahoo (DOT) com> wrote:
I'm not exactly sure I understand the bit about the delimited string,
but can you union two queries, one against editor?

jg
--
@home.com is bogus.
http://www.utsandiego.com/news/2012/...-diego-family/

On Mar 29, 4:52*pm, joel garry <joel-ga... (AT) home (DOT) com> wrote:

I think I'm gonna be stuck with an object table and casting that. The
delimited string becomes an IN clause, rather than a single value.....

ExecMan <artme... (AT) yahoo (DOT) com> wrote:
The traditional way is a conditional sum...

with
the_table as
(
select 1 as article_id, 1 as author_id, 1 as editor_id from dual
union all
select 2 as article_id, 1 as author_id, 1 as editor_id from dual
union all
select 3 as article_id, 1 as author_id, 2 as editor_id from dual
union all
select 4 as article_id, 2 as author_id, 4 as editor_id from dual
union all
select 5 as article_id, 2 as author_id, 4 as editor_id from dual
union all
select 6 as article_id, 2 as author_id, 1 as editor_id from dual
union all
select 7 as article_id, 3 as author_id, 2 as editor_id from dual
union all
select 8 as article_id, 3 as author_id, 1 as editor_id from dual
)
,
the_people as
(
select 1 as person_id from dual union all
select 2 as person_id from dual union all
select 3 as person_id from dual union all
select 4 as person_id from dual
)
select p.person_id,
count(decode(p.person_id, t.author_id, t.article_id)) as
author_ct,
count(decode(p.person_id, t.editor_id, t.article_id)) as
editor_ct
from the_people p
left join the_table t on p.person_id in (t.author_id, t.editor_id)
where p.person_id in (1, 2, 3, 4)
group by p.person_id
order by p.person_id;

Or you can unpivot and repivot the 'Table':

with
the_table as
(
select 1 as article_id, 1 as author_id, 1 as editor_id from dual
union all
select 2 as article_id, 1 as author_id, 1 as editor_id from dual
union all
select 3 as article_id, 1 as author_id, 2 as editor_id from dual
union all
select 4 as article_id, 2 as author_id, 4 as editor_id from dual
union all
select 5 as article_id, 2 as author_id, 4 as editor_id from dual
union all
select 6 as article_id, 2 as author_id, 1 as editor_id from dual
union all
select 7 as article_id, 3 as author_id, 2 as editor_id from dual
union all
select 8 as article_id, 3 as author_id, 1 as editor_id from dual
)
select person_id, author_ct, editor_ct
from (
select type_cd, person_id, article_id
from the_table unpivot
(person_id for
type_cd in (author_id as 'A', editor_id as 'E'))
) pivot (count(article_id) for
type_cd in ('A' as author_ct, 'E' as editor_ct))
where person_id in (1, 2, 3, 4)
order by person_id;

--
Peter

On Mar 29, 9:08*pm, Peter Nilsson <ai... (AT) acay (DOT) com.au> wrote:

I'm trying to understand the UNION parts. The procedure accepts a
comma delimited string of ID's. So, this dynamic query is put
together via PL/SQL code. Since I do not know how many ID's will be
passed, will I just have to create these UNION lines for each ID?

On Mar 29, 9:08*pm, Peter Nilsson <ai... (AT) acay (DOT) com.au> wrote:

Got to find the syntax error in the second statement.......but the
first one works.

On Mar 29, 9:08*pm, Peter Nilsson <ai... (AT) acay (DOT) com.au> wrote:

Peter,

This query almost gives the right results. But, the AUTHOR_CNT &
EDITOR_CNT are the same for each of the people:

PERSON_ID AUTHOR_CT EDITOR_CT
---------- ---------- ----------
85524202 3 3
631459560 15019 15019

That is not right. The columns should have different numbers.

with
the_table as
(
select 1 as article_id, 631459560 as author_id, 631459560 as
editor_id from articles where author_id = 631459560 or editor_id =
631459560
union all
select 1 as article_id, 85524202 as author_id, 85524202 as
editor_id from articles where author_id = 85524202 or editor_id =
85524202
) ,
the_people as
(
select 631459560 as person_id from dual union all
select 85524202 as person_id from dual
)
select p.person_id,
count(decode(p.person_id, t.author_id, t.article_id)) as
author_ct,
count(decode(p.person_id, t.editor_id, t.article_id)) as
editor_ct
from the_people p
left join the_table t on p.person_id in (t.author_id, t.editor_id)
where p.person_id in (631459560,85524202)
group by p.person_id
order by p.person_id;

On Mar 29, 9:08*pm, Peter Nilsson <ai... (AT) acay (DOT) com.au> wrote:
Peter,

This is almost correct, but the columns are showing the wrong
numbers. The columns should have different numbers:

PERSON_ID AUTHOR_CT EDITOR_CT
---------- ---------- ----------
85524202 3 3
631459560 15019 15019


with
the_table as
(
select 1 as article_id, 631459560 as author_id, 631459560 as
editor_id from articles where author_id = 631459560 or editor_id =
631459560
union all
select 1 as article_id, 85524202 as author_id, 85524202 as
editor_id from articles where author_id = 85524202 or editor_id =
85524202
) ,
the_people as
(
select 631459560 as person_id from dual union all
select 85524202 as person_id from dual
)
select p.person_id,
count(decode(p.person_id, t.author_id, t.article_id)) as
author_ct,
count(decode(p.person_id, t.editor_id, t.article_id)) as
editor_ct
from the_people p
left join the_table t on p.person_id in (t.author_id, t.editor_id)
where p.person_id in (631459560,85524202)
group by p.person_id
order by p.person_id;

ExecMan schrieb:

Totally untested, because I'm to lazy to generate a test-table:

Table name: articles
user_id = 12345



SELECT COUNT(*) AS TOTAL,
SUM(x.AUTHOR_COUNT) AS AUTHOR_COUNT,
SUM(x.EDITOR_COUNT) AS EDITOR_COUNT
FROM
(SELECT a.ARTICLE_ID,
CASE
WHEN a.AUTHOR_ID = 12345 THEN 1
ELSE 0
END AS AUTHOR_COUNT,
CASE
WHEN a.EDITOR_ID = 12345 THEN 1
ELSE 0
END AS EDITOR_COUNT
FROM ARTICLES a
WHERE (a.AUTHOR_ID = 12345 OR a.EDITOR_ID = 12345)
) x

Please try...

mfg
otto