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
m2ewAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Koen Van WielinkBusiness Intelligence SpecialistCommented:
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.
0
m2ewAuthor Commented:
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.
0
m2ewAuthor Commented:
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Koen Van WielinkBusiness Intelligence SpecialistCommented:
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.
0
m2ewAuthor Commented:
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

0
Koen Van WielinkBusiness Intelligence SpecialistCommented:
I think it's simply a case of missing a semicolon at the end of the create table statement.
I've reformatted your query to make it a bit more readable, and added the semicolons to identify the end of a batch:
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


If you want to do a simple test of your syntax:

CREATE TEMPORARY TABLE CapacitySummary 
(	Cid INT
,	Ccapacity INT);
                                     
INSERT INTO CapacitySummary 
(	Cid
,	Ccapacity)

Select 1, 2;

Drop table CapacitySummary;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
m2ewAuthor Commented:
Thanks Koen.
So far its working. I will work on combining the two statements this afternoon.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.