Incorrect Syntax in case Statement

In below code i and getting error Incorrect Syntax . I want if in any record -120 exists .

Case When (Select "staType" from INV4 where "DocEntry" = T0."DocEntry" and "StcCode" = t2."StcCode") In ('-120')  then sum(t2."BaseSum") else sum(t2."BaseSum")/2 End As "BaseSum",


----------------------------------------

select 
t0."BPLName",
t0."DocNum",t0."DocDate",Max(t0."CardCode") As "CardCode",Max(t0."CardName") As "CardName",t2."StcCode",
Case When (Select "staType" from INV4 where "DocEntry" = T0."DocEntry" and "StcCode" = t2."StcCode") In ('-120')  then sum(t2."BaseSum") else sum(t2."BaseSum")/2 End As "BaseSum",

Max(T0."DocTotal"+T0."DpmAmnt") As "Total Amount",
ROW_NUMBER() OVER (PARTITION BY t0."DocEntry" ORDER BY t0."DocNum") AS "RowNum"
from oinv t0 
left join inv4 t2 on t2."DocEntry" = t0."DocEntry"
where t0."DocDate" >= TO_DATE(:FromDate) AND  t0."DocDate" <= TO_DATE(:ToDate) 
group by t0."Series",t0."BPLName",t0."DocEntry",t0."DocNum",t0."DocDate",t2."StcCode",t0."CardCode",t0."ShipToCode"

Thanks

It seems like the SQL code you provided has a syntax issue in the `CASE` statement. In SQL, the `IN` keyword is used to check if a value exists in a list of specified values, so using `IN ('-120')` might be causing the incorrect syntax error.

To address the condition of checking if `-120` exists in the subquery, you can modify the `CASE` statement to handle this condition correctly. Here's a revised version of the `CASE` statement in your SQL query:


Case 
    When EXISTS (Select 1 from INV4 where "DocEntry" = T0."DocEntry" and "StcCode" = t2."StcCode" and "staType" = '-120') 
    then sum(t2."BaseSum") 
    else sum(t2."BaseSum")/2 
End As "BaseSum"

In this adjusted version, we use `EXISTS` to check if there is any record in the subquery where "staType" is equal to `-120`. This modification should help correct the syntax error you were encountering.

Remember, SQL syntax can sometimes be sensitive to the way conditions are structured, so making sure the logic is sound is crucial to avoid errors in your queries. If you implement this adjustment and encounter any further issues or need more guidance, feel free to ask for additional help!

1 Reply

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

Daniel Wright1y ago
Accepted answer
It seems like the SQL code you provided has a syntax issue in the `CASE` statement. In SQL, the `IN` keyword is used to check if a value exists in a list of specified values, so using `IN ('-120')` might be causing the incorrect syntax error.

To address the condition of checking if `-120` exists in the subquery, you can modify the `CASE` statement to handle this condition correctly. Here's a revised version of the `CASE` statement in your SQL query:
```
Case 
    When EXISTS (Select 1 from INV4 where "DocEntry" = T0."DocEntry" and "StcCode" = t2."StcCode" and "staType" = '-120') 
    then sum(t2."BaseSum") 
    else sum(t2."BaseSum")/2 
End As "BaseSum"
```
In this adjusted version, we use `EXISTS` to check if there is any record in the subquery where "staType" is equal to `-120`. This modification should help correct the syntax error you were encountering.

Remember, SQL syntax can sometimes be sensitive to the way conditions are structured, so making sure the logic is sound is crucial to avoid errors in your queries. If you implement this adjustment and encounter any further issues or need more guidance, feel free to ask for additional help!
0