asked on

CAST statement not working with UNION select (MySQL)

In my UNION of 2 SELECT statements below my CAST value of m3.meta_key (ie. capacity) gets assigned to 0.00 for most of my values and then only a handful get assigned a proper value. However, when I run the statements separately (without UNION) the total is calculated properly. I'm not sure what is causing the error. Is it because of the duplicated table aliases?

Select a.*, CASE WHEN m3.meta_key <> 'WPPP_dimensions' THEN CASE WHEN m3.meta_value like '%
%' AND (m3.meta_value like '%x%' OR m3.meta_value like '%X%') and SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), '
', -1) like '%x%' THEN CAST(SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), '
', 1),' Gallons',''), 'x', 1) * SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), '
', 1),' Gallons',''), 'x', -1) + SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), '
', -1),' Gallons',''), 'x', 1) * SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), '
', -1),' Gallons',''), 'x', -1) as decimal(10,2)) WHEN m3.meta_value like '%and%' AND (m3.meta_value like '%x%' OR m3.meta_value like '%X%') and SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), 'and', -1) like '%x%' THEN CAST(SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), 'and', 1),' Gallons',''), 'x', 1) * SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), 'and', 1),' Gallons',''), 'x', -1) + SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), 'and', -1),' Gallons',''), 'x', 1) * SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), 'and', -1),' Gallons',''), 'x', -1) as decimal(10,2)) WHEN m3.meta_value like '%&%' AND (m3.meta_value like '%x%' OR m3.meta_value like '%X%') and SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), '&', -1) like '%x%' THEN CAST(SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), '&', 1),' Gallons',''), 'x', 1) * SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), '&', 1),' Gallons',''), 'x', -1) + SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), '&', -1),' Gallons',''), 'x', 1) * SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), '&', -1),' Gallons',''), 'x', -1) as decimal(10,2)) WHEN m3.meta_value like '%
%' AND (m3.meta_value like '%x%' OR m3.meta_value like '%X%') THEN CAST(SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), '
', 1),' Gallons',''), 'x', 1) * SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), '
', 1),' Gallons',''), 'x', -1) + SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), '
', -1),' Gallons',''), 'x', 1) as decimal(10,2)) WHEN m3.meta_value like '%and%' AND (m3.meta_value like '%x%' OR m3.meta_value like '%X%') THEN CAST(SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), 'and', 1),' Gallons',''), 'x', 1) * SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), 'and', 1),' Gallons',''), 'x', -1) + SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), 'and', -1),' Gallons',''), 'x', 1) * SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), 'and', -1),' Gallons',''), 'x', -1) as decimal(10,2)) WHEN m3.meta_value like '%&%' AND (m3.meta_value like '%x%' OR m3.meta_value like '%X%') THEN CAST(SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), '&', 1),' Gallons',''), 'x', 1) * SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), '&', 1),' Gallons',''), 'x', -1) + SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), '&', -1),' Gallons',''), 'x', 1) as decimal(10,2)) WHEN m3.meta_value like '%X%' THEN CAST(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), 'x', 1) * SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), 'x', -1) as decimal(10,2)) WHEN m3.meta_value like '%/%' OR m3.meta_value IS NULL THEN 0 ELSE CAST(m3.meta_value as decimal(10,2)) END ELSE 0 END as capacity from WPPPposts z
INNER JOIN WPPPterm_relationships e ON z.ID = e.object_id
INNER JOIN WPPPterm_taxonomy c ON c.term_taxonomy_id = e.term_taxonomy_id
INNER JOIN WPPPterms d ON c.term_id = d.term_id
INNER JOIN WPPPterm_relationships f ON f.term_taxonomy_id = e.term_taxonomy_id
INNER JOIN WPPPposts a ON f.object_id = a.ID
INNER JOIN WPPPpostmeta m3 ON ( m3.post_id = z.ID )

INNER JOIN WPPPterm_relationships ze ON a.ID = ze.object_id
INNER JOIN WPPPterm_taxonomy zc ON zc.term_taxonomy_id = ze.term_taxonomy_id
INNER JOIN WPPPterms zd ON zc.term_id = zd.term_id

