Re: And again: 1NF may not be dead

"Kenneth Downs" <firstinit.lastname_at_lastnameplusfam.net> wrote in message news:e0lmlc.d77.ln_at_mercury.downsfam.net...
> Can it be simply that SQL lacks very basic constructs like SPLIT() and
> LIST()? (or pick your own favorite name)
>
> What if you had these two simple tables:
>
> CREATE TABLE parents (
> parent_id char(5) PRIMARY KEY,
> foo char(5))
>
> CREATE TABLE children (
> parent_id char(5),
> item_id char(5))
> FOREIGN KEY (parent_id) REFERENCES parents (parent_id)
>
> What I'd like is some kind of SQL command that splits arrays and lists
into
> multiple inserts, something like this:
>
> DECLARE ArrayExample char(5)[] = {'abc','xyz','123'};
> DECLARE ListExample varchar(50) = 'abc,xyz,123';
>
> INSERT INTO CHILDREN (parent_id,item_id)
> SELECT 'X',SPLIT(ArrayExample)
> INSERT INTO CHILDREN (parent_id,item_id)
> SELECT 'X',SPLIT(ListExample,",")
>
> which would result in 3 inserts. There could be sequenced version also.
> Oh, and a version that would not create duplicates (insert if not there,
> else update). I suppose while we're dreaming the array could be 2-d also,
> resulting in hits to multiple columns.
>
> Equally nice would be the opposite, something like this should return a
> comma-delimited list and/or array:
>
> DECLARE ArrayExample char(5)[]
> DECLARE ListExample String; -- Aha! Snuck that in
>
> SELECT ArrayExample = LIST(item_id) FROM children WHERE...
> SELECT ListExample = LIST(item_id,",") FROM children WHERE...
>
> FWIW, I've always thought that the RDM can pretty much handle anything,
but
> that the language is, believe it or not, too stuck in the RDM, and does
not
> easily translate into other models.
>
> Perhaps Mr. Celko can jump in and tell us if things like this are under
> consideration at all.
>
> --
> Kenneth Downs
> Use first initial plus last name at last name plus literal "fam.net" to
> email me

Oracle has been handling nested tables and arrays for several versions now. Received on Wed Oct 27 2004 - 15:43:26 CEST