Re: Complex Query

From: Pól <Pó>
Date: Thu, 26 Apr 2012 05:24:29 +0100
Message-ID: <4f98ce09$>

On 30/03/12 03:35, ExecMan wrote:
> On Mar 29, 9:08 pm, Peter Nilsson<> 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?

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

You should have a table

   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
CREATE TABLE Contributor

   Contributor_ID -- sequence
   Name, Address, Country, Phone, Mobile, Email... &c )


   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, sign up (it's free) and ask your design questions there.

HTH, Paul.... Received on Wed Apr 25 2012 - 23:24:29 CDT

Original text of this message