I have a table like below.
create table t2 ( id varchar2(1),val number) ;
insert into t2 values ('a',1);
insert into t2 values ('a',2);
insert into t2 values ('a',3);
insert into t2 values ('a',4);
insert into t2 values ('b',1);
insert into t2 values ('b',2);
insert into t2 values ('b',3);
insert into t2 values ('c',1);
insert into t2 values ('c',2);
insert into t2 values ('c',4);
insert into t2 values ('d',1);
insert into t2 values ('d',2);we have to print o/p like below.
id x
--- -------
a 1,2,3,4
b 1,2,3
c 1,2,4
d 1,2
this can achieve by below query
select id,LISTAGG(val, ',') WITHIN GROUP (ORDER BY val ) as x
from t2
group by id
Here x column is character datatype .But i need to convert this to varray of number / nested table of number ( not varray/nestedtable of character ).
i tried like below
CREATE TYPE varchar_TT AS TABLE OF varchar(10);
with z as (
select id,varchar_TT(LISTAGG(val, ',') WITHIN GROUP (ORDER BY val)) as x ,varchar_TT('1,2') y
from t2
group by id )
select id , x ,y from z ;
o/p
----
id x y
---- ------------- ---------------
a C##SIVA.<b>VARCHAR_TT('1,2,3,4')</b>C##SIVA.VARCHAR_TT('1,2')
b C##SIVA.<b>VARCHAR_TT('1,2,3') </b>C##SIVA.VARCHAR_TT('1,2')
c C##SIVA.<b>VARCHAR_TT('1,2,4') </b>C##SIVA.VARCHAR_TT('1,2')
d C##SIVA.<b>VARCHAR_TT('1,2') </b>C##SIVA.VARCHAR_TT('1,2')if i add below condition , i am not getting any result .
where y member of x ;so i tried to convert to number array .
CREATE TYPE number_TT AS TABLE OF number;
with z as (
select id,number_TT(LISTAGG(val, ',') WITHIN GROUP (ORDER BY val)) as x ,number_TT(1,2) y
from t2
group by id )
select id , x ,y from z ;
ORA-01722: invalid number
01722. 00000 - "invalid number"
*Cause: The specified number was invalid.
*Action: Specify a valid number.
1 ) Here i need o/p like below to use
member and
submultiset conditions.
o/p
----
id x y
---- ------------- ---------------
a C##SIVA.<b>NUMBER_TT(1,2,3,4) </b>C##SIVA.NUMBER_TT(1,2)
b C##SIVA.<b>NUMBER_TT(1,2,3) </b>C##SIVA.NUMBER_TT(1,2)
c C##SIVA.<b>NUMBER_TT(1,2,4) </b>C##SIVA.NUMBER_TT(1,2)
d C##SIVA.<b>NUMBER_TT(1,2) </b>C##SIVA.NUMBER_TT(1,2)
select varchar_tt('1,2') x ,number_TT(1,2) y from dual;
x y
-------------------- ----------------
C##SIVA.VARCHAR_TT('1,2') C##SIVA.NUMBER_TT(1,2)Please let me know how to convert character array to number array .
2)
create table t4 ( id VARCHAR2(1) , val number_tt )
NESTED TABLE val STORE AS val_2 ;
How to insert into t4 table from t2 ?
expected o/p query of t4 table should be like below
id val
---- -------------
a C##SIVA.<b>NUMBER_TT(1,2,3,4) </b>
b C##SIVA.<b>NUMBER_TT(1,2,3) </b>
c C##SIVA.<b>NUMBER_TT(1,2,4) </b>
d C##SIVA.<b>NUMBER_TT(1,2) </b>
i got similar output with below query .
select id , CAST(MULTISET(SELECT val FROM t2 where t2.id=x.id) as number_tt) as val1 from t2 x
id val1
--- --------------
a C##SIVA.NUMBER_TT(1, 2, 3, 4)
a C##SIVA.NUMBER_TT(1, 2, 3, 4)
a C##SIVA.NUMBER_TT(1, 2, 3, 4)
a C##SIVA.NUMBER_TT(1, 2, 3, 4)
b C##SIVA.NUMBER_TT(1, 2, 3)
b C##SIVA.NUMBER_TT(1, 2, 3)
b C##SIVA.NUMBER_TT(1, 2, 3)
c C##SIVA.NUMBER_TT(1, 2, 4)
c C##SIVA.NUMBER_TT(1, 2, 4)
c C##SIVA.NUMBER_TT(1, 2, 4)
d C##SIVA.NUMBER_TT(1, 2)
d C##SIVA.NUMBER_TT(1, 2)
But it has duplicates .
i tried with
distinct but it is not working .
so i used group by , it is working .
select id , CAST(MULTISET(SELECT val FROM t2 where t2.id=x.id) as number_tt) as val1 from t2 x
group by id
Is there any way other than the above ?
I think you're getting mixed up in how to find the matching rows. And how you display those rows in the end.
Listagg is handy for the final display. But it's not building an array! Just a string of comma separated values.
If you stick the output of listagg into a nested table, you have one element in the array. Which is a CSV string. To have each value as an array element, you need to collect() them in. Then do your comparisons on this.
You're dealing with numbers. So you want to compare nested tables of numbers. There are a couple of ways you can do this
MemberUse this to see if one value of the type for the nested table exists
with z as (
select id,
cast ( collect ( val ) as number_TT ) n_array,
3 n,
number_TT ( 1, 3 ) ntt,
listagg ( val, ',' ) within group ( order by val ) vals
from t2
group by id
)
select z.*
from z
where n member of n_array;
ID N_ARRAY N NTT VALS
a [1, 4, 3, 2] 3 [1, 3] 1,2,3,4
b [1, 3, 2] 3 [1, 3] 1,2,3 Submutliset Use this to check if all the elements in one nested table exist in another.
with z as (
select id,
cast ( collect ( val ) as number_TT ) n_array,
3 n,
number_TT ( 1, 3 ) ntt,
listagg ( val, ',' ) within group ( order by val ) vals
from t2
group by id
)
select z.*
from z
where ntt submultiset of n_array;
ID N_ARRAY N NTT VALS
a [1, 4, 3, 2] 3 [1, 3] 1,2,3,4
b [1, 3, 2] 3 [1, 3] 1,2,3 Note - display of the nested tables in the results looking like arrays is SQL Developer's processing of this. Different clients will have different ways of showing these!
Of course, I'm assuming you need to use nested tables here for some reason. The problem here is a form of relational division. Which you can solve with pure SQL if you want to:
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9534489800346122396