4 Replies Latest reply on Feb 25, 2010 8:58 PM by Barbara Boehmer

    Need query for nested table

    475868
      Hi,

      I am new to this nested table topic, I am facing problem in querying nested table. Actually i had created a table which consists of details regarding Applications and the platforms. As an application can run on more than one platform i had created a nested table for platform.

      CREATE OR REPLACE TYPE TY_PLATFORM AS OBJECT
      ( VC_PLATFORM VARCHAR2(64) );
      /

      CREATE OR REPLACE TYPE NT_TY_PLATFORM AS TABLE OF TY_PLATFORM;
      /

      I am using this nested table in the main table application:

      CREATE TABLE T_APPLICATION
      ( N_APPLICATION_ID NUMBER(32,6),
      VC_APPLICATION_NAME VARCHAR2(160),
      CL_NT_PLATFORM NT_TY_PLATFORM )
      NESTED TABLE CL_NT_PLATFORM STORE AS CL_NT_PLATFORM_TAB
      /

      The table contains the data like this(Just for example):

      N_APPLICATION_ID VC_APPLICATION_NAME PLATFORM
      ------------------------------------------------------------------------------------------------
      1 ABC Unix, Windows
      2 DEF Unix, MVS
      3 GHI Unix, Windows
      4 JKL Unix, Windows, MVS

      I have created the scripts like this...

      INSERT INTO T_APPLICATION(N_APPLICATION_ID, VC_APPLICATION_NAME) VALUES(1,'ABC');
      Like that for all applications...

      UPDATE T_APPLICATION SET CL_NT_PLATFORM=NT_TY_PLATFORM(TY_PLATFORM('Unix'),TY_PLATFORM('Windows')) WHERE N_ACCESS_NUMBER=1;
      Like that for all applications.....

      After creation i had queried
      SQL> select t.n_application_id from t_application t,table(cl_nt_platform)p
      2 where p.vc_platform='Unix' and p.vc_platform='Windows' and p.vc_platform!='MVS';

      no rows selected

      Actually i want the application list which run on unix and windows platform but not MVS

      It want the result like this:
      T.N_APPLICATION_ID
      ---------------------------------------------------
      1
      2

      but it is returning no rows selected.......

      Please help me......

      Thanks in advance.....
        • 1. Re: Need query for nested table
          Barbara Boehmer
          scott@ORA92> CREATE OR REPLACE TYPE TY_PLATFORM AS OBJECT
            2    (VC_PLATFORM VARCHAR2(64));
            3  /
          
          Type created.
          
          scott@ORA92> CREATE OR REPLACE TYPE NT_TY_PLATFORM AS TABLE OF TY_PLATFORM;
            2  /
          
          Type created.
          
          scott@ORA92> CREATE TABLE T_APPLICATION
            2    (N_APPLICATION_ID    NUMBER(32,6),
            3       VC_APPLICATION_NAME VARCHAR2(160),
            4       CL_NT_PLATFORM         NT_TY_PLATFORM )
            5    NESTED TABLE CL_NT_PLATFORM STORE AS CL_NT_PLATFORM_TAB
            6  /
          
          Table created.
          
          scott@ORA92> INSERT INTO T_APPLICATION (N_APPLICATION_ID, VC_APPLICATION_NAME)
            2  VALUES (1, 'ABC')
            3  /
          
          1 row created.
          
          scott@ORA92> UPDATE T_APPLICATION
            2  SET    CL_NT_PLATFORM =
            3           NT_TY_PLATFORM (TY_PLATFORM ('Unix'),
            4                     TY_PLATFORM ('Windows'))
            5  WHERE  n_application_id = 1
            6  /
          
          1 row updated.
          
          scott@ORA92> INSERT ALL
            2  INTO t_application VALUES (2, 'DEF', nt_ty_platform (ty_platform ('Unix'),
            3                                       ty_platform ('MVS')))
            4  INTO t_application VALUES (3, 'GHI', nt_ty_platform (ty_platform ('Unix'),
            5                                       ty_platform ('Windows')))
            6  INTO t_application VALUES (4, 'JKL', nt_ty_platform (ty_platform ('Unix'),
            7                                       ty_platform ('Windows'),
            8                                       ty_platform ('MVS')))
            9  SELECT * FROM DUAL
           10  /
          
          3 rows created.
          
          scott@ORA92> COLUMN vc_application_name FORMAT A19
          scott@ORA92> COLUMN platform FORMAT A30
          scott@ORA92> SELECT t.n_application_id,
            2           t.vc_application_name,
            3           p.vc_platform AS platform
            4  FROM   t_application t,
            5           TABLE (cl_nt_platform) p
            6  /
          
          N_APPLICATION_ID VC_APPLICATION_NAME PLATFORM
          ---------------- ------------------- ------------------------------
                         1 ABC                 Unix
                         1 ABC                 Windows
                         2 DEF                 Unix
                         2 DEF                 MVS
                         3 GHI                 Unix
                         3 GHI                 Windows
                         4 JKL                 Unix
                         4 JKL                 Windows
                         4 JKL                 MVS
          
          9 rows selected.
          
          scott@ORA92> SELECT t2.n_application_id
            2  FROM   t_application t2
            3  WHERE  EXISTS
            4           (SELECT t.n_application_id,
            5                t.vc_application_name,
            6                p.vc_platform AS platform
            7            FROM   t_application t,
            8                TABLE (cl_nt_platform) p
            9            WHERE  t.n_application_id = t2.n_application_id
           10            AND    p.vc_platform = 'Unix')
           11  AND    EXISTS
           12           (SELECT t.n_application_id,
           13                t.vc_application_name,
           14                p.vc_platform AS platform
           15            FROM   t_application t,
           16                TABLE (cl_nt_platform) p
           17            WHERE  t.n_application_id = t2.n_application_id
           18            AND    p.vc_platform = 'Windows')
           19  AND    NOT EXISTS
           20           (SELECT t.n_application_id,
           21                t.vc_application_name,
           22                p.vc_platform AS platform
           23            FROM   t_application t,
           24                TABLE (cl_nt_platform) p
           25            WHERE  t.n_application_id = t2.n_application_id
           26            AND    p.vc_platform = 'MVS')
           27  /
          
          N_APPLICATION_ID
          ----------------
                         1
                         3
          
          scott@ORA92> 
          • 2. Re: Need query for nested table
            475868
            Thank you very much.
            • 3. Re: Need query for nested table
              756205
              I have tried the following : Not getting any result with the select statement. Getting 'no rows selected.'. Pl help. Thank you.

              CREATE TYPE address_t AS OBJECT (
              street VARCHAR2(30),
              city VARCHAR2(20),
              state CHAR(2),
              zip CHAR(5) );
              /
              CREATE TYPE address_tab IS TABLE OF address_t;
              /
              CREATE TABLE customers (
              custid NUMBER,
              address address_tab )
              NESTED TABLE address STORE AS customer_addresses;

              INSERT INTO customers VALUES (1,
              address_tab(
              address_t('101 First', 'Redwood Shores', 'CA', '94065'),
              address_t('123 Maple', 'Mill Valley', 'CA', '90952')
              ) );


              select a.custid, b.street as t1 from customers a, table(address) b
              • 4. Re: Need query for nested table
                Barbara Boehmer
                It works for me as demonstrated below. Please post a copy and paste of an acutal run, as I have done below, to demonstrate that it does not work for you.
                SCOTT@orcl_11g> CREATE TYPE address_t AS OBJECT (
                  2  street VARCHAR2(30),
                  3  city VARCHAR2(20),
                  4  state CHAR(2),
                  5  zip CHAR(5) );
                  6  /
                
                Type created.
                
                SCOTT@orcl_11g> CREATE TYPE address_tab IS TABLE OF address_t;
                  2  /
                
                Type created.
                
                SCOTT@orcl_11g> CREATE TABLE customers (
                  2  custid NUMBER,
                  3  address address_tab )
                  4  NESTED TABLE address STORE AS customer_addresses;
                
                Table created.
                
                SCOTT@orcl_11g> 
                SCOTT@orcl_11g> INSERT INTO customers VALUES (1,
                  2  address_tab(
                  3  address_t('101 First', 'Redwood Shores', 'CA', '94065'),
                  4  address_t('123 Maple', 'Mill Valley', 'CA', '90952')
                  5  ) );
                
                1 row created.
                
                SCOTT@orcl_11g> 
                SCOTT@orcl_11g> select a.custid, b.street as t1 from customers a, table(address) b
                  2  /
                
                    CUSTID T1
                ---------- ------------------------------
                         1 101 First
                         1 123 Maple
                
                SCOTT@orcl_11g>