are the CTE available in ax? i'm trying to optimize some queries and given a quick check i've noticed that i can group those in which only one condition is different, but i cant find any informatión about CTE on ax. here and example of what im trying to do
current Queries:
query1:
select sum(qty)
from t1
where "conditions" exists join t2
where t1.Id == t2.Id
&& t2.conditionfield like "CHANGING CONDITION 1*" exists join inventDim
where "More conditions";
query2:
select sum(qty)
from t1
where "conditions" exists join t2
where t1.Id == t2.Id
&& t2.conditionfield like "CHANGING CONDITION 2*" exists join inventDim
where "More conditions";
Using cte:
WITH info
AS
(
SELECT CASE
WHEN t2.conditionfield like 'changing condition 1%' THEN CC1 '
WHEN t2.conditionfield like 'changing condition 2%' THEN 'CC2'
END VARIABLE,SUM(QTY) SumResult
from t1 join t2
on t1.Id = t2.Id join inventDim
on t1Id = t3.Id
where conditions
Group by CASE
WHEN t2.conditionfield like 'changing condition 1%' THEN CC1 '
WHEN t2.conditionfield like 'changing condition 2%' THEN 'CC2'
END
)
so I only make one call to the database with various results instead of many calls with one result.
well all least this was my first thought but as i said, cant find anything about CTE in AX, can this be done?
