CODEWITHSUNDEEP
sqloracle-databasevarray
Ying Liu
Ying Liu

Reputation: 41

How to match user defined data type in SQL

I have a user defined data type CRM_IDS:

create or replace TYPE CRM_IDS AS VARRAY(10) OF VARCHAR(32);

In my table purecov_summary, the data type of column NAV_CRM_ID is CRM_IDS:

CREATE TABLE "PE_REG"."PURECOV_SUMMARY"
(
    ...
    "NAV_CRM_ID" "PE_REG"."CRM_IDS" ,
    ...
)

When I select the nav_crm_id of the first line with:

select nav_crm_id 
from purecov_summary 
where rownum = 1

I can get "PE_REG.CRM_IDS('10035005')".

But when I run:

select * 
from purecov_summary 
where nav_crm_id = PE_REG.CRM_IDS('10035005')

I get this error:

ORA-00932: inconsistent datatypes: expected - got PE_REG.CRM_IDS
00932. 00000 - "inconsistent datatypes: expected %s got %s"
*Cause:
*Action:
Error at Line: 1 Column: 37

How can I select with PE_REG.CRM_IDS type?

Upvotes: 1

Views: 90

Answers (1)

Barbaros Özhan
Barbaros Özhan

Reputation: 65218

The trick is to use nested varray column (nav_crm_id) within table keyword as table(nav_crm_id). So consider one of the following : 

select p.* 
  from purecov_summary p 
 where exists (select column_value from table(p.nav_crm_id) where column_value = '10035005')

or

select p.*
  from purecov_summary p
  join table(p.nav_crm_id) c
    on c.column_value = '10035005';

Demo

Upvotes: 2

Related Questions