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

From: David Fitzjarrell <fitzjarrell_at_cox.net>
Date: 1 Jul 2004 07:08:52 -0700
Message-ID: <9711ade0.0407010608.69ebd620@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
>
> I guess this is not possible with plain SQL.....can it be done with
> SQL*Plus?
> If so, please give a hint since I'm not an experienced
> SQL*Plus-programmer....
>
> TIA!!!
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;

Of course this is rather silly, since you need to propagate this 'logic' for every value set you need from conditions. You COULD write some PL/SQL to generate this through a cursor, but it would be a bit longer (this is a simple case to illustrate the point; a more thorough approach would be to test the compare_value contents and enclose in '' any string values):

declare

     cursor get_cond is
     select content, operator, compare_value
     from conditions;
     rowctr number:=1;
     sqltext varchar2(4000):='select * from a_table where ';
begin
     for c in get_cond loop
          if rowctr = 1 then
          sqltext := sqltext || c.content ||' '||c.operator||'
'||c.compare_value;
          else
          sqltext := sqltext || ' and ' || || c.content ||'
'||c.operator||' '||c.compare_value;
          end if;
          rowctr := rowctr + 1;
     end loop;
     execute immediate sqltext;

end;
/

I hope you see Sybrand was correct; plain old SQL would fill the bill.  The PL/SQL is a bit fancier way to do it, without having to write a select for EVERY value in the content field for which you want to create a WHERE clause.

David Fitzjarrell Received on Thu Jul 01 2004 - 09:08:52 CDT