Help on a query of a query

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.

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.

I need a way to check if a code that has been inactivated is still in an active script. This works code by code but I need a way to make sure that all the current scripts are using valid codes.

Thanks in advance Received on Mon May 21 2007 - 15:11:29 CDT