attempting to CASE select in mysql select.

[Greywacke]

hi there,here is my SELECT CASE statement, compiled from the select statement in a previous topic.

SELECT CASE 0 	WHEN 0 THEN 								# Consumer Name		SUBSTRING_INDEX(			text_Consumer, 			";", 			1		) AS text_ConsumerName 	WHEN 1 THEN 								# Product Description		IF(			LOCATE(				"products_description", 				text_LeadAttributes			) = 0		, 			CONCAT_WS(				" - ", 				SUBSTRING_INDEX(					SUBSTRING_INDEX(						SUBSTRING_INDEX(							text_LeadAttributes, 							"<br />", 							6						), 						";", 						-1					), 					" = ", 					-1				), 				SUBSTRING_INDEX(					SUBSTRING_INDEX(						SUBSTRING_INDEX(							text_LeadAttributes, 							"<br />", 							2						), 						";", 						-1					), 					" = ", 					-1				)			)		, 			SUBSTRING_INDEX(				SUBSTRING_INDEX(					text_LeadAttributes, 					"<br />", 					1				), 				" = ", 				-1			)		) AS text_ProductDescription 	END CASE FROM 	6_serviceleads WHERE 	text_Consumer LIKE "%@%" ORDER BY timestamp_LeadCreated DESC LIMIT 1;

i attempted following the official documentation, but i receive the following error when testing in phpMyAdmin. unfortunately this is the first time i've tried using this, but i keep getting the following error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS text_ConsumerName WHEN 1 THEN # Product Description IF( LO' at line 7

i will keep trying and post back here if i get to know the resolution to this issue.

[Greywacke]

ah nm it seems we cannot declare a result AS, within a CASE statement.here is the working query:

SELECT CASE 0 	WHEN 0 THEN 								# Consumer Name		SUBSTRING_INDEX(			text_Consumer, 			";", 			1		) 	WHEN 1 THEN 								# Product Description		IF(			LOCATE(				"products_description", 				text_LeadAttributes			) = 0		, 			CONCAT_WS(				" - ", 				SUBSTRING_INDEX(					SUBSTRING_INDEX(						SUBSTRING_INDEX(							text_LeadAttributes, 							"<br />", 							6						), 						";", 						-1					), 					" = ", 					-1				), 				SUBSTRING_INDEX(					SUBSTRING_INDEX(						SUBSTRING_INDEX(							text_LeadAttributes, 							"<br />", 							2						), 						";", 						-1					), 					" = ", 					-1				)			)		, 			SUBSTRING_INDEX(				SUBSTRING_INDEX(					text_LeadAttributes, 					"<br />", 					1				), 				" = ", 				-1			)		) 	END 	AS text_Result FROM 	6_serviceleads WHERE 	text_Consumer LIKE "%@%" ORDER BY timestamp_LeadCreated DESC LIMIT 1;

this issue is now RESOLVED