Length change

[edwindow]

I am using the decode function in a view. I want to know if it is possible to set a field length for the new field which I am putting values.

Sample code:
create view x as
select x,x,x,
to_char(decode(trans_ukey1, 'HOME', rate1, trans_ukey1))
department
from x

By default it looks like department is getting a field length of 40.

I want to make department have a field length of 255 char.

Is there a way to define this in the view?

Eddie

 

[gansen]

select x,x,x,
to_char(decode(trans_ukey1, 'HOME', rate1, trans_ukey1))
department
from x

as per my knowledge the rate1 column or trans_ukey1 conlumn
must have a value of lengrh 40, thats why the col is created with 40.

Try inserting a value of 233 length in rate1 column or trans_ukey1 column and create the view

Now you can see the department column length as 233

 

[CRoberts]

Not really sure as to what you are doing. Your TO_CHAR is superfluous, Trans_Ukey1 is a character already, so Rate1 must also be a character. You can not insert into a view that has a function unless you have an Instead Of Trigger. Are you wanting to force a column's display to be a certain length? By default a function will normally return with a column width of 80. To force Sqlplus to display other than this default value, use the Column command.

COLUMN Department FORMAT A255

If the view is being used by other applications, then use the LPAD or RPAD function to pad the returned value so as to force the column into the appropriate size. Be careful here, all returned values (other than a NULL value) are always 255 in length because of the appended spaces. This is bad because this padded value is not what you want to insert/update to another table with unless a RTRIM or LTRIM is applied first.

RPAD(decode(trans_ukey1, 'HOME', rate1, trans_ukey1), 255) Department