Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Charset not supported when using convert and a join

856631Apr 20 2011 — edited Apr 26 2011
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?
This post has been answered by Peter Gjelstrup on Apr 22 2011
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 24 2011
Added on Apr 20 2011
11 comments
922 views