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

--
Clouds®

On Wed, 30 Jun 2004 17:08:26 +0200, "Clouds®"
<DOCloudsNOT@hardwareSPAM-spot.com> wrote:

>I guess this is not possible with plain SQL....


It is definitely possible with plain sql.
Either set up a static view
create view abc as <your original select>

select * from abc where

or (usually performs better) set up an inline view

select * from
(<your original select>)
where etc.

Don't resort to 'temp' tables, in Oracle you only rarely need them.



--
Sybrand Bakker, Senior Oracle DBA

"Sybrand Bakker" <sybrandb@hccnet.nl> schreef in bericht
news:0cb6e0tg2f3g65a1ut0inpcdt877jld42s@4ax.com...
> On Wed, 30 Jun 2004 17:08:26 +0200, "Clouds®"
> <DOCloudsNOT@hardwareSPAM-spot.com> wrote:
>
> >I guess this is not possible with plain SQL....
>
>
> It is definitely possible with plain sql.
>
> select * from
> (<your original select>)
> where etc.

Thank's for your reaction, but I think you did not realise what I want, or I
don't get your solution...

I'd like to do something like this:

select * from table1
where table1.value (select operator from table2) (select value from table2)

So the problem is getting an operator from table2 and use it in the query on
table1.
Table2.operator is in the form of = , > , != etc...

--
Clouds®

On Thu, 1 Jul 2004, DOCloudsNOT@hardwareSPAM-spot.com wrote:
>
> "Sybrand Bakker" <sybrandb@hccnet.nl> schreef in bericht
> news:0cb6e0tg2f3g65a1ut0inpcdt877jld42s@4ax.com...
>> On Wed, 30 Jun 2004 17:08:26 +0200, "Clouds®"
>> <DOCloudsNOT@hardwareSPAM-spot.com> wrote:
>>
>> >I guess this is not possible with plain SQL....
>>
>>
>> It is definitely possible with plain sql.
>>
>> select * from
>> (<your original select>)
>> where etc.
>
> Thank's for your reaction, but I think you did not realise what
> I want, or I don't get your solution...
>
> I'd like to do something like this:
>
> select * from table1 where table1.value (select operator from
> table2) (select value from table2)
>
> So the problem is getting an operator from table2 and use it in
> the query on table1. Table2.operator is in the form of = , > ,
> != etc...

PLSQL and execute immediate.
--
Galen Boyer

"Clouds®" <DOCloudsNOT@hardwareSPAM-spot.com> wrote in message news:<40e2d76a$0$35145$e4fe514c@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

"David Fitzjarrell" <fitzjarrell@cox.net> schreef in bericht
news:9711ade0.0407010608.69ebd620@posting.google.c om...
> "Clouds®" <DOCloudsNOT@hardwareSPAM-spot.com> wrote in message
news:<40e2d76a$0$35145$e4fe514c@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?

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

> 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" <fitzjarrell@cox.net> schreef in bericht
news:9711ade0.0407010608.69ebd620@posting.google.c om...
> "Clouds®" <DOCloudsNOT@hardwareSPAM-spot.com> wrote in message
news:<40e2d76a$0$35145$e4fe514c@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

"Clouds®" <DOCloudsNOT@hardwareSPAM-spot.com> wrote in message news:<40e40d98$0$48933$e4fe514c@news.xs4all.nl>...
> I'd like to do something like this:
>
> select * from table1
> where table1.value (select operator from table2) (select value from table2)
>
> So the problem is getting an operator from table2 and use it in the query on
> table1.
> Table2.operator is in the form of = , > , != etc...

This is not possible in plain SQL (Dynamic SQL and procedural
languages don't count). SQL is presize: you just specify what you
want. Your problem statement, however, contains an ambiguity:
operators and values from table2 could be either be conjuncted, or
disjuncted (although, in earlier message you mentioned that you want
conjunction).

You can look into expression filter developers guide.

"David Fitzjarrell" <fitzjarrell@cox.net> schreef in bericht
news:9711ade0.0407010817.2e4d64d1@posting.google.c om...
> 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®