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

"David Fitzjarrell" <fitzjarrell_at_cox.net> schreef in bericht news:9711ade0.0407010608.69ebd620_at_posting.google.com...
> "CloudsŪ" <DOCloudsNOT_at_hardwareSPAM-spot.com> wrote in message
news:<40e2d76a$0$35145$e4fe514c_at_news.xs4all.nl>...
> > Hi all,
> >
> > Just ran into a problem: I have to create a query based on the result of
a
> > 'parent'-query.
> >
> > For example: I have a table 'conditions' with the columns 'content',
> > 'operator' and 'compare_value'
> >
> > result for select * from conditions:
> >
> > content operator compare_value
> > -------------------------------------
> > value1 = ABC
> > value2 > 15
> >
> > The next step is creating a query that looks like:
> >
> > select * from a_table
> > where
> > value1 = ABC
> > and
> > value2 > 15
>
> select 'select * from a_table where '||a.content || ' '
> ||a.operator||' '||a.compare_value||' and '||b.content||'
> '||b.operator||' '||b.compare_value||';'
> from (select content, operator, compare_value from conditions where
> content = 'value1') a, (select content, operator, compare_value from
> conditions where content = 'value2') b;

> Ok, I tried this one, but it did as I expected: it's result is a string
> "select * from a_table where ......"
> The string is ok, but how can the query enclosed in this string be executed?

select 'select * from a_table where '||a.content || ' ' ||a.operator||' '||a.compare_value||' and '||b.content||' '||b.operator||' '||b.compare_value||';' from (select content, operator, compare_value from conditions where content = 'value1') a, (select content, operator, compare_value from conditions where content = 'value2') b

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.

> I'd like to think I'm a good SQL-programmer (that's why I can't stand the
> fact I don't understand your solutions :-)
> Once wrote a query of 18K, with 62 select-statements...I know the concept of

> select * from
> (select * from x) a,
> (select * from y) b
> where a.bla = (select bla from c)

> etc. but I fail to see how it can be used here

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

> 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.

> - 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?
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.

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

> Argl!!! Can't see it (yet)....
> Please have mercy and try to explain this again???

> Thanks for your help so far...

> --
> CloudsŪ

David Fitzjarrell Received on Thu Jul 01 2004 - 11:17:29 CDT