Complex Query

[ExecMan]

Hi,

I'm trying to write a single query for this and I'm kind of stuck. I
have a table with 3 columns:

ARTICLE_ID
AUTHOR_ID
EDITOR_ID

The AUTHOR_ID and EDITOR_ID need not be the same. I would like to get
a count from that table, providing a user ID, of how many articles
where they are the Author, and how many where they are the Editor.

It is easy with a single ID, but the application can pass in a
delimited string of ID's: '12,457,976'. That is where I am stuck.
If I query and use an OR ( author_id IN (...) OR editor_id IN
(...) ), I will get records where the AUTHOR_ID may be in my list,
but the EDITOR_ID is not.

I'm hoping this makes sense.

[joel garry]

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/mar/28/man-arrested-charged-bilking-san-diego-family/

[ExecMan]

> @home.com is bogus.http://www.utsandiego.com/news/2012/mar/28/man-arrested-charged-bilki...


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.....

[Peter Nilsson]

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

[ExecMan]

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?

[ExecMan]

On Mar 29, 9:08 pm, Peter Nilsson <ai...@acay.com.au> wrote:

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

[ExecMan]

On Mar 29, 9:08 pm, Peter Nilsson <ai...@acay.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)

[ExecMan]

On Mar 29, 9:08 pm, Peter Nilsson <ai...@acay.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)

[Otto A d a m]

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

[Peter Schneider]

If your application provides a delimited, comma separated list of IDs, which
you can syntactically use directly as an IN-list in a query, do so in a
dynamic SQL statement. Something like that, not nice, but you get the idea:

CREATE TABLE "PS"."ARTICLES"
( "ARTICLE_ID" NUMBER(12,0),
"ARTICLE_TITLE" VARCHAR2(100 BYTE),
"AUTHOR_ID" NUMBER(12,0),
"EDITOR_ID" NUMBER(12,0)
);

ALTER TABLE "PS"."ARTICLES" ADD CONSTRAINT "ARTICLES_UK1" UNIQUE
("ARTICLE_TITLE");
ALTER TABLE "PS"."ARTICLES" ADD CONSTRAINT "ARTICLES_PK" PRIMARY KEY
("ARTICLE_ID");

ALTER TABLE "PS"."ARTICLES" MODIFY ("EDITOR_ID" NOT NULL ENABLE);
ALTER TABLE "PS"."ARTICLES" MODIFY ("AUTHOR_ID" NOT NULL ENABLE);
ALTER TABLE "PS"."ARTICLES" MODIFY ("ARTICLE_TITLE" NOT NULL ENABLE);
ALTER TABLE "PS"."ARTICLES" MODIFY ("ARTICLE_ID" NOT NULL ENABLE);

INSERT INTO "PS"."ARTICLES" (ARTICLE_ID, ARTICLE_TITLE, AUTHOR_ID, EDITOR_ID)
VALUES ('1', 'Erster Artikel', '1', '2')
INSERT INTO "PS"."ARTICLES" (ARTICLE_ID, ARTICLE_TITLE, AUTHOR_ID, EDITOR_ID)
VALUES ('3', 'Dritter Artikel', '2', '3')
INSERT INTO "PS"."ARTICLES" (ARTICLE_ID, ARTICLE_TITLE, AUTHOR_ID, EDITOR_ID)
VALUES ('2', 'Zweiter Artikel', '1', '1')
INSERT INTO "PS"."ARTICLES" (ARTICLE_ID, ARTICLE_TITLE, AUTHOR_ID, EDITOR_ID)
VALUES ('4', 'Vierter Artikel', '3', '2')
INSERT INTO "PS"."ARTICLES" (ARTICLE_ID, ARTICLE_TITLE, AUTHOR_ID, EDITOR_ID)
VALUES ('5', 'Fünfter Artikel', '4', '4')
INSERT INTO "PS"."ARTICLES" (ARTICLE_ID, ARTICLE_TITLE, AUTHOR_ID, EDITOR_ID)
VALUES ('6', 'Sechster Artikel', '5', '1')

COMMIT;

DECLARE
p_author_count NUMBER;
p_editor_count NUMBER;
p_user_list VARCHAR2(255) := '3, 5';

BEGIN
DBMS_OUTPUT.ENABLE(1000000);

EXECUTE IMMEDIATE
'BEGIN
SELECT (SELECT COUNT(*)
FROM articles a
WHERE a.author_id IN (' || p_user_list || ')
) AS author_count,
(SELECT COUNT(*)
FROM articles a
WHERE a.editor_id IN (' || p_user_list || ')
) AS editor_count
INTO :1, :2
FROM dual;
END;'
USING OUT p_author_count, OUT p_editor_count;

DBMS_OUTPUT.PUT_LINE('author count: ' || p_author_count);
DBMS_OUTPUT.PUT_LINE('editor count: ' || p_editor_count);

END;

author count: 2
editor count: 1

So user_ids frin the list '3, 5' occur 2 times as author and 1 time as editor.

Instead of EXECUTE IMMEDIATE, you could also use a weak ref cursor (OPEN
cur_hndl FOR 'SELECT ...' || p_user_list etc. etc.)

Regards
Peter

--
The only way to keep your health is to eat what you don't want, drink what
you don't like, and do what you'd rather not. -- Mark Twain

[Pól]

Your approach is complete and utter madness and a total breach
of the 1NF - no repeating elements.

You should have a table
CREATE TABLE Book
(
Book_ID int -- seq not ISBN - not all books published
ISBN CHAR(13)
Title VARCHAR2(500)
Publisher VARCHAR2(100)
Format BOOLEAN // HB/SB
Pages int
&c
)
CREATE TABLE Contributor
(
Contributor_ID -- sequence
Name, Address, Country, Phone, Mobile, Email... &c
)

CREATE TABLE BOOK_Author
(
Book_ID FK references Book.Book_ID
Contributor_ID FK references Contributor
Contributor_Function -- Writer, Editor, ProofReader...
)

So, any book can have 1 or many contributors and any contributor can
have 0, 1 or many books.

This approach is far more scaleable - Google Publisher database schema,
and I'm sure that nobody will have proposed doing what you want to do.

I can't believe that this hasn't been pointed out to you.

Go to forums.oracle.com, sign up (it's free) and ask your design
questions there.

HTH,


Paul....