Total to be displayed at end

Hello Ramco Ramco,

To display the total at the end of your SQL query, you can utilize the UNION ALL operator effectively. The provided SQL code already includes a section to calculate the total, but it needs to be positioned correctly to ensure it appears last in the result set.

SELECT T3."ShortName" AS "Vendor Code",
       (SELECT MAX("CardName") FROM OCRD WHERE "CardCode" = T3."ShortName") AS "Vendor Name",
       T3."Debit", 
       T3."Credit"
FROM "TEST"."OJDT" T2 
INNER JOIN JDT1 T3 ON T2."TransId" = T3."TransId" 
WHERE (T2."TransId" NOT IN (SELECT T0."StornoToTr" FROM OJDT T0 WHERE T0."StornoToTr" IS NOT NULL)) 
AND (T2."StornoToTr" IS NULL)
AND (T3."ShortName" = :Vendor)
AND T2."TaxDate" >= TO_DATE(:FromDate) 
AND T2."TaxDate" <= TO_DATE(:ToDate)

UNION ALL

SELECT T2."CardCode" AS "Vendor Code", 
       T2."CardName" AS "Vendor Name", 
       (T2."DocTotal") + (T2."DpmAmnt") + (T2."DpmVat"), 
       0
FROM "TEST"."OINV" T2
WHERE T2."DocDate" >= TO_DATE(:FromDate) 
AND T2."DocDate" <= TO_DATE(:ToDate)
AND (T2."CardCode" = :Vendor) 
AND T2."CANCELED" NOT IN ('Y', 'C')

UNION ALL

SELECT 'Total' AS "Vendor Code",
       NULL AS "Vendor Name", 
       SUM(T3."Debit"), 
       0
FROM "TEST"."OJDT" T2 
INNER JOIN JDT1 T3 ON T2."TransId" = T3."TransId" 
WHERE (T2."TransId" NOT IN (SELECT T0."StornoToTr" FROM OJDT T0 WHERE T0."StornoToTr" IS NOT NULL)) 
AND (T2."StornoToTr" IS NULL)
AND (T3."ShortName" = :Vendor)
AND T2."TaxDate" >= TO_DATE(:FromDate) 
AND T2."TaxDate" <= TO_DATE(:ToDate);

This structure ensures that the total row is appended at the end of the result set, providing a clear summary of the data.