Error Expection )

Ramco Ramco

Getting error Expection ) at below Select

SELECT
"RowNo",
"Account",
"Debit",
SUM("Debit" - "Credit") OVER (PARTITION BY Account ORDER BY "RowNo") AS Balance
FROM NumberedRows;

WITH NumberedRows AS (
SELECT
ROW_NUMBER() OVER (PARTITION BY Account ORDER BY A."Account") AS "RowNo",
A."Account" AS "Account",(Select "AcctName" from OACT where "AcctCode" = A."Account") AS "G/l Name",
(A."Debit") AS "Debit", (A."Credit") AS "Credit"
From (

SELECT 
T1."Account" As "Account",T1."BPLId",Sum(T1."Debit") As "Debit",Sum(T1."Credit") As "Credit",T1."TransId",T1."TransType",
CASE
WHEN T1."TransType" = '13' THEN (Select max(A0."LocCode") from INV1 A0 where A0."DocEntry" = T1."CreatedBy")
Else  (Select Max("Location") from OWHS where "BPLid" = Max(T1."BPLId")) END AS "Location"


FROM OACT T0
inner Join JDT1 T1 on T0."AcctCode" = T1."Account"
inner join OJDT T2 on T1."TransId" = T2."TransId"
WHERE T2."RefDate" >= '2020/04/01' AND  T2."RefDate" <= '2020/09/30'  
Group By T1."TransId",T1."TransType",T1."Account",T1."BPLId",T1."CreatedBy"
)A

SELECT
    "RowNo",
    "Account",
    "Debit",
    SUM("Debit" - "Credit") OVER (PARTITION BY Account ORDER BY "RowNo") AS Balance
FROM NumberedRows;

Thanks

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jignesh Kumar1y ago

Hello Ramco,

You are missing ending bracket,

WITH NumberedRows AS (
    SELECT
        ROW_NUMBER() OVER (PARTITION BY Account ORDER BY A."Account") AS "RowNo",
        A."Account",
        (SELECT "AcctName" FROM OACT WHERE "AcctCode" = A."Account") AS "G/l Name",
        A."Debit",
        A."Credit"
    FROM (
        SELECT
            T1."Account",
            T1."BPLId",
            SUM(T1."Debit") AS "Debit",
            SUM(T1."Credit") AS "Credit",
            T1."TransId",
            T1."TransType",
            CASE
                WHEN T1."TransType" = '13' THEN 
                    (SELECT MAX(A0."LocCode") FROM INV1 A0 WHERE A0."DocEntry" = T1."CreatedBy")
                ELSE  
                    (SELECT MAX("Location") FROM OWHS WHERE "BPLid" = MAX(T1."BPLId"))
            END AS "Location"
        FROM OACT T0
        INNER JOIN JDT1 T1 ON T0."AcctCode" = T1."Account"
        INNER JOIN OJDT T2 ON T1."TransId" = T2."TransId"
        WHERE T2."RefDate" BETWEEN '2020/04/01' AND '2020/09/30'
        GROUP BY T1."TransId", T1."TransType", T1."Account", T1."BPLId", T1."CreatedBy"
    ) A
)

Sophia Carter1y ago
It seems like you are encountering an issue involving a syntax error in the SQL query provided. The error you are experiencing, denoted as Error Exception ), can be resolved by adjusting the SQL syntax in the SELECT statement.

In the SQL query snippet you shared, the issue lies in the calculation within the SUM function where you subtract "Credit" from "Debit". To rectify this error, you need to ensure that the column names "Debit" and "Credit" are referenced correctly within the query. Here's a corrected version of the problematic section:
```
SELECT
    "RowNo",
    "Account",
    "Debit",
    SUM("Debit" - "Credit") OVER (PARTITION BY "Account" ORDER BY "RowNo") AS Balance
FROM NumberedRows;
```
By enclosing the column names "Debit" and "Credit" within double quotation marks and specifying "Account" in the PARTITION BY clause, you should be able to execute the query without encountering the Error Exception ) issue.

If you make these adjustments in your SQL query, it should help resolve the error you are facing. Feel free to try out the updated query, and if you encounter any further issues or have additional questions, please don't hesitate to ask.
0