CODEWITHSUNDEEP
sqlsql-server
CK Ang
CK Ang

Reputation: 111

Case Statement in SQL Query Issue

I'm trying to run a SQL query but an error happens when I run it.

Error:

[Code: -811, SQL State: 21000]
The result of a scalar fullselect, SELECT INTO statement, or VALUES INTO statement is more than one row..
SQLCODE=-811, SQLSTATE=21000, DRIVER=4.19.49

This is the SQL query that I am trying to run, I believe there is a problem with my CASE statement, I'm running out of solution. Please help, thanks a lot!

SELECT 
    ES.SHPMNT_REF,
    (CASE 
        WHEN (ES.SERVICE_PROVIDER_NAME) IS NULL
           THEN (SELECT BRDB.EXPORT_ONHAND.SERVICE_PROVIDER_NAME 
                 FROM BRDB.EXPORT_ONHAND 
                 WHERE BRDB.EXPORT_ONHAND.SHPMNT_REF = ES.SHPMNT_REF)
           ELSE (ES.SERVICE_PROVIDER_NAME)
     END) AS SP
FROM 
    BRDB.EXPORT_SHIPMENT ES
WHERE 
    ES.DATE_CREATE > CURRENT TIMESTAMP - 30 DAYS

Upvotes: 0

Views: 385

Answers (3)

Red
Red

Reputation: 3267

I think this is what you are after. Joining on the table data you might need, then letting COALESCE check for null and get the other data if it is.

SELECT 
    ES.SHPMNT_REF,
    COALESCE(ES.SERVICE_PROVIDER_NAME, OH.SERVICE_PROVIDER_NAME) AS SP
FROM BRDB.EXPORT_SHIPMENT ES
    LEFT JOIN BRDB.EXPORT_ONHAND AS OH 
        ON ES.SHPMNT_REF = OH.SHPMNT_REF
WHERE 
    ES.DATE_CREATE > CURRENT TIMESTAMP - 30 DAYS

Upvotes: 1

gripep
gripep

Reputation: 379

The error is thrown because your subquery returns multiple values. The best way would be joining the two table first and then get the value you need when the value is NULL. That should work:

SELECT 
    ES.SHPMNT_REF
    ,CASE
       WHEN ES.SERVICE_PROVIDER_NAME IS NULL
         THEN EO.SERVICE_PROVIDER_NAME 
       ELSE ES.SERVICE_PROVIDER_NAME
     END AS 'SP'
FROM BRDB.EXPORT_SHIPMENT ES
LEFT JOIN BRDB.EXPORT_ONHAND EO
ON ES.SHPMNT_REF = EO.SHPMNT_REF

WHERE ES.DATE_CREATE > CURRENT TIMESTAMP - 30 DAYS

Upvotes: 0

sorineatza
sorineatza

Reputation: 106

Maybe you should put inside the case:

THEN (SELECT TOP 1 BRDB.EXPORT_ONHAND.SERVICE_PROVIDER_NAME

Upvotes: 0

Related Questions