Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2008
    Posts
    14

    Unanswered: How I can to execute a Query with field BLOB with grouping?

    Hi,

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

    Thanks

  2. #2
    Join Date
    Nov 2008
    Posts
    14
    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 ;(

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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().
    ---
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •