Re: Creating query based on results of a query...

"David Fitzjarrell" <fitzjarrell_at_cox.net> schreef in bericht news:9711ade0.0407010817.2e4d64d1_at_posting.google.com...
> spool myquery.sql
> /
> spool off
> @myquery
>
> I was presuming you were familiar with spooling output to a file,
> since you do consider yourself a good SQL programnmer.

It seems I have to adjust my opinion on me being a good SQL-programmer ;-) Plain SQL is no problem, but anything beyond that is rarely needed here, hence I am not familiar with it.
Besides that, spooling to a file is not an option I can (want to) use in this case.

> >You COULD write some PL/SQL to generate this through a cursor, but it

> > Well I could try that, but prefer not to for several reasons:
> > - I am not familiar with it
>
> Ummm, I GAVE you the basic code, which WILL work in your situation.
> That should put to rest the 'I am not familiar with it' argument.

True...

> > - Whatever I create will possibly be used in our ERP-system as a
> > 'quick-report', and has to be in the form of a plain SQL-query-
>
> What prevents PL/SQL from being executed in an ERP-system quick
> report?

The quick report has to be available to other users without a hassle. To achieve that, it's best to have the quick-report run within the executables of the ERP-system.
Quick reports in our system can be either plain SQL or Crystal Reports.

Of course PL/SQL can do the trick, but it would only be possible in a 3rd-party DBA tool, and 'normal' users don't have access to that...

> Who decided it HAS to be in the form of a plain SQL query? A plain
> SQL Query won't do what you want without spooling the output somewhere
> and executing the script you've generated on the fly.

The designers of the ERP-system limited the options...

> > I hope you see Sybrand was correct; plain old SQL would fill the bill.

I do now :-)
Too bad this cannot be solved the way preferred. I will switch to PL/SQL.

Thanks for your patience and help!

--
CloudsŪ