db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mamta Satoor" <msat...@gmail.com>
Subject Re: systemtables
Date Tue, 04 Dec 2007 17:40:03 GMT
Did you create a database with territory based collation? Also, what
version of Derby are you using?

When I try following sql in a territory based collation database with
current schema as 'APP'
select tablename from SYS.systables t where t.tablename = 'mytable';
I get following exception
Caused by: ERROR 42818: Comparisons between 'VARCHAR (UCS_BASIC)' and
'CHAR (TERRITORY_BASED)' are not supported. Types must be comparable.
String types must also have matching collation. If collation does not
match, a possible solution is to cast operands to force them to the
default collation (e.g. select tablename f
rom sys.systables where CAST(tablename as VARCHAR(128)) = 'T1')

To fix the problem, one can either set their current schema to be SYS,
as follows
ij> set schema sys;
0 rows inserted/updated/deleted
ij> select tablename from SYS.systables t where t.tablename = 'mytable';
TABLENAME
--------------------------------------------------------------------------------
------------------------------------------------
0 rows selected

Or the other alternative to changing the current schema would be use
CAST around t.tablename so that the resultant string now has the same
collation as the string literal 'mytable'

select tablename  from SYS.systables t where CAST(t.tablename as
VARCHAR(128)) = 'mytable';

Mamta
On 12/4/07, Thomas Vatter <thomas_vatter@web.de> wrote:
> Hi there,
>
> I am trying to make a selection from the systemtables and running into
> an error message. The select string is
>    select * from SYS.systables t where t.tablename = 'mytable'
>
> (The table 'mytable' really is existing in systables.tablename.)
> The error message is
>   Comparisons between 'VARCHAR' and 'CHAR' are not supported.
>
> What am I doing wrong?
>
> tom
>

Mime
View raw message