Hi,

How can I execute a Query with field BLOB with grouping?
Does have as converting BLOB to VARCHAR?

Thanks

Example....


[db2ihrs@hrsdsvx ~]$ db2 describe table t1

Data type Column
Column name schema Data type name Length Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
C1 SYSIBM BLOB 1048576 0 Yes
C2 SYSIBM INTEGER 4 0 Yes




[db2ihrs@hrsdsvx ~]$ db2 "select c1, avg(c2) c2 from t1 group by c1"
SQL0134N Improper use of a string column, host variable, constant, or
function "C1". SQLSTATE=42907


I can tested with varchar(c1)... cast(c1 as varchatr(32)) however don't work ;(

I don't think you can group by LOB expressions; also, the maximum length of the GROUP BY columns cannot exceed 32K. Try using SUBSTR() and VARCHAR().

SUBSTR and VARCHAR don't work on BLOBs - only on CLOBs. What you can do is to write an external UDF that does something with the BLOB to produces a VARCHAR or INT or so. The question is what that "something" should be, and this is typically highly application-specific.