Oracle LIKE operator with DECODE

Question

I am new to learning oracle and I am having some problems in understanding the syntax.

INSERT INTO TEMP_BILL_PAY_LWDAY_PL
            WITH tmp_acc AS (
              SELECT ac_id,ac_currency,ac_category,ac_co_code
              FROM account
              WHERE ac_co_code LIKE decode(Currency,'','%','ALL','%',Currency) AND ac_category = '9986'
            )

This is the oracle pl/sql statement, could anybody please tell me what this line means.

WHERE ac_co_code LIKE decode(Currency,'','%','ALL','%',Currency)

I dont understand what decode means here with LIKE. I would really appreciate some help.

Answer 1

It's producing a search pattern on the fly. The logic uses DECODE() to decide:

• If the value Currency is an empty VARCHAR ('') then use % as the pattern for the LIKE. The condition will be equivalent to:

  WHERE ac_co_code LIKE '%'
  

• Else, if the value Currency is 'ALL' then use % as the pattern for the LIKE. The condition will be equivalent to:

  WHERE ac_co_code LIKE '%'
  

• Else use the value of Currency as the pattern for the LIKE. The condition will be equivalent to:

  WHERE ac_co_code LIKE Currency
  

See Oracle Online Docs for details.