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 <[EMAIL PROTECTED]> 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 >
