cancel
Showing results for 
Search instead for 
Did you mean: 

MULTISET

SOLVED
Highlighted
Adventurer

MULTISET

Hi,

I am trying to convert from Oracle to Edb 9.6. Is there a way around to do CAST and MULTISET and SELECT.

Example:

SELECT CAST ( MULTISET ( SELECT "column" (B.COLUMN_NAME, B.DATA_TYPE) FROM all_tab_columns b, all_tables c ,all_tables a WHERE b.TABLE_NAME = a.TABLE_NAME AND b.table_name = c.TABLE_NAME AND B.OWNER = C.OWNER AND c.OWNER = USER)AS col_list_t) from dual

1 ACCEPTED SOLUTION

Accepted Solutions
EDB Team Member

Re: MULTISET

Below is the workaround. And it also shows how you should post the sample code:

Common code for Oracle and EPAS:
===================================
create or replace type t_col as object ( i number, n varchar2(30)); create or replace type t_nested_table as table of t_col; create or replace function return_table return t_nested_table as v_ret t_nested_table; begin v_ret := t_nested_table(); v_ret.extend; v_ret(v_ret.count) := t_col(1, 'one'); v_ret.extend; v_ret(v_ret.count) := t_col(2, 'two'); v_ret.extend; v_ret(v_ret.count) := t_col(3, 'three'); return v_ret; end return_table; select * from table(return_table); IN ORACLE:
=========== SQL> SELECT CAST(MULTISET(select * from table(return_table)) AS t_nested_table) FROM dual; CAST(MULTISET(SELECT*FROMTABLE(RETURN_TABLE))AST_NESTED_TABLE)(I, N) -------------------------------------------------------------------------------- T_NESTED_TABLE(T_COL(1, 'one'), T_COL(2, 'two'), T_COL(3, 'three')) IN EPAS:
========= edb=# SELECT array_agg(t_col(i,n)) FROM table(return_table); array_agg ----------------------------------- {"(1,one)","(2,two)","(3,three)"} (1 row)

 

2 REPLIES
Adventurer

Re: MULTISET

EXAMPLE:

 

  SELECT row1(id1,
                                  CAST(MULTISET(SELECT row2(A.id,
                                                       CAST(MULTISET(SELECT aa.id
                                                                       FROM (SELECT DISTINCT
                                                                                    *
                                                                               FROM TABLE(value1)) aa
                                                                      WHERE aa.id  = A.id
                                                                      ORDER BY TRUNC(aa.column1) NULLS LAST,
                                                                               NLSSORT(DECODE(column1, true, aa.column2, aa.column3), 'NLS_SORT=French'),
                                                                               aa.id) AS num_array))
                                                  FROM (SELECT DISTINCT
                                                               DECODE(value2,
                                                                      true,
                                                                      bb.id,
                                                                      aa.id) id,
                                                               aa.seq
                                                          FROM (SELECT aaa.*
                                                                  FROM Table1 aaa,
                                                                       TABLE(CAST(id7 AS num_array)) bbb
                                                                 WHERE bbb.COLUMN_VALUE = aaa.id) aa,
                                                               TABLE(value1) bb
                                                         WHERE bb.id(+) = aa.id
                                                         ORDER BY aa.seq) A
                                                 WHERE A.id IS NOT NULL) AS value),
                           
                                  CAST(MULTISET(SELECT bb.id
                                                  FROM TABLE(value1)            aa,
                                                        Table2        bb,
                                                       Table3 cc
                                                 WHERE column3              = column2
                                                   AND aa.id1            IN (DC.value1,
                                                                                     DC.value2,
                                                                                     DC.value3,
                                                                                     DC.value4,
                                                                                     DC.value5,)
                                                   AND bb.id2         = aa.id
                                                   AND bb.column1 = DC.value6
                                                   AND cc.id3                 = id1
                                                   AND cc.id2         = bb.id2) AS num_array))
          BULK COLLECT INTO value13
          FROM dual
         WHERE DC.value7 = DECODE(value9,
                                     false,
                                     true,
                                     DECODE(value9,
                                            0,
                                            false,
                                            true));

        RETURN value13;

EDB Team Member

Re: MULTISET

Below is the workaround. And it also shows how you should post the sample code:

Common code for Oracle and EPAS:
===================================
create or replace type t_col as object ( i number, n varchar2(30)); create or replace type t_nested_table as table of t_col; create or replace function return_table return t_nested_table as v_ret t_nested_table; begin v_ret := t_nested_table(); v_ret.extend; v_ret(v_ret.count) := t_col(1, 'one'); v_ret.extend; v_ret(v_ret.count) := t_col(2, 'two'); v_ret.extend; v_ret(v_ret.count) := t_col(3, 'three'); return v_ret; end return_table; select * from table(return_table); IN ORACLE:
=========== SQL> SELECT CAST(MULTISET(select * from table(return_table)) AS t_nested_table) FROM dual; CAST(MULTISET(SELECT*FROMTABLE(RETURN_TABLE))AST_NESTED_TABLE)(I, N) -------------------------------------------------------------------------------- T_NESTED_TABLE(T_COL(1, 'one'), T_COL(2, 'two'), T_COL(3, 'three')) IN EPAS:
========= edb=# SELECT array_agg(t_col(i,n)) FROM table(return_table); array_agg ----------------------------------- {"(1,one)","(2,two)","(3,three)"} (1 row)