user982124 user982124 - 4 months ago 14
MySQL Question

Creaet VIEW but alter data type for selected columns

I have successfully created a MySQL View but have just noticed that the data type for most of the columns is "longtext". I need to change some of these to integers but it looks like you can't change the data type for the View columns after the View has been created.

Is it possible to specify the data type for individual columns when creating the View? Here's my SQL query:

select p.ID as order_id,
p.post_date,
max(CASE WHEN pm.meta_key = '_billing_email' and p.ID = pm.post_id
THEN pm.meta_value END) as billing_email,
max(CASE WHEN pm.meta_key = '_billing_first_name' and p.ID = pm.post_id
THEN pm.meta_value END) as _billing_first_name,
max(CASE WHEN pm.meta_key = '_billing_last_name' and p.ID = pm.post_id
THEN pm.meta_value END) as _billing_last_name,
max(CASE WHEN pm.meta_key = '_order_total' and p.ID = pm.post_id
THEN pm.meta_value END) as order_total,
max(CASE WHEN pm.meta_key = '_order_tax' and p.ID = pm.post_id
THEN pm.meta_value END) as order_tax,
max(CASE WHEN pm.meta_key = '_paid_date' and p.ID = pm.post_id
THEN pm.meta_value END) as paid_date
from wp_posts as p,
wp_postmeta as pm
where post_type = 'shop_order'
and p.ID = pm.post_id
and
and post_status = 'wc-completed'
group by p.ID


I would like the order_tax and order_total columns to be integers (e.g. bigint(20)). I'm new to SQL in general and haven't been able to find the syntax if this is possible?

Answer

Thanks - I was able to get the View data types set correctly by using CAST, e.g.:

    CAST(max( CASE WHEN pm.meta_key = '_line_subtotal' and p.order_item_id = pm.order_item_id THEN pm.meta_value END ) AS UNSIGNED) as subtotal