# Multiple Case Statements Wildcard matches

**URL:** <https://discourse.getdbt.com/t/multiple-case-statements-wildcard-matches/5436>\
**Category:** Help\
**Tags:** jinja, seeds, modeling\
**Created:** [November 29, 2022, 7:49am UTC](https://discourse.getdbt.com/t/multiple-case-statements-wildcard-matches/5436 "2022-11-29T07:49:33Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![derrickt](https://avatars.discourse-cdn.com/v4/letter/d/f19dbf/32.png) [@derrickt](https://discourse.getdbt.com/u/derrickt)\
**Post date:** [November 29, 2022, 7:49am UTC](https://discourse.getdbt.com/t/multiple-case-statements-wildcard-matches/5436/1 "2022-11-29T07:49:33Z")

</div>

Hello!

I’m trying to create a set something similar to the following

```auto
{%- set payment_methods = ['bank_transfer', 'credit_card', 'coupon', 'gift_card'] -%}

with payments as (
   select * from {{ ref('stg_payments') }}
),

final as (
   select
       order_id,
       {% for payment_method in payment_methods -%}

       sum(case when payment_method = '{{ payment_method }}' then amount else 0 end)
            as {{ payment_method }}_amount

       {%- if not loop.last -%}
         ,
       {% endif -%}

       {%- endfor %}
   from {{ ref('stg_payments') }}
   group by 1
)

select * from final

```

However, instead of setting the payment methods to be exact matches, I would like to use Wildcard matches.  
e.g.

```auto
CASE WHEN payment_method LIKE %Amex% THEN "Credit Card" 
        ELSE 
           WHEN payment_method LIKE %Paynow% THEN "Bank Transfer" ELSE "Others" END

```

My current SQL in the dbt model has many lines of case statements and within each case statements many different variations of wildcard matches.

Appreciate some help on improving upon this and making the model cleaner with lesser lines of code and also making it easier to add on more variations of wildcard matches.

Thanks!

---

<div class="post-metadata">

**Author:** ![joellabes](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/joellabes/32/3980_2.png) [@joellabes](https://discourse.getdbt.com/u/joellabes)\
**Post date:** [November 30, 2022, 3:11am UTC](https://discourse.getdbt.com/t/multiple-case-statements-wildcard-matches/5436/2 "2022-11-30T03:11:58Z")

</div>

Hey @derrickt! If I’m understanding you correctly, you have have a lot of different types of credit card (Amex, Visa, Mastercard, etc), and lots of different types of bank transfers (Paynow etc), and want to flatten them down to just credit card, bank transfer, etc?

If so, instead of a case statement you could consider a [seed](https://docs.getdbt.com/docs/build/seeds), a csv file that gets turned into a table in your warehouse.

If you made something like this:  
 ![A table with two columns: payment type and payment group](https://us1.discourse-cdn.com/flex020/uploads/getdbt/original/2X/3/3fc065ad4f7692a3935659cb3158a52bbb0ae389.png)

Then you could do a join against the seed:

```sql
with payment_map as (
  select * from {{ ref('payment_map') }} --use the name of your seed file in the ref
),

payments as (
  select * from {{ ref('payments') }}
),

final as (
select 
  payments.*,  
  coalesce(payment_map.payment_group, "Unknown") as payment_group
from payments
  left outer join payment_map 
    on payments.payment_method ilike '%' + payment_type + '%'
)

```

For more info on why this works, check out [mysql - how to use a like with a join in sql? - Stack Overflow](https://stackoverflow.com/questions/1386166/how-to-use-a-like-with-a-join-in-sql)

Also keep in mind that if you have something like `Amex` and `Amex Black` in your seed file, then you will get a fanout because Amex Black will match against both rows. Remember to add a uniqueness test on your finished model!

---

<div class="post-metadata">

**Author:** ![derrickt](https://avatars.discourse-cdn.com/v4/letter/d/f19dbf/32.png) [@derrickt](https://discourse.getdbt.com/u/derrickt)\
**Post date:** [December 1, 2022, 2:13am UTC](https://discourse.getdbt.com/t/multiple-case-statements-wildcard-matches/5436/3 "2022-12-01T02:13:44Z")

</div>

Hey @joellabes

Thank you for the suggestion!  
Appreciate it!

Yeah I think in this case since I have alot of different types of Payment methods and I would want to group them to be under 3 Categories: Credit Card, Bank Transfer and Online Payments.

A Seed file would be the most appropriate solution for this.

Will try it out and see if it would work!