how we can filter out a nested table? or how we can use where clause in nested table?

I have nested table like this

CREATE TABLE "MMS_STG1"."MMS_AUDIT"
   (      "AUDIT_ID" NUMBER,
      "TABLE_NAME" VARCHAR2(30),
      "DML_TYPE" VARCHAR2(30),
      "MODIFIED_USER" VARCHAR2(100),
      "TIMESTAMP" DATE,
      "CHANGE_HISTORY" "MMS_STG1"."TABLE_HISTORY_TAB" ,
       PRIMARY KEY ("AUDIT_ID")
   NESTED TABLE "CHANGE_HISTORY" STORE AS "HIST"

CREATE OR REPLACE TYPE "MMS_STG1"."TABLE_HISTORY_TAB" IS TABLE OF table_history;

CREATE OR REPLACE TYPE "MMS_STG1"."TABLE_HISTORY" AS OBJECT (
column_name varchar2(30),
new_value varchar2(4000),
old_value varchar2(4000)
);

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

I can select from MMS_AUDIT in plsql developer, which gives a column for collection.
 but how I can filter out the collection. Based on the conditions in collections, I wanted to retrieve the records in MMS_AUDIT.
sakthikumarAsked:
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.

slightwv (䄆 Netminder) Commented:
0

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
sdstuberCommented:
please provide sample data and expected output
0
SujithData ArchitectCommented:
See this example.I have used your objects as an exmaple.
You can use TABLE clause to un-nest the nested table.

SQL> insert into mms_audit
  2  values(
  3  100,
  4  'test1',
  5  TABLE_HISTORY_TAB(TABLE_HISTORY('col1', 'new1', 'old1'), TABLE_HISTORY('col2', 'new2', 'old2'))
  6  );

1 row created.

SQL>
SQL> insert into mms_audit
  2  values(
  3  200,
  4  'newtable',
  5  TABLE_HISTORY_TAB(TABLE_HISTORY('newcol', 'new1', 'old1'), TABLE_HISTORY('newcol2', 'new2', 'old2'))
  6  );

1 row created.

SQL>
SQL> select audit_id, table_name, column_name, new_value, old_value
  2  from mms_audit m, table(m.change_history);

  AUDIT_ID TABLE_NAME                     COLUMN_NAME                    NEW_VALUE  OLD_VALUE
---------- ------------------------------ ------------------------------ ---------- ----------
       100 test1                          col1                           new1       old1
       100 test1                          col2                           new2       old2
       200 newtable                       newcol                         new1       old1
       200 newtable                       newcol2                        new2       old2

SQL>
SQL> select audit_id, table_name, column_name, new_value, old_value
  2  from mms_audit m, table(m.change_history)
  3  where column_name = 'newcol1';

no rows selected

SQL> select audit_id, table_name, column_name, new_value, old_value
  2  from mms_audit m, table(m.change_history)
  3  where column_name = 'newcol';

  AUDIT_ID TABLE_NAME                     COLUMN_NAME                    NEW_VALUE  OLD_VALUE
---------- ------------------------------ ------------------------------ ---------- ----------
       200 newtable                       newcol                         new1       old1

Open in new window

0
slightwv (䄆 Netminder) Commented:
>>You can use TABLE clause to un-nest the nested table.

Aside from using the names in the original post, isn't that basically the same as the example in the link I posted?
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.