Re: Can You convert a Field ??

Angelica Veron schrieb:

> 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.

First of all, you have to add a new column to your table:

alter table <tablename>
add (service_id number(10));

Now you have to update this new column with the needed values.... one or two questions about that
* service_id = houshold_num + acct_num_display + branch_num ???? For each row?

   How shall it be possible to compute a 10-digit number with numbers which have more than 10 digits?
* There exists an ACCT_NUM_DISPLAY with letters in it. Are these numbers hexadecimal or is it just plain text?

How shall service_id be computed?

the best thing would be, you send me an example....

Now suppose we have updated the new column. If the column shall be NOT NULL then you have to do this

alter table <tablename>
modify (service_id not null number(10)); (or is it 'number(10) not null' ??? -- you have to try...)

Now the table is consistent. But if you add new rows to this table and the the column_id has to be computed automatically with the values of the first 3 columns, you have to add a trigger which will do that....or you do it in your application code.

But at first you have to tell me how to compute service_id....

Hope it will help you

Regards, Stephan Born

--
---------------------------------------------------------------
Dipl.-Inf. (FH) Stephan Born   | beusen Consulting GmbH
fon: +49 30 549932-17          | Landsberger Allee 392
fax: +49 30 549932-29          | 12681 Berlin
mailto:stephan.born_at_beusen.de  | Germany
---------------------------------------------------------------