asked on

Populating a generated column that has been added

I had a DB2 table, mytable, with a field named flr that was defined as char(9). A column was added with a alter table mytable add column flr_num generated always as cast(flr as decimal(5,0). After the column was added, I would have expected to see values in the flr_num column but all the values are null. How does that column get populated or is the problem that the column is decimal(5,0) while the source is char(9)?

ASKER CERTIFIED SOLUTION

Member_2_2484401

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

SOLUTION

Member_2_276102

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

awking00

ASKER

Okay, I think the flr_num column showed null because the conversion to decimal(5,0) would not be performed. So the issue now is to modify the column to the correct datatype. Incidentally, I am primarily an Oracle person trying to help someone out with this DB2 issue. We tried issuing the command ALTER TABLE mytable ALTER COLUMN flr_num GENERATED ALWAYS AS CAST(flr AS INTEGER) but I also see in going through the DB2 syntax that a SET keyword may be required. If so, I'm not sure where, either ALTER COLUMN SET flr_num GENERATED ALWAYS AS CAST(flr AS INTEGER) or ALTER COLUMN flr_num SET GENERATED ALWAYS AS CAST(flr AS INTEGER). Can any one of these work? Also, it seems that we have run into issues where DB2 tells us we have an illegal use of CAST. Are there any other means of converting a datatype?

SOLUTION

Tomas Helgi Johannsson

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

awking00

ASKER

This problem was actually posed back to the party I was trying to help and they seem to have found a solution. Thanks for you input.