开发者

Mysql CASE - WHEN - THEN - returning wrong data type (blob)

Im creating customizable product attributes in a web store - each attribute can have different type of data, each data type is stored in a separate column using corresponding mysql datatype.

I Have a query like:

SELECT
products.id AS id,
products.sku AS sku,
products.name AS name,
products.url_key AS url_key,
attributes.name AS attribute, 

CASE
    WHEN `attribute_types`.`type` = 'text' 
        THEN product_attribute_values.value_text
    WHEN `attribute_types`.`type` = 'float' 
        THEN product_attribute_values.value_float
    WHEN `attribute_types`.`type` = 'price' 
        THEN product_attribute_values.value_float
    WHEN `attribute_types`.`type` = 'integer' 
        THEN product_attribute_values.value_integer
    WHEN `attribute_types`.`type` = 'multiple' 
        THEN product_attribute_values.value_text
    WHEN `attribute_types`.`type` = 'dropdown' 
        THEN product_attribute_values.value_text
    WHEN `attribute_types`.`type` = 'date' 
        THEN product_attribute_values.value_date
    WHEN `attribute_types`.`type` = 'textarea' 
        THEN product_attribute_values.value_textarea
END as value

from (...)
开发者_如何学Go

Now, the problem is that when attribute_types.type equals to ?some-type? i want it to return a value as it's stored in product_attribute_values table. Currently I get BLOb every time.

Should I use type-casting or there's some behind-the-scene magic that I dont know about, OR maybe there's some better alternative ?

EDIT:

Everything seems to be OKAY (im checking price that is float) until i add a condition for TEXT (textarea).


It seems you are using some query browser. Try executing this command through 'Putty'.

Also, to get the correct output even in the query browser, include CAST function in your CASE statement like this.

CAST(
CASE
WHEN `attribute_types`.`type` = 'text' 
    THEN product_attribute_values.value_text
WHEN `attribute_types`.`type` = 'float' 
    THEN product_attribute_values.value_float
WHEN `attribute_types`.`type` = 'price' 
    THEN product_attribute_values.value_float
WHEN `attribute_types`.`type` = 'integer' 
    THEN product_attribute_values.value_integer
WHEN `attribute_types`.`type` = 'multiple' 
    THEN product_attribute_values.value_text
WHEN `attribute_types`.`type` = 'dropdown' 
    THEN product_attribute_values.value_text
WHEN `attribute_types`.`type` = 'date' 
    THEN product_attribute_values.value_date
WHEN `attribute_types`.`type` = 'textarea' 
    THEN product_attribute_values.value_textarea
END 
AS CHAR) as value
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