Hi,

Following query was running sucessfully on AIX, WAS 3.02, DB2 6.1 but
when we apply DB2 fixpack 6.1.6, it is showing a strange behaviour.
The secound column (cat_tree.cat_id) is returning me the Hexadecimal
value instead of a string. I checked that value by reading using a
HexaEditor and it is showing me the exact string value (brand name).
Here all cat_id's are integer and attr_value is a string. The result
should be:
123 cK
444 Dockers

public static final String EXEC_STMT =
" WITH cat_tree (parent_cat_id, cat_id)"
+ "\n AS"
+ "\n (select root.cat_id, root.cat_id"
+ "\n from category root"
+ "\n where root.parent_cat_id is null"
+ "\n UNION ALL"
+ "\n select parent.parent_cat_id"
+ "\n ,child.cat_id"
+ "\n from cat_tree parent"
+ "\n ,category child"
+ "\n where child.parent_cat_id is not null"
+ "\n and child.parent_cat_id = parent.cat_id)"
+ "\n select DISTINCT"
+ "\n -1 AS parent_cat_id"
+ "\n ,upr.attr_value AS attr_value"
+ "\n from product prd"
+ "\n ,category_product cp"
+ "\n ,unary_prd_attr_val upr"
+ "\n where prd.display_stat_code = 1"
+ "\n and prd.search_visible = 'Y'"
+ "\n and prd.prod_id = cp.prod_id"
+ "\n and prd.prod_id = upr.prod_id"
+ "\n and upr.attr_name = 'BRAND'"
+ "\n UNION"
+ "\n select ct.parent_cat_id AS parent_cat_id"
+ "\n ,upr.attr_value AS attr_value"
+ "\n from cat_tree ct"
+ "\n ,category_product cp"
+ "\n ,product prd"
+ "\n ,unary_prd_attr_val upr"
+ "\n where ct.parent_cat_id in (select cat_id"
+ "\n from category"
+ "\n where parent_cat_id
is null)"
+ "\n and ct.cat_id = cp.cat_id"
+ "\n and cp.prod_id = prd.prod_id"
+ "\n and prd.display_stat_code = 1"
+ "\n and prd.search_visible = 'Y'"
+ "\n and prd.prod_id = upr.prod_id"
+ "\n and upr.attr_name = 'BRAND'"
+ "\n group by parent_cat_id"
+ "\n ,upr.attr_value"
+ "\n order by parent_cat_id"
+ "\n ,attr_value";

Thanks in advance.

Sushil Singh