Re: Help on a query of a query

On May 21, 4:11 pm, luke.pick..._at_gmail.com wrote:
> 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

wow, that's ugly. nested pointers in C a almost easier to understand.

>
> ****** I can replace with the code from the first query manually but
> there are hundreds of these inactive codes.

So write the first query to generate the second query. For example

SELECT 'select idcode, script'

    || ' from items where idcode= ''' || idcode || ''' ; '   FROM items
  WHERE active=0 AND type = 1

Spool the output to a file and run the file. You just have a larger query to build.

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

Okay, lets, make sure we have the facts.

You took a datamodel straight from 4d to oracle, unchanged. NOT a good idea. You had a great chance to clean up the data during that conversion but you missed it.

My generic advice would be to clean up the data. Don't try removing all of these at once, but start going for the "low hanging fruit". every data scrub just will make your entire system better. Hopefully you can create your query on the fly. (Meta queries can be very powerful.)

  HTH,
  ed Received on Tue May 22 2007 - 12:43:38 CDT