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?
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
0
 
sdstuberCommented:
please provide sample data and expected output
0
 
SujithConnect With a Mentor Data 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
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.

All Courses

From novice to tech pro — start learning today.