ORA-00932: inconsistent datatypes: expected CHAR got SYS.ODCIVARCHAR2LIST
Veronica, August 14, 2017 - 9:57 pm UTC
Thanks Connor.
When I implemented the same in the WHERE condition of my code it error-ed out
"ORA-00932: inconsistent datatypes: expected CHAR got SYS.ODCIVARCHAR2LIST"
Please find below some sample queries:
CREATE TABLE EMP
(
EMPNO NUMBER(4) ,
ENAME VARCHAR2(50 BYTE) ,
JOB VARCHAR2(50 BYTE) ,
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(10,2),
COMM NUMBER(10,2),
DEPTNO NUMBER(2) ,
REPORT_STATUS VARCHAR2(20 BYTE)
);
Insert into EMP
(EMPNO, ENAME, JOB, MGR, HIREDATE,
SAL, COMM, DEPTNO, REPORT_STATUS)
Values
(7369, 'SMITH', 'CLERK', 7902, TO_DATE('06/27/2017 18:42:12', 'MM/DD/YYYY HH24:MI:SS'),
800, 0, 20, 'new');
Insert into EMP
(EMPNO, ENAME, JOB, MGR, HIREDATE,
SAL, COMM, DEPTNO, REPORT_STATUS)
Values
(7499, 'ALLEN', 'SALESMAN', 7698, TO_DATE('06/26/2017 18:42:53', 'MM/DD/YYYY HH24:MI:SS'),
1600, 300, 30, 'sent');
Insert into EMP
(EMPNO, ENAME, JOB, MGR, HIREDATE,
SAL, COMM, DEPTNO, REPORT_STATUS)
Values
(7521, 'WARD', 'SALESMAN', 7698, TO_DATE('06/25/2017 18:42:53', 'MM/DD/YYYY HH24:MI:SS'),
1250, 500, 30, 'sent');
commit;
select * from emp
where report_status =
(CASE
WHEN ( :lv_empno IS NULL
AND :lv_ename IS NULL)
THEN
sys.odcivarchar2list('new')
else sys.odcivarchar2list('new','sent')
end);
When I pass NULL to all the parameters, it must pick the data with status 'new'.
When I pass a non-NULL value to all the parameters then it could pick data with status 'new' or 'sent'
Thanks
August 16, 2017 - 12:03 pm UTC
See my second example, ie
SQL> select *
2 from table(
3 select
4 case
5 when to_char(sysdate,'SS') > 30 then sys.odcivarchar2list('Sent')
6 else sys.odcivarchar2list('New','Sent')
7 end options
8 from dual
9 );
COLUMN_VALUE
--------------------------------------------------------------------------
New
Sent
So that is now a set of rows. So then your query becomes:
select *
from emp
where report_status in
(
select *
from table(
select
case
when to_char(sysdate,'SS') > 30 then sys.odcivarchar2list('Sent')
else sys.odcivarchar2list('New','Sent')
end options
from dual
)
)
AWESOME
ATIQ, August 28, 2020 - 1:32 pm UTC
You don't know how many hours i wasted just to find your solution.....
August 31, 2020 - 3:46 am UTC
glad we could help
Chatura, March 02, 2021 - 11:36 pm UTC
Hi
I used the method shown in the example for one of my queries but got an error. Please help. I need to use this query in my APEX parameter driven interactive report.
Query:
select *
from table(
select
case
when to_number(:P70_CURRENT_STATUS) = 1
then sys.odcivarchar2list(
'Base Period', 'Evergreen', 'Option Period (Annual)',
'Option Period (Monthly)', 'Option Period (up to ''n'' Days)',
'Until Canceled', 'Board Extension'
)
when to_number(:P70_CURRENT_STATUS) = 2
then sys.odcivarchar2list( 'Base Period', 'Canceled', 'Evergreen', 'Expired', 'Option Period (Annual)',
'Option Period (Monthly)', 'Option Period (up to ''n'' Days)', 'Until Canceled',
'Board Extension'
)
end options
from dual
);
Error:
ORA-22905: cannot access rows from a non-nested table item
22905. 00000 - "cannot access rows from a non-nested table item"
*Cause: attempt to access rows of an item whose type is not known at
parse time or that is not of a nested table type
*Action: use CAST to cast the item to a nested table type
Error at Line: 2 Column: 6
March 03, 2021 - 8:44 am UTC
You can't use case expressions inside the table operator like that.
You probably want to do something like this instead:
select * from table (
sys.odcivarchar2list (
'Base Period',
'Evergreen',
'Option Period (Annual)',
'Option Period (Monthly)',
'Option Period (up to ''n'' Days)',
'Until Canceled',
'Board Extension'
)
)
where to_number ( :p70_current_status ) = 1
union all
select * from table (
sys.odcivarchar2list (
'Base Period',
'Canceled',
'Evergreen',
'Expired',
'Option Period (Annual)',
'Option Period (Monthly)',
'Option Period (up to ''n'' Days)',
'Until Canceled',
'Board Extension'
)
)
where to_number ( :p70_current_status ) = 2
Chatura, March 03, 2021 - 3:47 pm UTC
You same my life.
Thank you so much for your quick response.
Your help is greatly appreciated.
March 03, 2021 - 5:46 pm UTC
You're welcome