Replace Function

[kwil38]

I have a field in a view called 'cr_dr' which contains 'CR' (for credit) and 'DR' (for debit).

I'm attempting to create another view that references the first view and would like it to contain '+' instead of 'CR' and '-' instead of 'DR'. I've tried to do this via the Replace and Translate functions.

The problem is that I have only been able to change one or the other, not both. I this possible to do with Replace or Translate, or is there another function that would be more appropriate?

 

[carp]

How about DECODE?

CREATE VIEW whatever AS
SELECT acct_number, transaction_date, 
       DECODE(the_column,'CR','+','DR','-') my_col, 
       amount
FROM cr_dr;

 

[SantaMufasa]

Here are some test data:

SQL> select cr_dr from kwil38;

CR
--
DR
CR
DR
CR

Here is code to do what you want:

select translate(cr_dr,'DCR','-+') from kwil38;

TR
--
-
+
-
+

The logic behind the code is, "Scan the 'cr_dr' column. If you encounter a 'D', translate it to '-'; if you encounter 'C', translate it to '+', if you encounter 'R' translate it to NULL."

Let us know if this satisfies your need.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[SantaMufasa]

Carp,

Great to see you back, man!

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[kwil38]

Perfect - That's exactly what I need! Thanks Carp!

 

[carp]

Nice to have a reprieve! This conversion is killing me, and I needed the break!

 

[SantaMufasa]

That's exactly what I need! Thanks Carp!

...as such, don't you think Carp deserves a Purple Star ("Thank carp for this valuable post")?

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.