Re: Can You Convert a Field ??

create or replace view myview as
select household_num,

           acct_num_display,
           to_number(household_num) +
          to_number(acct_num_display) +
          to_number(branch_num) service_id

However, if you do this then you will have to ensure that the routine which inserts rows must ensure that they are numeric or else you will get error messages when you select from the view.

You also have a basic problem with your data and what you want. If you are combining values that can have a length of 13 and 16 then you can't get a numberic value that is only 10 in length - it just doesn't make any sense.

Your data analyst also doesn't understand the data if the table was designed this way. If a string of numbers is to be treated as a number upon which athmatic operations can be performed, then it should be stored in a numeric field. There are certain numeric strings upon which math is never performed (e.g. SSNs, telephone numbers, etc) and they are rightfully stored as strings. One problem I see with your data is that you could easily duplicate values in the service_id column because there is no apparent control that would prevent this.

Ken

Angelica Veron 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.
Received on Wed Jun 30 1999 - 13:46:50 CEST