CASE function execution query

madsongtel · #1 (**permalink**) 01-02-12, 12:37

Hi All,

Below is my query, here i am not having any record which satisfies LOCATION ='123' in LOCATION_TB table then also my join query is executing and is giving -811 error due to more than 1 row returning when only one is expected, why it is executing like that.
when no row satisfies then it should not eecute Inner query right?
It should return ' ', please let me know how it is working ?

COALESCE(((Case
When SUBSTR(L.LOCATION,1,3) = '123' Then
(Select SUBSTR(LOC_CD,1,3) from POST Where POST_CD = L.POST_TX)
Else ' '
End ) ),( ' ' )) as ADDRESS_CODE from LOCATION_TB L

tonkuma · #2 (**permalink**) 01-02-12, 13:33

Quote:

why it is executing like that.

I'm not sure, but I guessed that result-subquery was evaluated before WHEN condition was evaluated.

To make the query workable, please try to use LEFT OUTER JOIN like in your previous thread
COALESCE Function conversion

tonkuma · #3 (**permalink**) 01-02-12, 13:56

Another simple way is to put WHEN condition into scalar-subquery, like...

Code:

SELECT
       COALESCE(
          (Select SUBSTR(LOC_CD,1,3)
            from  POST
            Where SUBSTR(L.LOCATION,1,3) = '123'
              AND POST_CD = L.POST_TX
          )
        , ' '
       ) as ADDRESS_CODE
 from  LOCATION_TB L

Note: I also added this solution into the thread
COALESCE Function conversion

Internet Services

Web Development

Digital Marketing

Consumer Tech