I'm kind of new to this, so maybe I'm asking something for which I'm not ready yet. I'm also not completely familiar with all the terminology, but what I'm looking for is to like set a subquery as a variable.
The purpose is of course to not have to repeat and recalculate a subquery more than once.
I want to set this outcome:
SET bedrag = (SELECT SUM(cot.amt_after_entry) FROM Prd_StgArea_Exact.fin.crdout cot LEFT JOIN Prd_StgArea_Exact.fin.payout pay ON cot.entry_nr=pay.entry_nr AND cot.adm_nr=pay.adm_nr WHERE (cot.creditor = out.creditor AND cot.adm_nr = out.adm_nr) AND pay.entry_nr is null);
And call it in my query:
SELECT DISTINCT out.adm_nr AS Administratie, out.creditor AS Crediteurnummer, crd.name AS Crediteur, @bedrag AS Bedrag
FROM Prd_StgArea_Exact.fin.crdout out LEFT JOIN Prd_StgArea_Exact.fin.credit crd ON out.creditor=crd.creditor AND out.adm_nr=crd.adm_nr
