Take the following tables:
--------------------------------------------------------
-- DDL for Table TEST_ASSOCIATION
--------------------------------------------------------
CREATE TABLE "TEST_ASSOCIATION"
( "PARENT_ID" VARCHAR2(20 BYTE),
"CHILD_ID" VARCHAR2(20 BYTE)
) ;
--------------------------------------------------------
-- DDL for Table TEST_OBJECT
--------------------------------------------------------
CREATE TABLE "TEST_OBJECT"
( "ID" NUMBER,
"NAME" VARCHAR2(20 BYTE)
) ;
Now this query works perfectly:
select * from test_object t_o
where convert(t_o.name, 'US7ASCII') like '%a%'
And this one doesn't (error ORA-01482):
select * from test_object t_o
where
exists (
select * from test_association t_a
inner join test_object t_o2
on t_a.parent_id = t_o2.id
where t_o2.id = t_o.id
)
and convert(t_o.name, 'US7ASCII') like '%a%'
I suspect the inner query corrupts the CONVERT function, making it fail.
As anyone encountered something like that? Is it possible to correct this?