Re: Complex Query

From: Peter Schneider <pschneider1968_at_googlemail.com>
Date: Mon, 02 Apr 2012 23:51:18 +0200
Message-ID: <jld74g$ra7$1_at_online.de>

Am 29.03.2012 20:28, schrieb 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.

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

Received on Mon Apr 02 2012 - 16:51:18 CDT