Hi
Just wondering if advanced queries support the 'with' clause
eg
WITH cte1 AS (SELECT a, b FROM table1),
cte2 AS (SELECT c, d FROM table2)
SELECT b, d FROM cte1 JOIN cte2
WHERE cte1.a = cte2.c;
Hi
Just wondering if advanced queries support the 'with' clause
eg
WITH cte1 AS (SELECT a, b FROM table1),
cte2 AS (SELECT c, d FROM table2)
SELECT b, d FROM cte1 JOIN cte2
WHERE cte1.a = cte2.c;
Hello lisa hepplewhite ,
Thanks for reaching out.
I just tried on a Sugar 14 and for the following example query:
WITH account_data AS ( SELECT id, name, description FROM accounts ), account_custom_data AS ( SELECT id_c, customfield_c FROM accounts_cstm ) SELECT ad.id, ad.name, ad.description, acd.customfield_c FROM account_data AS ad JOIN account_custom_data AS acd ON ad.id = acd.id_c;
The only thing you might need to be mindful of is the underlying MySQL version of you Sugar Instance as I believe that this is only supported on MySQL 8.0 and above.
Let me know if this helps.
Cheers,
André
Thanks André
I know we are only on Sugar 13, but I don't know what version of MySQL we are on. I will check it out. But it is good to know that it will work once we get to sugar14 and MySQL8
Thanks again
Lisa
Hi lisa hepplewhite ,
Great, The version 13 supports both MySQL 5.7 and 8.0.31. So it's good to double check.
Cheers,
André