on 2019 Aug 23 11:27 AM
We have a column in a table that was a computed column (smallint and would either be 1 or 0). We have an event that runs daily and updates data in the required rows. It does not update this column, but the updating of the row would just make it happen. The computed column was a case statement based on another column before it in the table ( case when x like 'xx%' then 1 else 0 end).
I have altered the table, dropped the compute and set the default to 0, and updated all rows to 0, yet the field is still getting set to 1 daily as if the compute is still on it. I have added code to catch the next update, to verify it is the daily event (as nothing else has updated these rows), but has anyone else seen a computed field sticky like this before?
More Info as requested:
The column was a computed column based on a text column before it in the table. Another column further down the line used this column in its compute. We originally removed the compute via sybase central and set the defaukt value to 0. When they came back as 1 the next day, I ran a drop compute. In sybase central, then and now, it says default 0. We have an even that runs every morning to update a date field and kick off processes. When this scheduled even runs, these revert to 1. If I trigger the event, they stay as 0. For a workaround, I have changed the event to also update this field to 0 until I have new requirements for what to do with the field.
example:
CREATE TABLE "MYTABLES"."THISTABLE" ( "ID" char(15) NOT NULL ,"PORTCODE" varchar(30) NULL ,"LEAGUEVALUE" integer NOT NULL ,"ISDOM" smallint NOT NULL COMPUTE (case when ISNULL(PORTCODE,'') like 'us%' then 1 else 0 end) ,"ISSD" integer NOT NULL COMPUTE (case when LEAGUEVALUE between 0 and 5 then 1 when ISDOM > 0 then 1 else 0 end) ,"CHECKDATE" date not null ,CONSTRAINT "PK_THISTABLE" PRIMARY KEY ("ID" ASC) ) go ALTER TABLE MYTABLES.THISTABLE ALTER ISDOM DROP COMPUTE; COMMIT; UPDATE MYTABLES.THISTABLE SET ISDOM=0 WHERE ISDOM=1; COMMIT; -- I then set the default to 0 for this field since it is not on ANY insert or update statements anywhere in our system -- An event runs every morning that calls a proc, this proc sets checkdate to the current date -- When this runs, it sets ISDOM back to 1
Best thing to figure this out is, copy the data in some different named column and drop this column.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
68 | |
8 | |
8 | |
6 | |
6 | |
6 | |
6 | |
6 | |
6 | |
5 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.