1

I have an issue whith this query which doesn't work :

select count(*)
from MYTABLE where
MYFIELD in (select trim(cast(CLOBFIELD as varchar(20000))) from TABLE2) ;
=>0 rows

The subquery return right results :

select trim(cast(CLOBFIELD as varchar(20000))) from TABLE2 ; 
=>1202,1203,1205,1206,1207,1208,1209,1210,1211,1212,1213,1214,1215,1216,1217,1218,1226

This query which doesn't have the subquery returns right reusults. 

select count(*)
from MYTABLE where
MYFIELD in (1202,1203,1205,1206,1207,1208,1209,1210,1211,1212,1213,1214,1215,1216,1217,1218,1226) ;

The column CLOBFIELD is a CLOB field VS the column is a char(4) field. In my opinion, this a cast issue in the subquery because of the cast on the clob field. I don't know what's wrong, I am not very familar with DB2, does anybody can help me ?

ben.IT
  • 1,490
  • 2
  • 18
  • 37
  • 2
    This isn't actually a DB2 issue, **no** RDBMS supports this natively. What's happening is that the db still sees the subquery as returning one long string, which of course doesn't match any ids. You need dynamic SQL to parse previous query results, a `SESSION` table to load to, a UDF to expand it for you, or potentially other options based on exact platform. [Here's a sample expanding function](http://stackoverflow.com/a/337752/812837) written for SQL Server that should be adaptable. Note that difficulty for querying is one of the reasons why you _shouldn't_ store data like this. – Clockwork-Muse Apr 29 '14 at 14:13
  • Which DB2 server are you using? DB2 for IBM i, z/OS, or LUW? – WarrenT Apr 29 '14 at 14:46

1 Answers1

0

As comments above say, it isn't right to mess with casting large datatypes like this, but here is some code to try:

select count(*)
from MYTABLE 
where LOCATE_IN_STRING((select ','||trim(cast(CLOBFIELD as varchar(20000)))||',' from TABLE2), ','||trim(MYFIELD)||','  ) > 0  ;

Can have problems if your values contain commas though.

Perhaps you could investigate applying a text search index? http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=%2Fcom.ibm.db2.luw.admin.ts.doc%2Fdoc%2Ft_creatingafulltextindex.html

However, if you need to access the values in the CLOB, perhaps they shouldn't be in a CLOB?

Turophile
  • 3,367
  • 1
  • 13
  • 21