Datatype Question

[edwindow]

Hi,
I created a view in my database and I used the decode function to get the information I needed based on a simple condition. However, I do not know how to convert one field to a string data type. I think Oracle by default gave the field a number data type. Numbers will be going into the field; however, I need the data type to be a string.

Here is the Script:
Create view dept_trans as
--Populates the trans_ukey1 field with the correct dept code when value defaults to -
--"HOME"
select trans_id, emp_id, trans_date, activity_id, resource_id, trans_type, correct_id,
code_key, correct_status, period_ending, reason_num, trans_hours, status_date, location,
special_amt, user_field, rate1, rate2, last_update, logid_update, empid_update, dept_code,
salary_code, tw_row, tw_col, pmflag, trans_ukey2, notes, decode(trans_ukey1, 'HOME', rate1, trans_ukey1)
department
from transactions;

I want DEPARTMENT to be string rather than a number. Is there a way to define this field as a string in this select statement.

Thanks

Eddie

 

[jee]

Use the TO_CHAR function as in

...
TO_CHAR (decode(trans_ukey1, 'HOME', rate1, trans_ukey1)) department
...

TO_CHAR has an optional second parameter which is a format string if you want any special handling like forcing leading zeros, etc. This is documented in the SQL manual (available online at the Oracle technology network site).

 

[edwindow]

I see now. Thank you so much.