Custom Query Builder - help needed with MySQL variables

Wappler General How To
#1

I have a query which worked well in Wappler until I tried to add a bit of code to simulate lag function in MySQL 8.0. I basically added these line, and everything worked well in phpMyAdmin.

SET @last_val = '';
...
	if(@last_val  = b.business_id, null, 'Address') as rota_cat,
	   @last_val := b.business_id as temp,
...

Unfortunately when I paste this into the Database Custom Query Builder I get this error message in Wappler

Notification : Parameters do not match Query!

If I remove the few lines mentioned above then the query works, so what is it that I may be doing wrong in those three lines?

And this is the full query

SET @last_val = '';
SELECT 	b.business_id, 
	b.business, 
	b.trading_name, 
	b.is_principal, 
	b.is_bodyshop, 
	b.is_engineer, 
	b.is_misc, 
	b.logo, 
	b.notes, 
	b.rainbow, 
	list_add_typeB.list_code, 
	list_add_typeB.list_ref,

	a.address_id AS rota_id, 

	if(@last_val  = b.business_id, null, 'Address') as rota_cat,
	   @last_val := b.business_id as temp,

	list_add_type.list_label  AS rota_subcat,
	CONCAT_WS(', ',
		NULLIF(a.property,''),
		NULLIF(a.street,''),
		NULLIF(a.locality,''),
		NULLIF(a.post_town,''),
		NULLIF (a.county,''),
		NULLIF(a.postcode,'')
		)  AS rota_data,
	a.address_type AS rota_type


FROM 	tbl_business AS b,
	tbl_address AS a, 
	link_business_address AS b_a,
	tbl_menu_list AS list_add_type,
	tbl_menu_list AS list_add_typeB

WHERE	b_a.address_id = a.address_id 
AND 	b_a.business_id = b.business_id
AND 	list_add_type.menu_list_id = a.address_type
AND	list_add_typeB.menu_list_id = b.rainbow

UNION

SELECT 	b.business_id, 
	b.business, 
	b.trading_name, 
	b.is_principal, 
	b.is_bodyshop, 
	b.is_engineer, 
	b.is_misc, 
	b.logo, 
	b.notes, 
	b.rainbow, 
	list_add_typeB.list_code, 
	list_add_typeB.list_ref,

	c.comms_id AS rota_id, 

	if(@last_val  = b.business_id, null, 'Comms') as rota_cat,
	   @last_val := b.business_id as temp,

	list_add_type.list_label  AS rota_subcat,
	c.comms AS rota_data,
	c.comms_type AS rota_type


FROM 	tbl_business AS b,
	tbl_comms AS c, 
	link_business_comms AS b_c,
	tbl_menu_list AS list_add_type,
	tbl_menu_list AS list_add_typeB
WHERE	b_c.business_id = b.business_id
AND 	c.comms_id = b_c.comms_id
AND 	list_add_type.menu_list_id = c.comms_type
AND	list_add_typeB.menu_list_id = b.rainbow

UNION

SELECT 	b.business_id, 
	b.business, 
	b.trading_name, 
	b.is_principal, 
	b.is_bodyshop, 
	b.is_engineer, 
	b.is_misc, 
	b.logo, 
	b.notes, 
	b.rainbow, 
	list_add_typeB.list_code, 
	list_add_typeB.list_ref,

	p.people_id AS rota_id, 

	if(@last_val  = b.business_id, null, 'Contacts') as rota_cat,
	   @last_val := b.business_id as temp,

	list_add_type.list_label  AS rota_subcat,
	CONCAT_WS(' ',
		NULLIF(p.title,''),
		NULLIF(p.forename,''),
		NULLIF(p.midname,''),
		NULLIF(p.surname,''),
		NULLIF (p.nickname,'')
		)  AS rota_data,
	p.people_type AS rota_type

FROM 	tbl_business AS b,
	tbl_people AS p, 
	link_business_people AS b_p,
	tbl_menu_list AS list_add_type,
	tbl_menu_list AS list_add_typeB
WHERE	b_p.business_id = b.business_id
AND 	p.people_id = b_p.people_id
AND 	list_add_type.menu_list_id = p.people_type
AND	list_add_typeB.menu_list_id = b.rainbow

ORDER BY 	business_id, rota_cat, rota_id
#2

Unfortunately, you cannot run this query in custom query.
@ and : are reserved for parameters… So any custom query involving variables cannot be used here.

You need to use a stored procedure and call that here.

1 Like
#3

OK, thanks for letting me know @sid.
Problem: Never set up Stored Procedure and therefore never called one :frowning_face:

#4

Never as in never ever? Sounds like an opportunity to learn something new. :slightly_smiling_face:

#5

Yep!
Have you got a steer on the best quick reference?

#6

https://dev.mysql.com/doc/refman/8.0/en/create-procedure.html

Official docs. The text in the beginning is a bit overwhelming. You can read through it if you really want to learn SP.
Or just skip to first example code which should be enough for your use case.

1 Like
#7

Cheers. And calling it in Wappler?

#8

Custom query.
With query like:

CALL sp_finally('yes','no','maybe');

This should return you the query result.

Take a look at this too… a bit simpler explanation:

2 Likes
#9

Cheers @sid