Results 1 to 3 of 3
  1. #1
    Join Date
    May 2011
    Posts
    11

    Unanswered: CASE function execution query

    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

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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
    http://www.dbforums.com/db2/1673112-...onversion.html

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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
    http://www.dbforums.com/db2/1673112-...onversion.html

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •