Topic on Talk:Quarry

SQL functions are not allowed on Quarry.

My query at https://quarry.wmflabs.org/query/48686 keeps failing with Access denied for user 's52788'@'%' to database 'commonswiki_p'. I'm new to SQL, so I might have an error in my code, but two other queries to the same database did work for me.

Edit:

code for reference 

USE commonswiki_p;
CREATE FUNCTION panoramiocatcount(a INT)
RETURNS INT
BEGIN
     RETURN(SELECT COUNT(cl_to)
     FROM categorylinks
     WHERE cl_from = a
     AND cl_to like 'Photos_from_Panoramio_ID_%');
END;

SELECT page_title, panoramiocatcount(page_id)
FROM page
WHERE panoramiocategorynumber(page_id) > 1
AND page_namespace = 6
ORDER BY 2 DESC;

I am pretty sure SQL functions are not allowed.

Hm, that may be the issue. Do you have any suggestions how to make the query without declaring a function?

Something like:

USE commonswiki_p;
SELECT page_title, COUNT(cl_to) AS panoramiocatcount
FROM page
JOIN categorylinks ON cl_from = page_id
WHERE page_namespace = 6
AND cl_to LIKE 'Photos\_from\_Panoramio\_ID\_%'
GROUP BY page_id
HAVING panoramiocatcount > 1;

Thanks (it works)!