Re: Complex Query

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

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.... Received on Wed Apr 25 2012 - 23:24:29 CDT