netraider netraider - 7 months ago 18
SQL Question

Case statement not executing second case

I need your assistance with the below query. Could you please assist?

Below are sample records from the Oracle database:

id:

L7985W6W7W5HJYR5
LJHGTIKFF89EE7HJJU7
LIUH789KEHD7W9JHF
LYUEJDJ8F2NIBRANCH
LOI9DDH7E38BRANCH
L8908HYS6WIBRANCH
PJHS79209KJSJYIEJHSV12


My intention is if
b.branch_id
begins with
L
then it will remove leading zeroes and strip characters but if
b.branch_id
ends with
‘%BRANCH%'
then I would like it to merge with another field but is not working. The first part is working, if it begins with
L
then it works but somehow never executes the
‘%BRANCH%'
part.

Select distinct b.branch_id,
b.branch_date

CASE
WHEN b.branch_id like L%'
THEN TRIM(substr((TO_CHAR(TRIM(LEADING 0 FROM b.branch_id))),-10))
WHEN b.branch_id like ‘%BRANCH%'
THEN substr(b.branch_loc,2,9) || substr(i.branch_name,1,9)
END AS BRANCH_INFO

from tbl_brach b
JOIN tbl_branch_info i
on b.branch_id = i.branch_id_key

where b.branch_id like L%'

Answer

From the documentation:

In a simple CASE expression, Oracle Database searches for the first WHEN ... THEN pair for which expr is equal to comparison_expr and returns return_expr.
...
For a simple CASE expression, the database evaluates each comparison_expr value only before comparing it to expr, rather than evaluating all comparison_expr values before comparing any of them with expr.

You need to arrange the case statement so the most restrictive rule is matched first, otherwise it is never reached:

CASE
  WHEN b.branch_id like ‘L%BRANCH%'
    THEN substr(b.branch_loc,2,9) || substr(i.branch_name,1,9)
  WHEN b.branch_id like L%'
    THEN TRIM(substr((TO_CHAR(TRIM(LEADING '0' FROM b.branch_id))),-10))
END AS BRANCH_INFO

You could also say

  WHEN b.branch_id LIKE 'L%' AND b.branch_id LIKE '%BRANCH%' THEN

but it's simpler to combine into a single LIKE pattern.

Your query also has a where clause that makes the L% in the case expression redundant, but I'm not sure if that is temporary. If it is then you may want an ELSE to handle all values that don't start with L, unless you want them to (implicitly) be null.

As Laurel noted, if you want the value to end with 'BRANCH' then you don't want the final wildcard % sign, as that will also match if there are more characters after BRANCH. You don't have that in your sample data.

Comments