Re: Reporting with dynamic sql

Deltones wrote:
> DA Morgan wrote:
> > Deltones wrote:
> > > G Quesnel wrote:
> > >> BTW / just a little side note - you are not using TEMP tables.
> >
> > > These tables are dropped at the end of the script.
> >
> > This is horrible. Please pull over to the side of the road and ask
> > for help. This is like watching two kids street racing and knowing
> > what is going to happen sooner or later. The suspense is killing me.
> > --
>
> That's exactly what I'm trying to do on this thread (the asking for
> advice, not the racing part ;)
>
> If I wanted to do the concept below, how would you go about it?
>
> variable mycur refcursor;
> variable yourcursor refcursor;
>
> begin
> open :mycur for
> 'select emp_name, emp_no from emp';
>
> open :yourcursor for
> 'select a.emp_name, b.salary from :mycursor a, salary b';
> end;
>
> As you can see, that's basically the kind of construct from the "create
> table" query you saw earlier, but I'm trying to figure out how to do it
> using Sybrand's advice on using refcursor instead. But the selecting
> from another refcursor, as seen in the "open :yourcursor" section
> doesn't work.
>
> Denis

Based on your example, this looks like a join if both cursors are really looking at tables or views. So why wouldn't you write one query like this?

'select a.emp_name, b.salary from emp a,salary b where ...(likely joined on emp_name)

???
You seem to be making things too complicated for yourself. A join query is your friend.

  Ed Received on Thu Aug 17 2006 - 08:32:47 CDT