Oracle SQL: using a LAG function with custom types returns "inconsistent data types",

Question

I have a type MyType, which is defined as follows:

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

And the MyTable table is defined as follows:

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

I am trying to select 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 answer:

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

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

The column type in the current row and the previous row should be the same, so I can only assume that this is due to a user-defined type.

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

Answer 1

After reading all of the above, I chose the following as the most effective method to achieve 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 

i.e. hybrid with a delay / self-connect, using a delay in the number field in which it does not complain to identify the connection condition. I think this is pretty neat and I get my collections as I wish. Thanks to everyone for the extremely helpful input.

Answer 2

You can use lag with UDT. The problem is varray

Does this give you the 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; 

Answer 3

You can 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.