Incorrect Syntax near Then

Certainly! The issue in the SQL query you provided lies in the incorrect placement of the `Then` keyword within the `CASE` statement. In SQL, the correct syntax for a `CASE` statement is `CASE WHEN condition THEN result ELSE else_result END`.

Here's the corrected version of your SQL query:


SELECT CASE 
    WHEN (SELECT "GSTIN" FROM CRD1 WHERE "CardCode" = OPCH."CardCode" AND "GSTIN" = '') = 'GA'
    THEN 'GA' 
    ELSE 'Other Value' 
END 
FROM OPCH;

In the corrected query:

- We've moved the condition `(SELECT "GSTIN" FROM CRD1 WHERE "CardCode" = OPCH."CardCode" AND "GSTIN" = '') = 'GA'` within the `WHEN` clause.

- Changed the `THEN 'GA' else 'GA'` to `THEN 'GA' ELSE 'Other Value'`.

This revised query should now operate correctly, checking if the subquery returns 'GA' and then outputting 'GA'; otherwise, it will output 'Other Value'.

If you have any further questions or need more clarification, feel free to ask!

2 Replies

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

Jignesh Kumar1y ago

Accepted answer

Hello Ramco,

You can use this query,

SELECT 
    CASE 
        WHEN EXISTS (
            SELECT 1 
            FROM CRD1 
            WHERE CRD1."CardCode" = OPCH."CardCode" 
              AND CRD1."GSTIN" = ''
        ) 
        THEN 'GA' 
        ELSE 'GA' 
    END AS GSTIN_Status
FROM OPCH;

Sophia Carter1y ago
Certainly! The issue in the SQL query you provided lies in the incorrect placement of the `Then` keyword within the `CASE` statement. In SQL, the correct syntax for a `CASE` statement is `CASE WHEN condition THEN result ELSE else_result END`.

Here's the corrected version of your SQL query:
```
SELECT CASE 
    WHEN (SELECT "GSTIN" FROM CRD1 WHERE "CardCode" = OPCH."CardCode" AND "GSTIN" = '') = 'GA'
    THEN 'GA' 
    ELSE 'Other Value' 
END 
FROM OPCH;
```
In the corrected query:

- We've moved the condition `(SELECT "GSTIN" FROM CRD1 WHERE "CardCode" = OPCH."CardCode" AND "GSTIN" = '') = 'GA'` within the `WHEN` clause.

- Changed the `THEN 'GA' else 'GA'` to `THEN 'GA' ELSE 'Other Value'`.

This revised query should now operate correctly, checking if the subquery returns 'GA' and then outputting 'GA'; otherwise, it will output 'Other Value'.

If you have any further questions or need more clarification, feel free to ask!
0