WHERE zd.slug = 'commercial-trash-and-recycle-bins' AND a.post_status = 'publish' AND m3.meta_key IN ('WPPP_capacity','WPPP_dimensions') AND z.post_type = 'modelnumbers' AND c.taxonomy = 'modelgroups' AND a.post_type = 'recyclingbins' AND
NOT EXISTS
(SELECT * FROM WPPPposts NOTA
INNER JOIN WPPPpostmeta NOTAA ON (NOTA.ID = NOTAA.post_id)
WHERE (NOTAA.meta_value = 'accessory' OR NOTAA.meta_value = 'lid') AND NOTAA.meta_key = 'WPPP_accessory' AND NOTA.ID = z.ID)

UNION

SELECT a.*, CASE WHEN m3.meta_key <> 'WPPP_dimensions' THEN CASE WHEN m3.meta_value like '%
%' AND (m3.meta_value like '%x%' OR m3.meta_value like '%X%') and SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), '
', -1) like '%x%' THEN CAST(SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), '
', 1),' Gallons',''), 'x', 1) * SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), '
', 1),' Gallons',''), 'x', -1) + SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), '
', -1),' Gallons',''), 'x', 1) * SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), '
', -1),' Gallons',''), 'x', -1) as decimal(10,2)) WHEN m3.meta_value like '%and%' AND (m3.meta_value like '%x%' OR m3.meta_value like '%X%') and SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), 'and', -1) like '%x%' THEN CAST(SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), 'and', 1),' Gallons',''), 'x', 1) * SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), 'and', 1),' Gallons',''), 'x', -1) + SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), 'and', -1),' Gallons',''), 'x', 1) * SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), 'and', -1),' Gallons',''), 'x', -1) as decimal(10,2)) WHEN m3.meta_value like '%&%' AND (m3.meta_value like '%x%' OR m3.meta_value like '%X%') and SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), '&', -1) like '%x%' THEN CAST(SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), '&', 1),' Gallons',''), 'x', 1) * SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), '&', 1),' Gallons',''), 'x', -1) + SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), '&', -1),' Gallons',''), 'x', 1) * SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), '&', -1),' Gallons',''), 'x', -1) as decimal(10,2)) WHEN m3.meta_value like '%
%' AND (m3.meta_value like '%x%' OR m3.meta_value like '%X%') THEN CAST(SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), '
', 1),' Gallons',''), 'x', 1) * SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), '
', 1),' Gallons',''), 'x', -1) + SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), '
', -1),' Gallons',''), 'x', 1) as decimal(10,2)) WHEN m3.meta_value like '%and%' AND (m3.meta_value like '%x%' OR m3.meta_value like '%X%') THEN CAST(SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), 'and', 1),' Gallons',''), 'x', 1) * SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), 'and', 1),' Gallons',''), 'x', -1) + SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), 'and', -1),' Gallons',''), 'x', 1) * SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), 'and', -1),' Gallons',''), 'x', -1) as decimal(10,2)) WHEN m3.meta_value like '%&%' AND (m3.meta_value like '%x%' OR m3.meta_value like '%X%') THEN CAST(SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), '&', 1),' Gallons',''), 'x', 1) * SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), '&', 1),' Gallons',''), 'x', -1) + SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), '&', -1),' Gallons',''), 'x', 1) as decimal(10,2)) WHEN m3.meta_value like '%X%' THEN CAST(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), 'x', 1) * SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), 'x', -1) as decimal(10,2)) WHEN m3.meta_value like '%/%' OR m3.meta_value IS NULL THEN 0 ELSE CAST(m3.meta_value as decimal(10,2)) END ELSE 0 END as capacity FROM WPPPposts mp INNER JOIN WPPPpostmeta m1 ON ( mp.ID = m1.post_id ) INNER JOIN WPPPpostmeta m2 ON ( m2.post_id = m1.post_id ) INNER JOIN WPPPpostmeta m3 ON ( m3.post_id = m1.post_id ) INNER JOIN WPPPpostmeta m4 ON ( m4.post_id = m1.post_id ) INNER JOIN WPPPposts a ON m1.meta_value = a.ID INNER JOIN WPPPterm_relationships e ON a.ID = e.object_id INNER JOIN WPPPterm_taxonomy c ON c.term_taxonomy_id = e.term_taxonomy_id INNER JOIN WPPPterms d ON c.term_id = d.term_id WHERE m3.meta_key IN ('WPPP_capacity','WPPP_dimensions') AND mp.post_type = 'modelnumbers' AND mp.post_status = 'publish' AND m1.meta_key = 'WPPP_modelproductselect' AND m2.meta_key = 'WPPP_prodtype' AND e.object_id = a.ID AND d.slug = 'commercial-trash-and-recycle-bins' AND a.post_status='publish' AND a.post_type = 'recyclingbins'

