Nested Oracle Sub Queries HELP!!!!!

I have a select statement below with a sub-query that seems to work fine:    

 strSQL := 'select * from '||clienttransloop.table_name||' where '||clienttransloop.table_name||' .LCOUNTING in (select link from COUNTING where link = '||clienttransloop.table_name||' .LCOUNTING and to_date(lastupdate) < to_date(sysdate) -90 and CLOSED not like ''0000000000000000000'')';

My problem is that I need to do another subquery on the subquery, my attempt is below:

strSQL := 'select * from '||clienttransloop.table_name||' where '||clienttransloop.table_name||' .LCOUNTING in (select link from COUNTING where link in (select link from Client where link = Counting.LCLIENT and NAME Not Like ''%ING%'')) and Link = '||clienttransloop.table_name||' .LCOUNTING and to_date(lastupdate) < to_date(sysdate) -90 and CLOSED not like ''0000000000000000000'')';

Essentially for the first subquery I need to only return records that link to the CLIENT table, which in-turn filter out ING records.  I have tried to write a second subquery to achieve this, but cannot get it to work.  For context reference below, is the complete set of commands:

Declare

Cursor clienttrans
Is
select table_name from user_tables
  where table_name = 'COUNTINGSEP'
  OR table_name = 'COUNTINGCONTENT'
  OR table_name = 'DENOMCNT'
  OR table_name = 'COUNTINGTIMER'
  OR table_name = 'COUNTINGCOUNTERFEIT'
  OR table_name = 'REASONS';

strSQL varchar2(10000);

Begin

For clienttransloop in clienttrans Loop

      strSQL := 'select * from '||clienttransloop.table_name||' where '||clienttransloop.table_name||' .LCOUNTING in (select link from COUNTING where link in (select link from Client where link = Counting.LCLIENT and NAME Not Like ''%ING%'')) and Link = '||clienttransloop.table_name||' .LCOUNTING and to_date(lastupdate) < to_date(sysdate) -90 and CLOSED not like ''0000000000000000000'')';
      Execute Immediate strSQL;

End Loop;

commit;
End;

********THANKS IN ADVANCE!       
      

dlrsuppportAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

earth man2Commented:
something like this :-
DECLARE
   TYPE EmpCurTyp IS REF CURSOR;
   emp_cv   EmpCurTyp;
   emp_rec  emp%ROWTYPE;
   sql_stmt VARCHAR2(200);
   my_job   VARCHAR2(15) := 'CLERK';
BEGIN
   sql_stmt := 'SELECT * FROM emp WHERE job = :j';
   OPEN emp_cv FOR sql_stmt USING my_job;
   LOOP
      FETCH emp_cv INTO emp_rec;
      EXIT WHEN emp_cv%NOTFOUND;
      -- process record
   END LOOP;
   CLOSE emp_cv;
END;
DrSQLCommented:
dlrsuppport,
    I suspect that there's something in the line: " need to only return records that link to the CLIENT table, which in-turn filter out ING records" that means something to you, but not us.  The subquery you have added will restrict the  links returned from the COUNTING table to just those links in the client table that equal the current LCLIENT, but have a NAME that doesn't include the letters "ING".  Since you say the code "doesn't work" I assume this is either not what you want, or is based on incorrect assumptions.  Perhaps:

1) NAME, in Client, is actually not all upper case.  If so, it might not match the letters ING.  try changing the reference to name to be upper(name).

2) The link in the new subquery shuld be between the new subquery and the original.  We don't know what your table relationship is.

3) Any number of syntax errors are possible.

You should also let us know what result you DO get.

Finally, there's no need for the first select adn you can simplify the logic of your dynamic sql greatly.  I'd suggest that when you know your result set literally, you use a literal table instead of a select.  And, when you want to limit a result to the contents of a hierarchy like this, it's usually better to use a join:

Declare

  Type chartable is table of varchar2(30);

  table_names chartable := chartable('COUNTINGSEP','COUNTINGCONTENT',
                                     'DENOMCNT','COUNTINGTIMER',
                                     'COUNTINGCOUNTERFEIT','REASONS');

strSQL varchar2(2000); /* Should be sufficient and it's a length handled better by pl/sql */

Begin

  For this in table_names.first..table_names.last Loop
--
--   I find a string, with a replace makes it MUCH easier to see what I was doing.
--
     strSQL := replace(
         'select * from $table where $table.LCOUNTING in ' ||
              '(select link from COUNTING, Client ' ||
                'where COUNTING.link = Client.link and ' ||
--
--   I question the logic of this section, but this IS what you had, in essence.
--
                      'Client.link = Counting.LCLIENT and ' ||
                      'NAME Not Like ''%ING%'') and ' ||
              'Link = $table.LCOUNTING and ' ||
              'to_date(lastupdate) < to_date(sysdate) -90 and ' ||
              'CLOSED != ''0000000000000000000'')',
          '$table', table_names(this));
     Execute Immediate strSQL into <something>;
--
--    I assume your going to select into something and then DO something.
--    Whatever it is would go here.
  End Loop;

   /* You don't need to commit, if you're only selecting */
End;

  Check the part where you say "link in (select link from Client where link = Counting.LCLIENT" because that says that client.link has to be equal to BOTH COUNTING.link AND COUNTING.LCOUNTING, which seems unlikely.

Good luck!
DrSQL

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DrSQLCommented:
dlrsuppport,
    Thank you for closing this out, but I hope you'll post something about the final solution.  I also hope that, in future, you will let the experts know what is lacking in their answers before you offer a grade of B.  It didn't seem like a B effort, but only you know the value.  Continuing the dialog until you receive an A resolution can only improve the knowledge base at EE.

Good luck!
DrSQL
dlrsuppportAuthor Commented:
No worries still abit new to this site!! comments taken on board.

Here was the query that made it all work for me:

CREATE OR REPLACE PACKAGE BODY maestro_traceaudit AS
Declare

Cursor cur_delete
Is
select table_name from user_tables
  where table_name like '%TRACE%'
  and table_name not in 'SIIPSCINPARAMETERSCHECKTRACE'
  and table_name not in 'SIIPSCINPARAMETERSTRACE'
  and table_name not in 'VMSTRANSACTIONDETAILSTRACE'
  and table_name not in 'VMSTRANSACTIONHEADERTRACE';

strSQL varchar2(2000);

Begin

DELETE from AUDITKR
WHERE AUDITKR.linktoerror in (select link from errors where
   link = AUDITKR.linktoerror and to_date(LASTUPDATE) < to_date(sysdate - p_retention_time);
   
DELETE from ERRORS
WHERE to_date(LASTUPDATE) < to_date(sysdate - p_retention_time);

For loop in cur_delete Loop


       strSQL := 'Delete from '||loop.table_name||' where to_date(lastupdate) < to_date(sysdate - p_retention_time);
     Execute Immediate strSQL;



End Loop;

commit;
End;
DrSQLCommented:
dlrsupport,
   Ok, but are you sure that's the package we were discussing?

Good luck!
DrSQL
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.