Re: Complex Query
Date: Thu, 26 Apr 2012 05:24:29 +0100
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
Format BOOLEAN // HB/SB
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