MySQL #1582 - Incorrect parameter count in the call to native function 'SUBSTRING_INDEX' - can anyone find the line? 0o

[Greywacke]

hi all,

 

i am attempting to debug the following query, but there is little success.

 

the substring_indexes are set so, due to only one substring that is not always there (accessories).

 

however, i cannot see a substring_index with an invalid parameter count, as this error specifies...

SELECT 	IFNULL(S.text_OnePageData, ";;") AS text_OnePageData, 	PFX2.text_OnePageID AS text_ContactID, 	(PFX1.bigint_LeadID + 11001000) AS bigint_EnquiryID, 	PFX0.text_ConsumerName AS text_EnquiryFullName, 	PFX2.text_Duplicates AS text_EnquiryDuplicates, 	PFX0.text_ConsumerCity AS text_EnquiryCityTown,		R.text_RegionDescription AS text_EnquiryRegion, 	PFX0.text_ConsumerCity AS text_EnquiryZip, 	CONCAT_WS(		' ',		SUBSTRING_INDEX(SUBSTRING_INDEX(PFX1.text_LeadAttributes,'<br />',1),' = ',-1),		SUBSTRING_INDEX(SUBSTRING_INDEX(PFX1.text_LeadAttributes,'<br />',2),' = ',-1)	) AS text_EnquiryMakeModel, 	SUBSTRING_INDEX(SUBSTRING_INDEX(PFX1.text_LeadAttributes,'<br />',3),' = ',-1) AS text_EnquiryYear, 	SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(PFX1.text_LeadAttributes,'<br />',-6),'<br />',1)' = ',-1) AS text_EnquiryRequirement, 	SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(PFX1.text_LeadAttributes,'<br />',-9),'<br />',1),' = ',-1) AS text_EnquiryBudget, 	SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(PFX1.text_LeadAttributes,'<br />',-8),'<br />',1),' = ',-1) AS text_EnquiryFitment, 	IF(		SUBSTRING_INDEX(SUBSTRING_INDEX(PFX1.text_LeadAttributes,'<br />',4),' = ',1)="Accessories",		SUBSTRING_INDEX(SUBSTRING_INDEX(PFX1.text_LeadAttributes,'<br />',4),' = ',-1),		""	) AS text_EnquiryAccessories, 	PFX1.text_LeadMessage AS text_EnquiryComments, 	PFX0.text_ConsumerPhone AS text_EnquiryPhone, 	PFX0.`text_ConsumerE-Mail` AS text_EnquiryEmail, 	PFX1.timestamp_ExpectedBy AS text_EnquiryExpectedFitmentBy FROM performatix.5_suppliers S 	LEFT JOIN performatix.19_consumers PFX0 ON (PFX0.`text_ConsumerE-Mail` = "jaap@performatix.net") 	LEFT JOIN performatix.25_serviceleads PFX1 ON (PFX0.bigint_ConsumerID = PFX1.bigint_ConsumerID) 	LEFT JOIN 27_leadssent PFX2 ON (PFX1.bigint_LeadID = PFX2.bigint_LeadID AND PFX2.bigint_SupplierID = S.bigint_SupplierID) 	LEFT JOIN 1_regions R ON (PFX0.bigint_ConsumerRegion = R.bigint_RegionID) WHERE S.bigint_SupplierID = 192 ORDER BY bigint_EnquiryID DESC LIMIT 1;

if somebody can see what is causing this error #1582 - please let me know on which line it is!

sincerely,

Pierre du Toit.

[Greywacke]

ah i discovered the issue it was in the outer SUBSTRING_INDEX when text_EnquiryRequirement is selected - there is no comma before the delimiter of ' = '!

 

the code has been updated as follows - and now works perfectly!

SELECT 	IFNULL(S.text_OnePageData, ";;") AS text_OnePageData, 	PFX2.text_OnePageID AS text_ContactID, 	(PFX1.bigint_LeadID + 11001000) AS bigint_EnquiryID, 	PFX0.text_ConsumerName AS text_EnquiryFullName, 	PFX2.text_Duplicates AS text_EnquiryDuplicates, 	PFX0.text_ConsumerCity AS text_EnquiryCityTown,		R.text_RegionDescription AS text_EnquiryRegion, 	PFX0.text_ConsumerCity AS text_EnquiryZip, 	CONCAT_WS(		' ',		SUBSTRING_INDEX(SUBSTRING_INDEX(PFX1.text_LeadAttributes,'<br />',1),' = ',-1),		SUBSTRING_INDEX(SUBSTRING_INDEX(PFX1.text_LeadAttributes,'<br />',2),' = ',-1)	) AS text_EnquiryMakeModel, 	SUBSTRING_INDEX(SUBSTRING_INDEX(PFX1.text_LeadAttributes,'<br />',3),' = ',-1) AS text_EnquiryYear, 	SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(PFX1.text_LeadAttributes,'<br />',-5),'<br />',1),' = ',-1) AS text_EnquiryRequirement, 	SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(PFX1.text_LeadAttributes,'<br />',-8),'<br />',1),' = ',-1) AS text_EnquiryBudget, 	SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(PFX1.text_LeadAttributes,'<br />',-7),'<br />',1),' = ',-1) AS text_EnquiryFitment, 	IF(		SUBSTRING_INDEX(SUBSTRING_INDEX(PFX1.text_LeadAttributes,'<br />',4),' = ',1)="Accessories",		SUBSTRING_INDEX(SUBSTRING_INDEX(PFX1.text_LeadAttributes,'<br />',4),' = ',-1),		""	) AS text_EnquiryAccessories, 	PFX1.text_LeadMessage AS text_EnquiryComments, 	PFX0.text_ConsumerPhone AS text_EnquiryPhone, 	PFX0.`text_ConsumerE-Mail` AS text_EnquiryEmail, 	PFX1.timestamp_ExpectedBy AS text_EnquiryExpectedFitmentBy FROM performatix.5_suppliers S 	LEFT JOIN performatix.19_consumers PFX0 ON (PFX0.`text_ConsumerE-Mail` = "jaap@performatix.net") 	LEFT JOIN performatix.25_serviceleads PFX1 ON (PFX0.bigint_ConsumerID = PFX1.bigint_ConsumerID) 	LEFT JOIN 27_leadssent PFX2 ON (PFX1.bigint_LeadID = PFX2.bigint_LeadID AND PFX2.bigint_SupplierID = S.bigint_SupplierID) 	LEFT JOIN 1_regions R ON (PFX0.bigint_ConsumerRegion = R.bigint_RegionID) WHERE S.bigint_SupplierID = 192 ORDER BY bigint_EnquiryID DESC LIMIT 1;

cheers!