Re: seems impossible without PL/SQL

I am curious as to what this decode syntax you mention looks like, and how
it can be applied to the specific problem I posted. Also, there is no way of doing this in standard SQL, obviously. This reduces portability as every
database system seems to have its own version of PL/SQL.

Thanks for the feedback,

Neil

stinky <stinky_at_stinkytoes.org> wrote in message news:<3C97E932.30DA50BF_at_stinkytoes.org>...
> dynamic sql isn't necessary(and yes, you're right). knowledge of decode and/or case expressions and
> some dictionary views (tabs_columns) can do the trick.
>
>
> Ryan Gaffuri wrote:
>
> > I thought native dynamic sql could only be done inside of pl/sql?
> >
> > You initial a varchar variable to a string then do execute immediate on the varchar?
> >
> > Or am I wrong?
> >
> > damorgan <damorgan_at_exesolutions.com> wrote in message news:<3C975FED.9A30E219_at_exesolutions.com>...
> > > I just love people that come to conclusions before asking their question. You
> > > state "cannot be achieved" and then wax poetic about something that (assuming
> > > I understand what you are trying to do) already exists in Oracle. It is called
> > > DECODE. Mix it with a little native dynamic SQL and what you want can be
> > > easily accomplished.
> > >
> > > Daniel Morgan
> > >
> > >
> > >
> > > Neil Zanella wrote:
> > >
> > > > Hello,
> > > >
> > > > It is quite unfortunate that the following cannot be achieved
> > > > with standard SQL. PL/SQL or some other procedural language
> > > > is needed to achieve the desired effect.
> > > > Suppose there are three tables:
> > > >
> > > > X(A)
> > > > Y(A,B)
> > > > Z(B)
> > > >
> > > > where A is the primary key of X, B is the primary key of Z, and
> > > > (A,B) is the primary key of Y with Y.A referencing X.A and Y.B
> > > > referencing Z.B.
> > > >
> > > > It would be nice to be able to dynamically create the following table:
> > > >
> > > > T(A, B1, ..., Bn) where
> > > >
> > > > a) A is the primary key of T,
> > > >
> > > > b) B1, ..., Bn are the n entries of table Z used as column names for T,
> > > >
> > > > c) for each A in X there is exactly one A as the first column of T,
> > > >
> > > > d) for each A, each Bi is 1 if (A,Bi) appears in B, otherwise (A,Bi) is 0.
> > > >
> > > > I would be interested in knowing how the generation of such a table
> > > > could be automated using Oracle as well as other (R)DBMSs.
> > > >
> > > > Thanks!
> > > >
> > > > Neil
Received on Thu Mar 21 2002 - 01:35:18 CST