How to use CTE instead of Cursor

Question

Hi

How to do below task with CTE instead of using Cursors

Declare CURSOR C1 FOR Select T0."CardCode",T0."CardName",T0."U_AREA",T0."U_ROUTE", T0."U_Rider_1",T0."U_Rider_2",T0."U_Rider_3",T0."U_Rider_4" from OCRD T0 inner join OCRG T1 on T0."GroupCode" = T1."GroupCode" ; for cur_row as C1 Do SELECT cur_row."CardCode",cur_row."CardName",cur_row."U_AREA",cur_row."U_ROUTE", T2."DocDate",T2."DocNum", (Select Sum("LineTotal") from INV1 where "DocEntry" = T2."DocEntry") As "Inv Total" ,'' As "Rider", '0' As "Exp Total" FROM OCRD T0 left join OINV T2 on T2."CardCode" = T0."CardCode" where T2."DocDate" >= (:FromDate) and T2."DocDate" <= (:ToDate) and T0."CardCode" = cur_row."CardCode" end for;

Thanks

Amira Bedhiafi · Answer

You're essentially looping through customers with details like CardCode , CardName ... and then for each customer, returning their invoices from OINV in a certain date range, along with the invoice total. WITH CustomerData AS ( SELECT T0."CardCode", T0."CardName", T0."U_AREA", T0."U_ROUTE", T0."U_Rider_1", T0."U_Rider_2", T0."U_Rider_3", T0."U_Rider_4" FROM OCRD T0 INNER JOIN OCRG T1 ON T0."GroupCode" = T1."GroupCode" ), InvoiceTotals AS ( SELECT T2."CardCode", T2."DocDate", T2."DocNum", T2."DocEntry", SUM(INV1."LineTotal") AS "Inv Total" FROM OINV T2 LEFT JOIN INV1 ON INV1."DocEntry" = T2."DocEntry" WHERE T2."DocDate" >= :FromDate AND T2."DocDate"