Re: Help on a query of a query

luke.pickett_at_gmail.com skrev:
> I am working on generating report from a database that has been
> migrated to Oracle from 4D and they used packed fields in 4D to store
> the data instead of recursively calling the same table multiple
> times(upto 25). The issue I have run into is if I break out the
> packed fields I get a set of columns and I need to search through all
> of these columns with the results of another query that tells me if a
> piece of data is active or not. This table is used for all actions
> within the system and I have no way of knowing if these linked codes
> contain inactive actions. This system has been in-use for several
> years and no-one has ever looked to cleaned up the inactive codes in
> scripts, they have inactivated codes without regard causing weird
> errors that the users learned to ignore.

I had problems understanding the description. I think it would help if you gave DDL and sample data for the tables. I do believe I understand the problem now, although I am not sure whether the "recursively calling the same table multiple times (up to 25)" means 25 levels of recursions, or just 25 references one level deep each.

Anyway, your second query seems like it would have problems if one code can be a substring of another. For instance, if the first query returned "12", the second would match "12", "123" etc.

I think a better approach would be something like

select idcode
from items
where (
substr(
 script,
 instr(script,'\',1,9)+1,
 instr(script,'\',1,10)-instr(script,'\',1,9)-1 ) = '******'
or
substr(

   script,
   instr(script,'\',1,32)+1,
   instr(script,'\',1,33)-instr(script,'\',1,32)-1 ) = '******'
or
...
) and active=1 and type=2

This could then be simplified using IN:

select idcode
from items
where '******' in
(substr(
 script,
 instr(script,'\',1,9)+1,
 instr(script,'\',1,10)-instr(script,'\',1,9)-1 ),
substr(

   script,
   instr(script,'\',1,32)+1,
   instr(script,'\',1,33)-instr(script,'\',1,32)-1 ),
...
) and active=1 and type=2

Finally, you could use a self-join to run the query for all the inactive codes at once:

select i1.idcode
from items i1, items i2
where i2.idcode in
(substr(
 i1.script,
 instr(i1.script,'\',1,9)+1,
 instr(i1.script,'\',1,10)-instr(i1.script,'\',1,9)-1 ),
substr(

   script,
   instr(i1.script,'\',1,32)+1,
   instr(i1.script,'\',1,33)-instr(i1.script,'\',1,32)-1 ),
...
) and i2.active = 0 and i2.type = 1
and i1.active=1 and i1.type=2

Note: This has not been tested. It may take a very long time to run, you might be better off normalizing first (since you should aim to do this anyway). Note that if a script contains multiple inactive codes, it will show up multiple times.

Yours,
Nis

> As it stands I have 2 queries:
>
> select idcode
> from items
> where active=0 and type = 1
>
> And
>
> select idcode
> from items
> where
> instr(
> substr(
> script,
> instr(script,'\',1,9)+1,
> instr(script,'\',1,10)-instr(script,'\',1,9)-1
> )||','||
> substr(
> script,
> instr(script,'\',1,32)+1,
> instr(script,'\',1,33)-instr(script,'\',1,32)-1
> )||','||
> substr(
> script,
> instr(script,'\',1,55)+1,
> instr(script,'\',1,56)-instr(script,'\',1,55)-1
> )||','||
> substr(
> script,
> instr(script,'\',1,78)+1,
> instr(script,'\',1,79)-instr(script,'\',1,78)-1
> ),'******')>0 and active=1 and type=2
>
> ****** I can replace with the code from the first query manually but
> there are hundreds of these inactive codes.
Received on Tue May 22 2007 - 15:26:24 CDT