| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Can You Convert a Field ?
On Tue, 29 Jun 1999 16:54:29 -0500, Angelica Veron
<Faro_at_globalserve.net> wrote:
>Consider the following 3 fields :
>
>HOUSEHOLD_NUM ACCT_NUM_DISPLAY BRAN
>------------- ----------------
>----
>8427400003321 0000000574724777 2827
>8427400005053 0000000017560888 2827
>8427400005323 0000000500001670 2827
>8427400006144 0000000025590888 2807
>8427400006616 5000575
>2827
>8427400007776 0000000AB0045467 2827
>8427400007776 007800146220002 2827
>8427400009756 007300062620001 2827
>8427400009756 0360756
>2827
>8427400009756 3812853
>2827
>
> These 3 fields are
>
> Name Null? Type
> ------------------------------- -------- ----
> HOUSEHOLD_NUM NOT NULL VARCHAR2(13)
> ACCT_NUM_DISPLAY NOT NULL VARCHAR2(16)
> BRANCH_NUM NOT NULL CHAR(4)
>
>
> I need to combine the total of each 3 given fields as a new field of
>type NUMBER(10), called Service_id . So service_id would be a new 4th
>field (numerical) which has the total of Household_NUM, ACCT_NUM_DISPLAY
>and BRANCH_NUM, how do I do that ??.... how do I create a new field
>called Service_id in the above table ?. Can anyone help ?? I would be
>realy grateful for any help, thank you so much for your time.
To add the column:
alter table TABLE_NAME add (service_id number(10));
Now to update it, there's a question. You say it should be the total of the other three fields, but they're character datatypes. Do you mean to say you want to add the numeric value of the above columns? You will need to convert them on-the-fly to numeric data.
If so, do this:
update TABLE_NAME
set service_id = ( to_number(household_num) +
to_number(acct_num_display) + to_number(branch_num) );
This will bomb if there are any non-numeric characters in those columns.
You might want to consider storing those columns as numeric datatypes, if it really is numeric data. If not, keep it in varchar2 or char datatypes.
To change the datatype, you'll unfortuately have to empty the columns first.
Chris
 |
 |