comparing values between columns using LIKE on a DB2 6.1

[Laurent Gittler]

I would like to write a C++ application comparing values between columns
using LIKE on a DB2 6.1.

There is a limitation for that in DB2, for which it seems I cannot compare
columns . It is documented, but is really a painful limitation for me. Do
Anyboday has an idea about a workaround or an easy way to do. I would like
to avoid to split in several SQL queries to avoid as much as possible delays
due to network latency times on WANS (but it would be possible to make SQL
Blocks).

create table test (COL1 varchar(500), COL2 varchar(500))

insert into test values ('123456789', NULL)

select * from test where COL1 LIKE VALUE('123456789', '123') || '%' ====>
Works because '123456789' is fixed
select * from test where COL1 LIKE VALUE(COL2, '123') || '%' ====>
Doesn't work; seems due to a DB2 limitation (Oracle, MS SQL Server and
Sybase XI accept this kind of queries)

Has anybody an idea on how to build such queries in DB2 6.1 (using DB2 CLI)?
Thank you

Laurent Gittler

[truu...@ca.ibm.com]

Laurent Gittler wrote:
> ...

> create table test (COL1 varchar(500), COL2 varchar(500))
>
> insert into test values ('123456789', NULL)
>
> select * from test where COL1 LIKE VALUE('123456789', '123') || '%' ====>
> Works because '123456789' is fixed
> select * from test where COL1 LIKE VALUE(COL2, '123') || '%' ====>
> Doesn't work; seems due to a DB2 limitation (Oracle, MS SQL Server and
> Sybase XI accept this kind of queries)

So you want to find out whether col2 is a (leftmost) substring of col1?
You should be able to use one of the various string functions to do the
job. How about something like
... where LEFT(COL1, LENGTH(COL2)) = COL2
Or maybe you'd want to use the LOCATE function?

(Since it looks like you care about NULLs, add the appropriate IS NOT
NULL stuff where it makes sense. And if col2 is sometimes longer than
col1 then you have to do some length checking first to avoid problems).

Hope this helps,
--

Kaarel Truuvert
DB2 UDB Development