Group By a.ID
Order By CONVERT(capacity, decimal(10,2)) asc LIMIT 48 offset 0

Koen Van Wielink

First of all, please try to post your SQL queries using the "Code" feature when you create your question. This makes it easier for us to read.
Secondly, it's very hard to answer your question without any sample data, especially when you have such a complex and unstructured query. But my gut feel would be that either some of your CAST values return Null and that that is messing up your data, or that perhaps you have some duplicate values. Union does not filter out duplicates, whereas UNION ALL does. Check for NULL values and provision for them (e.g. use ISNULL() to replace a null value with an empty string for example), and try using Union ALL to exclude duplicate values.
If that still doesn't work, I'd recommend posting some sample data that we can work on.

Mike Tew

ASKER

Hi Koen
I think I'll try using temporary tables and do and insert then SELECT off of that. There is a ton of data in the UNION ALL, an I'm concerned its going to slow down the page too much.

Mike Tew

ASKER

Hi Koen,
As a side note I tool a look at the original query and if I remove the LIMIT 48 offset 0 then I can see its works, but its adding more rows with the same a.ID (ie. duplicates) where the capacity is set to 0.

Koen Van Wielink

Temp tables might be a better idea. Allows you to break things down into smaller chunks without too much overhead. There's also the possibility of writing a custom function that handles some of these string checks for you, but I'm not sure if that's a smart way to go performance wise. But calling a single inline function rather than the entire case statement would certainly improve the readability of this beast.
I'm still pretty sure that the union either cancels out some of your results, or that a Null is screwing things up.

Mike Tew

ASKER

Hi Koen,
I think your right about the results, it would explain the additional zero values.

For the temporary tables, what would be the best way to insert?

I was going to do something like this (although I'm getting a synax error with the INSERT INTO). Am I missing brackets as the SELECT CAST works on its own? I changed the query so its only grabbing the ID and setting the capacity.

CREATE TEMPORARY TABLE CapacitySummary (Cid INT,Ccapacity INT)
                                     
INSERT INTO CapacitySummary (Cid, Ccapacity)
     Select a.ID,  
     CASE 
      WHEN m3.meta_key <> 'WPPP_dimensions' THEN 
        CASE 
        WHEN m3.meta_value like '%
%' AND (m3.meta_value like '%x%' OR m3.meta_value like '%X%') and SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), '', -1) like '%x%' 
        THEN CAST(SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), '', 1),' Gallons',''), 'x', 1) * SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), '
