Re: seems impossible without PL/SQL

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 Tue Mar 19 2002 - 19:43:15 CST