CODEWITHSUNDEEP
plsqlcasenetsuite
Mathieu Laporte
Mathieu Laporte

Reputation: 13

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,

Upvotes: 0

Views: 38

Answers (2)

Littlefoot
Littlefoot

Reputation: 142705

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.

Upvotes: 0

Krypton
Krypton

Reputation: 5231

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.

Upvotes: 0

Related Questions