', 1),' Gallons',''), 'x', -1) + SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), '
', -1),' Gallons',''), 'x', 1) * SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), '
', -1),' Gallons',''), 'x', -1) as decimal(10,2)) 
        WHEN m3.meta_value like '%and%' AND (m3.meta_value like '%x%' OR m3.meta_value like '%X%') and SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), 'and', -1) like '%x%' 
        THEN CAST(SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), 'and', 1),' Gallons',''), 'x', 1) * SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), 'and', 1),' Gallons',''), 'x', -1) + SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), 'and', -1),' Gallons',''), 'x', 1) * SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), 'and', -1),' Gallons',''), 'x', -1) as decimal(10,2)) 
        WHEN m3.meta_value like '%&%' AND (m3.meta_value like '%x%' OR m3.meta_value like '%X%') and SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), '&', -1) like '%x%' 
        THEN CAST(SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), '&', 1),' Gallons',''), 'x', 1) * SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), '&', 1),' Gallons',''), 'x', -1) + SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), '&', -1),' Gallons',''), 'x', 1) * SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), '&', -1),' Gallons',''), 'x', -1) as decimal(10,2)) 
        WHEN m3.meta_value like '%
%' AND (m3.meta_value like '%x%' OR m3.meta_value like '%X%') 
        THEN CAST(SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), '
', 1),' Gallons',''), 'x', 1) * SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), '
', 1),' Gallons',''), 'x', -1) + SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), '
', -1),' Gallons',''), 'x', 1) as decimal(10,2)) 
        WHEN m3.meta_value like '%and%' AND (m3.meta_value like '%x%' OR m3.meta_value like '%X%') 
        THEN CAST(SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), 'and', 1),' Gallons',''), 'x', 1) * SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), 'and', 1),' Gallons',''), 'x', -1) + SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), 'and', -1),' Gallons',''), 'x', 1) * SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), 'and', -1),' Gallons',''), 'x', -1) as decimal(10,2)) 
        WHEN m3.meta_value like '%&%' AND (m3.meta_value like '%x%' OR m3.meta_value like '%X%') 
        THEN CAST(SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), '&', 1),' Gallons',''), 'x', 1) * SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), '&', 1),' Gallons',''), 'x', -1) + SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), '&', -1),' Gallons',''), 'x', 1) as decimal(10,2)) WHEN m3.meta_value like '%X%' 
        THEN CAST(SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), 'x', 1) * SUBSTRING_INDEX(REPLACE(m3.meta_value,' Gallons',''), 'x', -1) as decimal(10,2)) 
        WHEN m3.meta_value like '%/%' OR m3.meta_value IS NULL THEN 0 ELSE CAST(m3.meta_value as decimal(10,2)) END ELSE 0 END as capacity 
from WPPPposts z
INNER JOIN WPPPterm_relationships e ON z.ID = e.object_id
INNER JOIN WPPPterm_taxonomy c ON c.term_taxonomy_id = e.term_taxonomy_id
INNER JOIN WPPPterms d ON c.term_id = d.term_id
INNER JOIN WPPPterm_relationships f ON f.term_taxonomy_id = e.term_taxonomy_id
INNER JOIN WPPPposts a ON f.object_id = a.ID
INNER JOIN WPPPpostmeta m3 ON ( m3.post_id = z.ID )
INNER JOIN WPPPterm_relationships ze ON a.ID = ze.object_id
INNER JOIN WPPPterm_taxonomy zc ON zc.term_taxonomy_id = ze.term_taxonomy_id
INNER JOIN WPPPterms zd ON zc.term_id = zd.term_id

WHERE  zd.slug = 'commercial-trash-and-recycle-bins' AND a.post_status = 'publish' AND m3.meta_key IN ('WPPP_capacity','WPPP_dimensions')  AND z.post_type = 'modelnumbers' AND c.taxonomy = 'modelgroups' AND  a.post_type = 'recyclingbins' AND
 NOT EXISTS
(SELECT * FROM WPPPposts NOTA
  INNER JOIN WPPPpostmeta NOTAA ON (NOTA.ID = NOTAA.post_id)
   WHERE (NOTAA.meta_value = 'accessory' OR NOTAA.meta_value = 'lid') 
   AND NOTAA.meta_key = 'WPPP_accessory' AND NOTA.ID = z.ID) 

SELECT * FROM CapacitySummary

Open in new window

ASKER CERTIFIED SOLUTION

Koen Van Wielink

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mike Tew

ASKER

Thanks Koen.
So far its working. I will work on combining the two statements this afternoon.