Link to home
Start Free TrialLog in
Avatar of sakthikumar
sakthikumar

asked on

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.
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Sean Stuber
Sean Stuber

please provide sample data and expected output
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
>>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?