CASE statement using a "tronc"?

My goal is to make a Case statement that says when Bill Credit THEN x when Bill Payment then Y but the field Bill Credit/Payment contain also the reference number in it like Bill Credit #123 so I can't use;

Case {type} when 'Bill Credit' then X ELSE 0 END because there the ref number in it.

Si I'd like something like;

Case when {type} "start with" 'Bill Credit' then X Same thing for Bill Payment.

Thanks,

2 answers

  • answered 2018-10-11 19:59 Krypton

    I'm a little confused, because {type} would not contain any reference numbers, but it sounds like the function you're looking for is SUBSTR()

    Here's an example of usage"

    CASE WHEN SUBSTR({field}, 1, 11) = 'Bill Credit' THEN X ELSE 0 END
    

    The first argument is the input string, the second is the starting position, and the third is the length of the sub-string to return.

  • answered 2018-10-11 20:02 Littlefoot

    This is one option you might use:

    SQL> with test (bill) as
      2    (select 'Bill Credit #123' from dual union all
      3     select 'Bill Credit #566' from dual union all
      4     select 'Bill Payment #32' from dual union all
      5     select 'Bill Payment'     from dual
      6    )
      7  select bill,
      8         case when regexp_substr(bill, '\w+', 1, 2) = 'Credit'  then 'X --> credit'
      9              when regexp_substr(bill, '\w+', 1, 2) = 'Payment' then 'Y --> payment'
     10         end result
     11  from test;
    
    BILL             RESULT
    ---------------- -------------------------
    Bill Credit #123 X --> credit
    Bill Credit #566 X --> credit
    Bill Payment #32 Y --> payment
    Bill Payment     Y --> payment
    
    SQL>
    

    Depending on what you really have, this code might need to be modified. If you provide test case, it would be easier to assist.