CODEWITHSUNDEEP
sqloracle-databasefunctionuser-defined-types
Pancho
Pancho

Reputation: 2193

Oracle SQL: using LAG function with user-defined-type returns "inconsistent datatypes"

I have a type MyType defined as follows:

create or replace type MyType as varray(20000) of number(18);

And a table MyTable defined as follows:

create table MyTable (
   id       number(18) primary key
  ,widgets  MyType
)

I am trying to select the widgets for each row and its logically previous row in MyTable using the following SQL:

select  t.id
       ,lag(t.widgets,1) over (order by t.id) as widgets_previous
from MyTable t
order by t.id;

and I get the response:

ORA-00932: inconsistent datatypes: expected - got MYSCHEMA.MYTYPE

If I run the exact same query using a column of type varchar or number instead of MyType it works fine.

The type of the column in the current row and its previous row must be the same so I can only assume it is something related to the user defined type.

Do I need to do something special to use LAG with a user defined type, or does LAG not support user defined types? If the latter, are there any other utility functions that would provide the same functionality or do I need to do a traditional self join in order to achieve the same?

Upvotes: 7

Views: 789

Answers (3)

Pancho
Pancho

Reputation: 2193

After reading all the above I've opted for the following as the most effective method for achieving what I need:

select curr.id
      ,curr.widgets  as widgets
      ,prev.widgets  as previous_widgets
from (select  a.id                                      
             ,a.widgets
             ,lag(a.id,1) over (order by a.id)  as previous_id
      from mytable a
     ) curr
     left join mytable prev on (prev.id = curr.previous_id)
order by curr.id

ie. a lag / self join hybrid using lag on a number field that it doesn't complain about to identify the join condition. It's fairly tidy I think and I get my collections as desired. Thanks to everyone for the extremely useful input.

Upvotes: 2

tbone
tbone

Reputation: 15483

You could try something like:

SQL> create or replace type TestType as varray(20000) of number(18);
Type created.
SQL> create table TestTable (
   id       number(18) primary key
  ,widgets  TestType
)
Table created.
SQL> delete from testtable
0 rows deleted.
SQL> insert into TestTable values (1, TestType(1,2,3,4))
1 row created.
SQL> insert into TestTable values (2, TestType(5,6,7))
1 row created.
SQL> insert into TestTable values (3, TestType())
1 row created.
SQL> insert into TestTable values (4,null)
1 row created.
SQL> commit
Commit complete.

SQL> -- show all data with widgets
SQL> select t.id, w.column_value as widget_ids
from testtable t, table(t.widgets) w

        ID WIDGET_IDS
---------- ----------
         1          1
         1          2
         1          3
         1          4
         2          5
         2          6
         2          7

7 rows selected.
SQL> -- show with lag function
SQL> select t.id, lag(w.column_value, 1) over (order by t.id) as widgets_previous
from testtable t, table(t.widgets) w

        ID WIDGETS_PREVIOUS
---------- ----------------
         1                 
         1                1
         1                2
         1                3
         2                4
         2                5
         2                6

7 rows selected.

Upvotes: 0

Kacper
Kacper

Reputation: 4818

You can use lag with UDT. The problem is varray

Does this give you a result?

select  t.id
       ,lag(
              (select listagg(column_value, ',') within group (order by column_value) 
                  from table(t.widgets))
            ,1) over (order by t.id) as widgets_previous
from MyTable t
order by t.id;

Upvotes: 1

Related Questions