I need your assistance with the below query. Could you please assist?
Below are sample records from the Oracle database:
Select distinct b.branch_id,
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%'
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
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.