decode in WHERE 1

[paljnad]

I have a query that joins two tables based on column A.


select * from
table a, table b
where table a.column a = table b.column a


Column A in table A has a value of 'SEP A'
Column A in table B has a value of 'SEP_A'

How do I put a decode in the where statement so that it does a correct match. I want the query to know that SEP A in table A is the same as SEP_A in table B.

 

[SantaMufasa]

Paljnad,

You didn't specify whether you wanted all "_" characters in Table_B to be treated as blanks, or whether you only want Table_B's "SEP_A" values to be treated as "SEP A".

Regardless, there are several ways to achieve the result you want:

SQL> select * from table_a;

        ID COLUMN_A
---------- ----------
         1 SEP A
         2 SEP B
         3 XXX

3 rows selected.

SQL> select * from table_b;

        ID COLUMN_A
---------- ----------
         1 SEP_A
         2 SEPB
         3 YYY

3 rows selected.

select *
  from table_a a, table_b b
 where a.column_a = translate(b.column_a,'_',' ');

        ID COLUMN_A           ID COLUMN_A
---------- ---------- ---------- --------
         1 SEP A               1 SEP_A

1 row selected.

select *
  from table_a a, table_b b
 where a.column_a = replace(b.column_a,'SEP_A','SEP A');

        ID COLUMN_A           ID COLUMN_A
---------- ---------- ---------- ----------
         1 SEP A               1 SEP_A

1 row selected.

select *
  from table_a a, table_b b
 where a.column_a = case when b.column_a = 'SEP_A' then 'SEP A' else b.column_a end
/

        ID COLUMN_A           ID COLUMN_A
---------- ---------- ---------- ----------
         1 SEP A               1 SEP_A

1 row selected.

select *
  from table_a a, table_b b
 where a.column_a = decode(b.column_a,'SEP_A','SEP A',b.column_a);

        ID COLUMN_A           ID COLUMN_A
---------- ---------- ---------- ----------
         1 SEP A               1 SEP_A

1 row selected.

Let us know if any of these choices satisfactorily resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[paljnad]

Thanks, Santa. I used the decode and it worked like a charm !