Re: Challenging Query Problem!

Thank you all for your help.
I had to use dynamic SQL in Cold Fusion (my web development tool/language) in order to solve it.

Thank you.

Michael J. Ort <michael_ort_at_my-deja.com> wrote in message news:8cvj1v$u2i$1_at_nnrp1.deja.com...
> That query works for a finite number of assignments, but what I think
> he's asking for is a query that works when there are n assignments and
> n users. In other words, a query that returns a variable number of
> columns, which cannot be done with traditional SQL. It has to be
> created dynamically.
>
> I'm sure someone out there can figure out a means by which to use
> DBMS_SQL, but it my case I needed to pass back a REF CURSOR for the web
> application (developed in Delphi) to loop through (don't ask me about
> that side of it; I have no idea how they handled it, but they did).
> This can NOT be done with DBMS_SQL. That's when I found Native Dynamic
> SQL in the oracle documentation (new to version 8.1.5 I think). It
> will allow you to open a cursor dynamically and pass back a reference
> to it. Keep in mind that since the number of columns (and their
> aliases so you know what the column is) will change, the SGA will have
> to parse and store a version of each. I have found said overhead to be
> miniscule when compared to the alternative of writing, handling, and
> maintaining separate SQL statements.
>
> Eddie, I know you were probably hoping for something a little easier,
> but this is the best way I have found, but it may require you to learn
> more PL/SQL than it's worth to you as a Web Application Developer.
> Before you dive in though, make absolutely certain, within your web
> development language, you can handle/loop through a cursor reference
> (especially one where you won't know the number of columns, as well as
> their names, being returned until run-time)...
>
> Michael J. Ort
>
> P.S.
> I've also used native dynamic SQL to create what I call 'private views'
> (variables holding SQL declared in a package body that are concatenated
> into the from clauses of other dynamic SQL procedures in the package),
> but that's for another post. You get the picture though. The addition
> of REF CURSOR and native dynamic SQL open up some interesting
> possibilities...
>
> In article <8cuplt$rss$2_at_news7.svr.pol.co.uk>,
> "Gerrit-Jan Linker" <linker_at_oraxcel.com> wrote:
> > Base table:
> >
> > MyTable:
> > Name Assignment Mark
> > ==== ======== ====
> > name1 assignment1 10
> > name2 assignment1 9
> > name3 assignment1 9
> > name1 assignment2 12
> > name2 assignment2 15
> > name3 assignment2 11
> >
> > select name, assignment1, assignment2 from
> > ( select name, mark assignment1 from MyTable
> > where assignment = 'ASSIGNMENT1') a
> > ( select name, mark assignment2 from MyTable
> > where assignment = 'ASSIGNMENT2') b
> > ( select distinct name from MyTable) c
> > where a.name = c.name
> > and b.name = c.name
> >
> > Will output
> >
> > NAME Assignment1 Assignment2
> > name1 10 12
> > name2 9 15
> > name3 9 11
> >
> > Have a look at inline views in the Oracle documentation. Inline views
 are
> > the subqueries I've done in the from clause.
> >
> > Hope this helps,
> >
> > --
> > Gerrit-Jan Linker
> >
> > web: http://www.oraxcel.com
> > email: gjlinker_at_oraxcel.com
> >
> > Oraxcel: Linking Oracle to Excel
> > OraCodes: Explaining ORA- error codes
> > OraSQL: Explaining Oracle SQL syntax
> > OraDebug: PL/SQL debugger
> > OraWeb: Oracle access from IE
> >
> > Eddie <edawad_at_hotmail.com> wrote in message
> > news:8ctmg5$296$1_at_nnrp1.deja.com...
> > > Hi, I have a query which has the following output:
> > >
> > > name1 assignment1 10
> > > name2 assignment1 9
> > > name3 assignment1 9
> > > name1 assignment2 12
> > > name2 assignment2 15
> > > name3 assignment2 11
> > >
> > > What I want is the following:
> > >
> > > NAME Assignment1 Assignment2
> > > name1 10 12
> > > name2 9 15
> > > name3 9 11
> > > I have absolutely no idea how do this. Neither the number of
 students
> > > (name1, name2....) is fixed nor the number of assignments. Any help
 is
> > > greatly appreciated. Thanks.
> > >
> > > --
> > > Eddie
> > > Web Application Developer
> > >
> > >
> > > Sent via Deja.com http://www.deja.com/
> > > Before you buy.
> >
> >
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Wed Apr 12 2000 - 00:00:00 CDT