Using length function when access db2 table get errors

Reply
New Contributor
Posts: 2

Using length function when access db2 table get errors

My code & log is below. Any help is much appreciated! Thanks in advance.

lib.table: var1 char(6);

code:

libname lib db2 datasrc=... user=... password=...;

data t1;

set lib.table;

where length(var1)=6;

run;

log:

......

DB2_2: Prepared: on connection 0

SELECT  "var1"  FROM table  WHERE  ({fn LENGTH( "var1")} = '6' ) FOR READ ONLY

The following error message may be issued when using SAS/ACCESS Interface to DB2:

ERROR: CLI describe error: [IBM][CLI Driver][DB2/NT] SQL0401N The data types of the operands for the operation "=" are not

compatible or comparable. SQLSTATE=42818

Respected Advisor
Posts: 3,896

Re: Using length function when access db2 table get errors

I believe this is a SAS "feature" generating wrong SQL pass-through code when using the length statement where the DB2 column has a type of CHAR.

I'm not aware of a fix for this (there might be one but I couldn't find it).

Easiest would be to re-write your code using SQL Pass-through. If you can change the DB2 column type then another way to go would be to change it from CHAR to VARCHAR - which I feel would anyway be the right thing to have if this column is used to store variable length strings.

New Contributor
Posts: 2

Re: Using length function when access db2 table get errors

Thanks a lot!

Ask a Question
Discussion stats
  • 2 replies
  • 328 views
  • 3 likes
  • 2 in conversation