Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Problem with materialized computed columns not being updated after inserts/updates

6 posts in General Discussion Last posting was on 2011-09-01 15:29:30.0Z
Bill Showler Posted on 2011-08-29 20:03:51.0Z
Sender: 3e4d.4e5bd45b.1804289383@sybase.com
From: Bill Showler
Newsgroups: sybase.public.ase.general
Subject: Problem with materialized computed columns not being updated after inserts/updates
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4e5bf0a7.4266.1681692777@sybase.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="-=_forums-1-dub4e5bf0a7"
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 29 Aug 2011 13:03:51 -0700
X-Trace: forums-1-dub 1314648231 10.22.241.41 (29 Aug 2011 13:03:51 -0700)
X-Original-Trace: 29 Aug 2011 13:03:51 -0700, 10.22.241.41
Lines: 125
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30506
Article PK: 72685

My company is trying to make use of UDFs and materialized
columns for the first time. We found not making a computed
column "materialized" caused a significant performance hit
so we went with materialized computed columns. However, we
found the materialized column itself is not set upon INSERT
(left as NULL) or was not corrected if an update occurred
that should have affected it. Our specific example is from
the TV industry and is quite simple. We need to track
whether a system (which supplies our TV signal to consumers)
is inside or outside the province of Quebec ... or both. You
can supply both (or mixed) if you are a satellite signal
provider otherwise if say cable, you are either inside or
outside Quebec based on your location. So the logic of the
materialized computed column "inside_quebec_yes_no_mixed"
(which resides in the system table) is that it checks the
system table's technology code and if satellite, then this
computed column gets "mixed". Otherwise, the function
examines the parent table of a system, which is named
business, and checks its province code to see if it is
quebec or not. We also have a 2nd UDF and a 2nd
corresponding computed column which operates at another
level (contract has many systems) and it assess whether or
not all of the systems associated with the contract are all
inside or outside or mixed as well. When we created the
materialized columns and UDFs, everything evaluated
perfectly; however, subsequent inserts and updates were not
reflected in the materilized computed columns. how can we
force it to be updated? Is it a problem to have logic that
is dependent upon data in a separate table? Most computed
column examples that we saw were very simple and were from
the same table. We could abandon the materialized setting
bvut the oursearches are too slow. Thanks for taking the
time to review our issue!


"Mark A. Parsons" <iron_horse Posted on 2011-08-29 20:25:39.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.13) Gecko/20101207 Lightning/1.0b2 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Problem with materialized computed columns not being updated after inserts/updates
References: <4e5bf0a7.4266.1681692777@sybase.com>
In-Reply-To: <4e5bf0a7.4266.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4e5bf5c3$1@forums-1-dub>
Date: 29 Aug 2011 13:25:39 -0700
X-Trace: forums-1-dub 1314649539 10.22.241.152 (29 Aug 2011 13:25:39 -0700)
X-Original-Trace: 29 Aug 2011 13:25:39 -0700, vip152.sybase.com
Lines: 52
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30507
Article PK: 72686

I'd suggest you post the following:

- 'create table' command (so we can see the actual computed column definition)

- more detail on what you mean by 'everything evaluated perfectly' (was this a test call of the UDF that 'evaluated
perfectly', or did you actually perform inserts/updates and a computed column was updated correctly?)

- you mention that the materialized column was not set upon INSERT *or* was not corrected if an update occurred that
should have affected it (are you saying that sometimes the INSERT succeeds (at populating the computed columns) but the
follow-on UPDATE fails?)

- an example INSERT statement and the results from a follow-on SELECT of that same record

The main objective here is to provide a reproducible example that we can see/analyze/test.

I'm assuming from your comments that you have (at least) 2 computed columns that are not working properly. I'd suggest,
at least initially, you post details for just one issue (ie, let's focus on a single issue for the moment).

On 08/29/2011 16:03, Bill Showler wrote:
> My company is trying to make use of UDFs and materialized
> columns for the first time. We found not making a computed
> column "materialized" caused a significant performance hit
> so we went with materialized computed columns. However, we
> found the materialized column itself is not set upon INSERT
> (left as NULL) or was not corrected if an update occurred
> that should have affected it. Our specific example is from
> the TV industry and is quite simple. We need to track
> whether a system (which supplies our TV signal to consumers)
> is inside or outside the province of Quebec ... or both. You
> can supply both (or mixed) if you are a satellite signal
> provider otherwise if say cable, you are either inside or
> outside Quebec based on your location. So the logic of the
> materialized computed column "inside_quebec_yes_no_mixed"
> (which resides in the system table) is that it checks the
> system table's technology code and if satellite, then this
> computed column gets "mixed". Otherwise, the function
> examines the parent table of a system, which is named
> business, and checks its province code to see if it is
> quebec or not. We also have a 2nd UDF and a 2nd
> corresponding computed column which operates at another
> level (contract has many systems) and it assess whether or
> not all of the systems associated with the contract are all
> inside or outside or mixed as well. When we created the
> materialized columns and UDFs, everything evaluated
> perfectly; however, subsequent inserts and updates were not
> reflected in the materilized computed columns. how can we
> force it to be updated? Is it a problem to have logic that
> is dependent upon data in a separate table? Most computed
> column examples that we saw were very simple and were from
> the same table. We could abandon the materialized setting
> bvut the oursearches are too slow. Thanks for taking the
> time to review our issue!


Bill Showler Posted on 2011-08-30 13:08:29.0Z
Sender: 3e4d.4e5bd45b.1804289383@sybase.com
From: Bill Showler
Newsgroups: sybase.public.ase.general
Subject: Re: Problem with materialized computed columns not being updated after inserts/updates
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4e5ce0cd.2535.1681692777@sybase.com>
References: <4e5bf5c3$1@forums-1-dub>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="-=_forums-1-dub4e5ce0cd"
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 30 Aug 2011 06:08:29 -0700
X-Trace: forums-1-dub 1314709709 10.22.241.41 (30 Aug 2011 06:08:29 -0700)
X-Original-Trace: 30 Aug 2011 06:08:29 -0700, 10.22.241.41
Lines: 9687
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30508
Article PK: 72694


> I'd suggest you post the following:
>
> - 'create table' command (so we can see the actual
> computed column definition)
>
> - more detail on what you mean by 'everything evaluated
> perfectly' (was this a test call of the UDF that
> 'evaluated perfectly', or did you actually perform
> inserts/updates and a computed column was updated
> correctly?)
>
> - you mention that the materialized column was not set
> upon INSERT *or* was not corrected if an update occurred
> that should have affected it (are you saying that
> sometimes the INSERT succeeds (at populating the computed
> columns) but the follow-on UPDATE fails?)
>
> - an example INSERT statement and the results from a
> follow-on SELECT of that same record
>
> The main objective here is to provide a reproducible
> example that we can see/analyze/test.
>
> I'm assuming from your comments that you have (at least) 2
> computed columns that are not working properly. I'd
> suggest, at least initially, you post details for just
> one issue (ie, let's focus on a single issue for the
> moment).
>
> On 08/29/2011 16:03, Bill Showler wrote:
> > My company is trying to make use of UDFs and
> > materialized columns for the first time. We found not
> > making a computed column "materialized" caused a
> > significant performance hit so we went with materialized
> > computed columns. However, we found the materialized
> > column itself is not set upon INSERT (left as NULL) or
> > was not corrected if an update occurred that should have
> > affected it. Our specific example is from the TV
> > industry and is quite simple. We need to track whether a
> > system (which supplies our TV signal to consumers) is
> > inside or outside the province of Quebec ... or both.
> You can supply both (or mixed) if you are a satellite
> > signal provider otherwise if say cable, you are either
> > inside or outside Quebec based on your location. So the
> > logic of the materialized computed column
> > "inside_quebec_yes_no_mixed" (which resides in the
> > system table) is that it checks the system table's
> > technology code and if satellite, then this computed
> > column gets "mixed". Otherwise, the function examines
> > the parent table of a system, which is named business,
> > and checks its province code to see if it is quebec or
> > not. We also have a 2nd UDF and a 2nd corresponding
> > computed column which operates at another level
> > (contract has many systems) and it assess whether or not
> > all of the systems associated with the contract are all
> > inside or outside or mixed as well. When we created the
> > materialized columns and UDFs, everything evaluated
> perfectly; however, subsequent inserts and updates were
> > not reflected in the materilized computed columns. how
> > can we force it to be updated? Is it a problem to have
> > logic that is dependent upon data in a separate table?
> > Most computed column examples that we saw were very
> > simple and were from the same table. We could abandon
> > the materialized setting bvut the oursearches are too
> > slow. Thanks for taking the time to review our issue!

create table System (
SystemID numeric(12,0)
not null ,
ContractID numeric(12,0)
null ,
RankNbr int
DEFAULT 0
not null ,
SystemTypeCode numeric(12,0)
null ,
RegionCode numeric(12,0)
null ,
CRTCDecisionNumber varchar(10)
DEFAULT ' '
null ,
CRTCClassification char(3)
DEFAULT ' '
null ,
PostalCodeLength NoYes
DEFAULT ' '
null ,
BuyingGroup NoYes
DEFAULT ' '
null ,
ProvincialCableGroup NoYes
DEFAULT ' '
null ,
AnglophonePct numeric(4,1)
null ,
FrancophonePct numeric(4,1)
null ,
PotentialSubs int
null ,
BasicSubs int
null ,
AsOnDate datetime
null ,
TechnologyCode numeric(12,0)
not null ,
InsertedUser InsUser
not null ,
InsertedDate InsDate
DEFAULT GetDate()
not null ,
LastUpdUser LastUpdUser
not null ,
LastUpdDate LastUpdDate
DEFAULT GetDate()
not null ,
TimeStamp timestamp
null ,
AreaServed varchar(1024)
null ,
DesignatedMarket char(1)
null ,
CRTCLicenseExempt datetime
null ,
CRTCLicenseExemptEnd datetime
null ,
RegionalLicense char(1)
DEFAULT 'N'
not null CONSTRAINT RegionalLicense_YN CHECK
(RegionalLicense in ("Y", "N"))
,
Alldigitalflag char(1)
DEFAULT 'N'
not null ,
Alldigitaleffectivedate datetime
null ,
inside_quebec_yes_no_mixed AS
dbo.f_inside_quebec_yes_no_mixed(systemid) MATERIALIZED
)

function syntax was already included in an attachment but
it's short so ... here it is:
create function dbo.f_inside_quebec_yes_no_mixed(@SystemID
numeric(12,0))
returns char(5)
as
-- created by Bill Showler Aug 2011
-- this function will return a CHAR(5) value of Yes or No or
Mixed for the SystemID passed to the parameter @SystemID
-- it will return null for the following conditions:
-- the value passed to any parameter is null
-- @SystemID does not exist in System.SystemID

declare @inside_quebec_yes_no_mixed char(5)

if @SystemID is null return null

select @inside_quebec_yes_no_mixed = case TechnologyCode
when 201 then 'Mixed' else (case PrincipalStateCode when 209
then 'Yes' else 'No' end) end
from business b, system s
where b.businessid = s.systemid
and s.systemid = @SystemID

return @inside_quebec_yes_no_mixed
go

and the parent table above this System table (Yes, I can't
imagine who would choose a table name that sooo looks like a
reserved word! :)) is the business table which is where you
find the province code which determines the computed
column's materialized value if Quebec and the
system.technologycode is not satellite or 201. The
business.PrincipalStateCode for Quebec is 209.

create table Business (
BusinessID numeric(12,0)
identity ,
Alias char(50)
DEFAULT ' '
not null ,
DMAID int
null ,
BusinessTypeCode numeric(12,0)
not null ,
BusinessSubTypeCode int
null ,
BusinessName NameLong
DEFAULT ' '
not null ,
WebAddress char(30)
DEFAULT ' '
not null ,
SystemNum char(6)
DEFAULT ' '
not null ,
DisplaySeqNbr int
DEFAULT 0
not null ,
AffiliateNum char(10)
DEFAULT ' '
not null ,
PrincipalStateCode numeric(12,0)
null ,
PrincipalCommunity char(30)
DEFAULT ' '
not null ,
PrincipalCounty char(30)
DEFAULT ' '
not null ,
PrincipalCountryCode numeric(12,0)
null ,
StateCountyCd char(5)
DEFAULT ' '
not null ,
InsertedUser InsUser
not null ,
InsertedDate InsDate
DEFAULT GetDate()
not null ,
LastUpdUser LastUpdUser
not null ,
LastUpdDate LastUpdDate
DEFAULT GetDate()
not null ,
TimeStamp timestamp
null ,
GSTExemptionNo char(15)
null ,
PSTExemptionNo char(15)
null ,
BuyingGroup char(1)
DEFAULT ' '
null ,
ProvincialCableGroup char(1)
DEFAULT ' '
null ,
OwnerInfo text
null ,
PrimaryPhoneID numeric(12,0)
null ,
PrimaryFaxID numeric(12,0)
null ,
Inactive char(1)
null ,
CCSA AS
case
when businesstypecode != 175 then
case
when BuyingGroup = 'S' then 'Y'
else 'N'
end
else dbo.f_CCSA_FCCQ(BusinessID, 'C', getdate())
end ,
FCCQ AS
case
when businesstypecode != 175 then
case
when ProvincialCableGroup = 'A' then 'Y'
else 'N'
end
else dbo.f_CCSA_FCCQ(BusinessID, 'F', getdate())
end
)

I attached a couple of JPGs to show our app results. The
GUI uses a wizard to create a new busines of sub-type SYSTEM
as a hard coded tech code 202 = Cable and subsequently I
updated it to Tech code 201 which is DTH or satellite so i
now understand now that our app didn't gen the right INSERT
to set the column appropriately but the subsequent UPDATE
set = 201 should have influenced the materialized column and
set it to 'Mixed'.

What i meant by 'everything evaluated perfectly' is that
when we generated the materilized column via Sybase Central
GUI which would have genned an ALTER Table add computed
materialized column syntax ... the value of the column was
calculated by the UDF exactly as we had hoped/expected which
was set out in the f_inside_quebec_yes_no_mixed(systemid)
function.

Thanks again for your attention to detail and time to review
our issue.

INSERT INTO Business ( BusinessName, Alias, BusinessTypeCode, PrincipalStateCode, PrincipalCommunity, PrincipalCountryCode ) VALUES ( 'ex satellite co should be MIXED for inside QC', 'ASATELLITE CO', 175, 206, 'MIXED: INSIDE AND OUTSIDE QC', 189 )

INSERT INTO System ( SystemID, TechnologyCode ) VALUES ( 24280401, 202 )

UPDATE System Set crtcclassification = 'DTH', systemtypecode = 20000268, DesignatedMarket = 'M', AreaServed = 'this company should be evaluated as both since it will use a tech code of DTH (Direct To Home = Satellite) even though it resides in Ontario or outside QC' WHERE systemid = 24280401

UPDATE System SET CRTCClassification = 'DTH', DesignatedMarket = 'M', TechnologyCode = 201, AllDigitalFlag = 'Y', Alldigitaleffectivedate = '10-1-2011 0:0:0.000' WHERE SystemID = 24280401

UPDATE Business SET PrincipalStateCode = 209 WHERE BusinessID = 24280401

ex of materialized column remaining NULL.jpg
ex of updating province and mat column still not set.jpg

"Mark A. Parsons" <iron_horse Posted on 2011-08-30 19:10:49.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.13) Gecko/20101207 Lightning/1.0b2 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Problem with materialized computed columns not being updated after inserts/updates
References: <4e5bf5c3$1@forums-1-dub> <4e5ce0cd.2535.1681692777@sybase.com>
In-Reply-To: <4e5ce0cd.2535.1681692777@sybase.com>
Content-Type: multipart/mixed; boundary="------------070203060107060902080507"
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4e5d35b9$1@forums-1-dub>
Date: 30 Aug 2011 12:10:49 -0700
X-Trace: forums-1-dub 1314731449 10.22.241.152 (30 Aug 2011 12:10:49 -0700)
X-Original-Trace: 30 Aug 2011 12:10:49 -0700, vip152.sybase.com
Lines: 408
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30509
Article PK: 72688

Still too confusing with 2 different table definitions, pictures that have no clear mapping between displayed fields and
columns in which of the 2 tables, INSERT/UPDATE statements with no list of the results (or no correlation with fields in
the pictures) ...

So let's make this a little easier ...

You've provided the definition of the System table, including the computed column you're having problems with (ie,
System.inside_quebec_yes_no_mixed).

Let's look solely at queries affecting the System table.

Let's take the front-end application out of the picture and deal solely with the T-SQL commands being issued to the
dataserver; to do this we'll use isql for all of your T-SQL commands.

Please post the complete output from running the following in an isql session:

=================================
set nocount off
go

print 'delete'
delete System
where SystemID = 24280401
go

print 'insert'
INSERT INTO System ( SystemID, TechnologyCode ) VALUES ( 24280401, 202 )
go

print 'select #1'
select SystemID, inside_quebec_yes_no_mixed
from System
where SystemID = 24280401
go

print 'update #1'
UPDATE System Set crtcclassification = 'DTH', systemtypecode = 20000268, DesignatedMarket = 'M', AreaServed = 'this
company should be evaluated as both since it will use a tech code of DTH (Direct To Home = Satellite) even though it
resides in Ontario or outside QC' WHERE systemid = 24280401
go

print 'select #2'
select SystemID, inside_quebec_yes_no_mixed
from System
where SystemID = 24280401
go

print 'update #2'
UPDATE System SET CRTCClassification = 'DTH', DesignatedMarket = 'M', TechnologyCode = 201, AllDigitalFlag = 'Y',
Alldigitaleffectivedate = '10-1-2011 0:0:0.000' WHERE SystemID = 24280401
go

print 'select #3'
select SystemID, inside_quebec_yes_no_mixed
from System
where SystemID = 24280401
go
=================================

NOTE: If you've got RI installed and you can't perform these specific DELETE/INSERT/UPDATE commands, then replace the
values with something that will work. The primary issue here is to see the actual INSERT/UPDATE commands and the
results of the follow-on SELECTs.

For each of the 3 SELECT statements, tell us if the SELECT displays the expected value for the
inside_quebec_yes_no_mixed column ... or if the displayed value is wrong, tell us what you expect the value to be.

-------------------

In the meantime, I can tell you that computed column definitions will only fire when the referenced column(s) value changes.

In your case the computed column for System.inside_quebec_yes_no_mixed is based solely on the SystemID column, so in
order for the computed column to function as expected, you have to modify the SystemID column value. In other words,
the computed column will only first for an INSERT (when SystemID is populated), or during an UPDATE that sets/modifies
the SystemID column.

Since you're not performing a 'set' against the SystemID column in either of your UPDATEs, the computed column
definition will not fire and thus the inside_quebec_yes_no_mixed column will not be changed.

What you'll need to do is redefine the computed column definition to reference one of the columns (or a group of
columns) that you're updating.

There are a handful of ways you can redefine your computed column to fire more often, eg,

- include a rather lengthy, possibly convoluted expression that references all desired columns

- if using ASE 15.x consider using the hashbytes() function to reference all desired columns (still a bit convoluted,
and requires a column redefinition each time a column is added/dropped)

- add a timestamp column to the table (timestamp columns are automatically updated anytime any other column in the table
changes), then have your computed column definition reference the timestamp column

- ... there probably other ways ... you're only limited by your imagination ...

I've attached a copy of one of my recent posts to the ISUG forums showing how to use a timestamp column to keep the
last-user/mod-date computed columns up to date.

NOTE: I'm not saying you have to use this same exact design, just providing an example that shows how to insure the
computed column definition fires each time any of the columns in my sample table are modified.

> create table System (
> SystemID numeric(12,0)
> not null ,
> ContractID numeric(12,0)
> null ,
> RankNbr int
> DEFAULT 0
> not null ,
> SystemTypeCode numeric(12,0)
> null ,
> RegionCode numeric(12,0)
> null ,
> CRTCDecisionNumber varchar(10)
> DEFAULT ' '
> null ,
> CRTCClassification char(3)
> DEFAULT ' '
> null ,
> PostalCodeLength NoYes
> DEFAULT ' '
> null ,
> BuyingGroup NoYes
> DEFAULT ' '
> null ,
> ProvincialCableGroup NoYes
> DEFAULT ' '
> null ,
> AnglophonePct numeric(4,1)
> null ,
> FrancophonePct numeric(4,1)
> null ,
> PotentialSubs int
> null ,
> BasicSubs int
> null ,
> AsOnDate datetime
> null ,
> TechnologyCode numeric(12,0)
> not null ,
> InsertedUser InsUser
> not null ,
> InsertedDate InsDate
> DEFAULT GetDate()
> not null ,
> LastUpdUser LastUpdUser
> not null ,
> LastUpdDate LastUpdDate
> DEFAULT GetDate()
> not null ,
> TimeStamp timestamp
> null ,
> AreaServed varchar(1024)
> null ,
> DesignatedMarket char(1)
> null ,
> CRTCLicenseExempt datetime
> null ,
> CRTCLicenseExemptEnd datetime
> null ,
> RegionalLicense char(1)
> DEFAULT 'N'
> not null CONSTRAINT RegionalLicense_YN CHECK
> (RegionalLicense in ("Y", "N"))
> ,
> Alldigitalflag char(1)
> DEFAULT 'N'
> not null ,
> Alldigitaleffectivedate datetime
> null ,
> inside_quebec_yes_no_mixed AS
> dbo.f_inside_quebec_yes_no_mixed(systemid) MATERIALIZED
> )
>
> function syntax was already included in an attachment but
> it's short so ... here it is:
> create function dbo.f_inside_quebec_yes_no_mixed(@SystemID
> numeric(12,0))
> returns char(5)
> as
> -- created by Bill Showler Aug 2011
> -- this function will return a CHAR(5) value of Yes or No or
> Mixed for the SystemID passed to the parameter @SystemID
> -- it will return null for the following conditions:
> -- the value passed to any parameter is null
> -- @SystemID does not exist in System.SystemID
>
> declare @inside_quebec_yes_no_mixed char(5)
>
> if @SystemID is null return null
>
> select @inside_quebec_yes_no_mixed = case TechnologyCode
> when 201 then 'Mixed' else (case PrincipalStateCode when 209
> then 'Yes' else 'No' end) end
> from business b, system s
> where b.businessid = s.systemid
> and s.systemid = @SystemID
>
> return @inside_quebec_yes_no_mixed
> go
>
> and the parent table above this System table (Yes, I can't
> imagine who would choose a table name that sooo looks like a
> reserved word! :)) is the business table which is where you
> find the province code which determines the computed
> column's materialized value if Quebec and the
> system.technologycode is not satellite or 201. The
> business.PrincipalStateCode for Quebec is 209.
>
> create table Business (
> BusinessID numeric(12,0)
> identity ,
> Alias char(50)
> DEFAULT ' '
> not null ,
> DMAID int
> null ,
> BusinessTypeCode numeric(12,0)
> not null ,
> BusinessSubTypeCode int
> null ,
> BusinessName NameLong
> DEFAULT ' '
> not null ,
> WebAddress char(30)
> DEFAULT ' '
> not null ,
> SystemNum char(6)
> DEFAULT ' '
> not null ,
> DisplaySeqNbr int
> DEFAULT 0
> not null ,
> AffiliateNum char(10)
> DEFAULT ' '
> not null ,
> PrincipalStateCode numeric(12,0)
> null ,
> PrincipalCommunity char(30)
> DEFAULT ' '
> not null ,
> PrincipalCounty char(30)
> DEFAULT ' '
> not null ,
> PrincipalCountryCode numeric(12,0)
> null ,
> StateCountyCd char(5)
> DEFAULT ' '
> not null ,
> InsertedUser InsUser
> not null ,
> InsertedDate InsDate
> DEFAULT GetDate()
> not null ,
> LastUpdUser LastUpdUser
> not null ,
> LastUpdDate LastUpdDate
> DEFAULT GetDate()
> not null ,
> TimeStamp timestamp
> null ,
> GSTExemptionNo char(15)
> null ,
> PSTExemptionNo char(15)
> null ,
> BuyingGroup char(1)
> DEFAULT ' '
> null ,
> ProvincialCableGroup char(1)
> DEFAULT ' '
> null ,
> OwnerInfo text
> null ,
> PrimaryPhoneID numeric(12,0)
> null ,
> PrimaryFaxID numeric(12,0)
> null ,
> Inactive char(1)
> null ,
> CCSA AS
> case
> when businesstypecode != 175 then
> case
> when BuyingGroup = 'S' then 'Y'
> else 'N'
> end
> else dbo.f_CCSA_FCCQ(BusinessID, 'C', getdate())
> end ,
> FCCQ AS
> case
> when businesstypecode != 175 then
> case
> when ProvincialCableGroup = 'A' then 'Y'
> else 'N'
> end
> else dbo.f_CCSA_FCCQ(BusinessID, 'F', getdate())
> end
> )
>
> I attached a couple of JPGs to show our app results. The
> GUI uses a wizard to create a new busines of sub-type SYSTEM
> as a hard coded tech code 202 = Cable and subsequently I
> updated it to Tech code 201 which is DTH or satellite so i
> now understand now that our app didn't gen the right INSERT
> to set the column appropriately but the subsequent UPDATE
> set = 201 should have influenced the materialized column and
> set it to 'Mixed'.
>
> What i meant by 'everything evaluated perfectly' is that
> when we generated the materilized column via Sybase Central
> GUI which would have genned an ALTER Table add computed
> materialized column syntax ... the value of the column was
> calculated by the UDF exactly as we had hoped/expected which
> was set out in the f_inside_quebec_yes_no_mixed(systemid)
> function.
>
> Thanks again for your attention to detail and time to review
> our issue.

If you're using ASE 15.x you can implement the create/update columns as materialized computed columns (mcc), though there is a small catch when using the mcc for the update columns.

The mmc's will only get updated if the mcc definition references one of the other columns that are updated; this could lead to a lot of coding if you have a lot of columns, and of course would require maintenance of the mcc definition if you add/modify/drop columns. And of course (?) you need to mask the values of the updated columns so that they don't adversely affect the result of the mcc re-evaluation.

I prefer to minimize the coding and maintenance hassles by insuring the table has a timestamp column, then reference the timestamp column in the mcc definition. [NOTE: The timestamp column is updated anytime any of the other columns in the table are updated, which is typically anytime you're looking to modify the update columns.] Since the timestamp is only used to trigger the mcc re-evaluation, we need to come up with a way to mask the timestamp column (ie, keep it from affecting the mcc's new value).

Confused yet? Hopefully a mug of your favorite beverage and staring at the attached example for awhile will prove beneficial ...

================================
create table t1
(a tinyint
,b tinyint
,c timestamp
,cr_date compute getdate() materialized
,cr_user compute suser_name() materialized
,upd_date compute dateadd(ss,0*c,getdate()) materialized
,upd_user compute left('',0*c)+suser_name() materialized
)
go

-- person doing insert

select suser_name()
go

--------
sa


-- 1st insert

insert t1 (a,b) values (1,2)
select a,b,cr_date,cr_user,upd_date,upd_user from t1
go

a b cr_date cr_user upd_date upd_user
--- --- ---------------------- -------- ------------------- ---------
1 2 Aug 20 2011 4:57PM sa Aug 20 2011 4:57PM sa


-- 2nd insert

waitfor delay "00:02:00"
insert t1 (a,b) values (10,20)
select a,b,cr_date,cr_user,upd_date,upd_user from t1
go

a b cr_date cr_user upd_date upd_user
--- --- ---------------------- -------- ------------------- ---------
1 2 Aug 20 2011 4:57PM sa Aug 20 2011 4:57PM sa
10 20 Aug 20 2011 4:59PM sa Aug 20 2011 4:59PM sa


-- person doing update

select suser_name()
go

--------
markp


-- update

waitfor delay "00:02:00"
update t1 set b = b + 1
select a,b,cr_date,cr_user,upd_date,upd_user from t1
go

a b cr_date cr_user upd_date upd_user
--- --- ---------------------- -------- ------------------- ---------
1 3 Aug 20 2011 4:57PM sa Aug 20 2011 5:01PM markp
10 21 Aug 20 2011 4:59PM sa Aug 20 2011 5:01PM markp

================================


Thanks Mark! Posted on 2011-09-01 13:51:47.0Z
Sender: 3e4d.4e5bd45b.1804289383@sybase.com
From: Thanks Mark!
Newsgroups: sybase.public.ase.general
Subject: Re: Problem with materialized computed columns not being updated after inserts/updates
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4e5f8df3.1dfe.1681692777@sybase.com>
References: <4e5d35b9$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 1 Sep 2011 06:51:47 -0700
X-Trace: forums-1-dub 1314885107 10.22.241.41 (1 Sep 2011 06:51:47 -0700)
X-Original-Trace: 1 Sep 2011 06:51:47 -0700, 10.22.241.41
Lines: 475
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30510
Article PK: 72691

Your comment "I can tell you that computed column
definitions will only fire when the referenced column(s)
value changes." resolves our issue. We were not aware that
this was the key point regarding what triggers an update. I
was wondering how that "magic" was controlled. Thanks for
your suggestions too. We do have a timestamp column on the
table in question ... and frankly, your suggestion to
include it to trigger the function firing is the first
useful thing that I've ever found for a timestamp column! :)
Thanks again for your help! Very much appreciated!

> Still too confusing with 2 different table definitions,
> pictures that have no clear mapping between displayed
> fields and columns in which of the 2 tables,
> INSERT/UPDATE statements with no list of the results (or
> no correlation with fields in the pictures) ...
>
> So let's make this a little easier ...
>
> You've provided the definition of the System table,
> including the computed column you're having problems with
> (ie, System.inside_quebec_yes_no_mixed).
>
> Let's look solely at queries affecting the System table.
>
> Let's take the front-end application out of the picture
> and deal solely with the T-SQL commands being issued to
> the dataserver; to do this we'll use isql for all of your
> T-SQL commands.
>
> Please post the complete output from running the following
> in an isql session:
>
> =================================
> set nocount off
> go
>
> print 'delete'
> delete System
> where SystemID = 24280401
> go
>
> print 'insert'
> INSERT INTO System ( SystemID, TechnologyCode ) VALUES (
> 24280401, 202 ) go
>
> print 'select #1'
> select SystemID, inside_quebec_yes_no_mixed
> from System
> where SystemID = 24280401
> go
>
> print 'update #1'
> UPDATE System Set crtcclassification = 'DTH',
> systemtypecode = 20000268, DesignatedMarket = 'M',
> AreaServed = 'this company should be evaluated as both
> since it will use a tech code of DTH (Direct To Home =
> Satellite) even though it resides in Ontario or outside
> QC' WHERE systemid = 24280401 go
>
> print 'select #2'
> select SystemID, inside_quebec_yes_no_mixed
> from System
> where SystemID = 24280401
> go
>
> print 'update #2'
> UPDATE System SET CRTCClassification = 'DTH',
> DesignatedMarket = 'M', TechnologyCode = 201,
> AllDigitalFlag = 'Y', Alldigitaleffectivedate =
> '10-1-2011 0:0:0.000' WHERE SystemID = 24280401 go
>
> print 'select #3'
> select SystemID, inside_quebec_yes_no_mixed
> from System
> where SystemID = 24280401
> go
> =================================
>
> NOTE: If you've got RI installed and you can't perform
> these specific DELETE/INSERT/UPDATE commands, then replace
> the values with something that will work. The primary
> issue here is to see the actual INSERT/UPDATE commands and
> the results of the follow-on SELECTs.
>
> For each of the 3 SELECT statements, tell us if the SELECT
> displays the expected value for the
> inside_quebec_yes_no_mixed column ... or if the displayed
> value is wrong, tell us what you expect the value to be.
>
> -------------------
>
> In the meantime, I can tell you that computed column
> definitions will only fire when the referenced column(s)
> value changes.
>
> In your case the computed column for
> System.inside_quebec_yes_no_mixed is based solely on the
> SystemID column, so in order for the computed column to
> function as expected, you have to modify the SystemID
> column value. In other words, the computed column will
> only first for an INSERT (when SystemID is populated), or
> during an UPDATE that sets/modifies the SystemID column.
>
> Since you're not performing a 'set' against the SystemID
> column in either of your UPDATEs, the computed column
> definition will not fire and thus the
> inside_quebec_yes_no_mixed column will not be changed.
>
> What you'll need to do is redefine the computed column
> definition to reference one of the columns (or a group of
> columns) that you're updating.
>
> There are a handful of ways you can redefine your computed
> column to fire more often, eg,
>
> - include a rather lengthy, possibly convoluted expression
> that references all desired columns
>
> - if using ASE 15.x consider using the hashbytes()
> function to reference all desired columns (still a bit
> convoluted, and requires a column redefinition each time
> a column is added/dropped)
>
> - add a timestamp column to the table (timestamp columns
> are automatically updated anytime any other column in the
> table changes), then have your computed column definition
> reference the timestamp column
>
> - ... there probably other ways ... you're only limited by
> your imagination ...
>
> I've attached a copy of one of my recent posts to the ISUG
> forums showing how to use a timestamp column to keep the
> last-user/mod-date computed columns up to date.
>
> NOTE: I'm not saying you have to use this same exact
> design, just providing an example that shows how to insure
> the computed column definition fires each time any of the
> columns in my sample table are modified.
>
>
>
> > create table System (
> > SystemID numeric(12,0)
> > not null ,
> > ContractID numeric(12,0)
> > null ,
> > RankNbr int
> > DEFAULT 0
> > not null ,
> > SystemTypeCode numeric(12,0)
> > null ,
> > RegionCode numeric(12,0)
> > null ,
> > CRTCDecisionNumber varchar(10)
> > DEFAULT ' '
> > null ,
> > CRTCClassification char(3)
> > DEFAULT ' '
> > null ,
> > PostalCodeLength NoYes
> > DEFAULT ' '
> > null ,
> > BuyingGroup NoYes
> > DEFAULT ' '
> > null ,
> > ProvincialCableGroup NoYes
> > DEFAULT ' '
> > null ,
> > AnglophonePct numeric(4,1)
> > null ,
> > FrancophonePct numeric(4,1)
> > null ,
> > PotentialSubs int
> > null ,
> > BasicSubs int
> > null ,
> > AsOnDate datetime
> > null ,
> > TechnologyCode numeric(12,0)
> > not null ,
> > InsertedUser InsUser
> > not null ,
> > InsertedDate InsDate
> > DEFAULT GetDate()
> > not null ,
> > LastUpdUser LastUpdUser
> > not null ,
> > LastUpdDate LastUpdDate
> > DEFAULT GetDate()
> > not null ,
> > TimeStamp timestamp
> > null ,
> > AreaServed varchar(1024)
> > null ,
> > DesignatedMarket char(1)
> > null ,
> > CRTCLicenseExempt datetime
> > null ,
> > CRTCLicenseExemptEnd datetime
> > null ,
> > RegionalLicense char(1)
> > DEFAULT 'N'
> > not null CONSTRAINT RegionalLicense_YN CHECK
> > (RegionalLicense in ("Y", "N"))
> > ,
> > Alldigitalflag char(1)
> > DEFAULT 'N'
> > not null ,
> > Alldigitaleffectivedate datetime
> > null ,
> > inside_quebec_yes_no_mixed AS
> > dbo.f_inside_quebec_yes_no_mixed(systemid) MATERIALIZED
> > )
> >
> > function syntax was already included in an attachment
> > but it's short so ... here it is:
> > create function
> > dbo.f_inside_quebec_yes_no_mixed(@SystemID
> > numeric(12,0)) returns char(5)
> > as
> > -- created by Bill Showler Aug 2011
> > -- this function will return a CHAR(5) value of Yes or
> > No or Mixed for the SystemID passed to the parameter
> > @SystemID -- it will return null for the following
> > conditions: -- the value passed to any parameter is
> > null -- @SystemID does not exist in System.SystemID
> >
> > declare @inside_quebec_yes_no_mixed char(5)
> >
> > if @SystemID is null return null
> >
> > select @inside_quebec_yes_no_mixed = case TechnologyCode
> > when 201 then 'Mixed' else (case PrincipalStateCode when
> > 209 then 'Yes' else 'No' end) end
> > from business b, system s
> > where b.businessid = s.systemid
> > and s.systemid = @SystemID
> >
> > return @inside_quebec_yes_no_mixed
> > go
> >
> > and the parent table above this System table (Yes, I
> > can't imagine who would choose a table name that sooo
> > looks like a reserved word! :)) is the business table
> > which is where you find the province code which
> > determines the computed column's materialized value if
> > Quebec and the system.technologycode is not satellite or
> > 201. The business.PrincipalStateCode for Quebec is 209.
> >
> > create table Business (
> > BusinessID numeric(12,0)
> > identity ,
> > Alias char(50)
> > DEFAULT ' '
> > not null ,
> > DMAID int
> > null ,
> > BusinessTypeCode numeric(12,0)
> > not null ,
> > BusinessSubTypeCode int
> > null ,
> > BusinessName NameLong
> > DEFAULT ' '
> > not null ,
> > WebAddress char(30)
> > DEFAULT ' '
> > not null ,
> > SystemNum char(6)
> > DEFAULT ' '
> > not null ,
> > DisplaySeqNbr int
> > DEFAULT 0
> > not null ,
> > AffiliateNum char(10)
> > DEFAULT ' '
> > not null ,
> > PrincipalStateCode numeric(12,0)
> > null ,
> > PrincipalCommunity char(30)
> > DEFAULT ' '
> > not null ,
> > PrincipalCounty char(30)
> > DEFAULT ' '
> > not null ,
> > PrincipalCountryCode numeric(12,0)
> > null ,
> > StateCountyCd char(5)
> > DEFAULT ' '
> > not null ,
> > InsertedUser InsUser
> > not null ,
> > InsertedDate InsDate
> > DEFAULT GetDate()
> > not null ,
> > LastUpdUser LastUpdUser
> > not null ,
> > LastUpdDate LastUpdDate
> > DEFAULT GetDate()
> > not null ,
> > TimeStamp timestamp
> > null ,
> > GSTExemptionNo char(15)
> > null ,
> > PSTExemptionNo char(15)
> > null ,
> > BuyingGroup char(1)
> > DEFAULT ' '
> > null ,
> > ProvincialCableGroup char(1)
> > DEFAULT ' '
> > null ,
> > OwnerInfo text
> > null ,
> > PrimaryPhoneID numeric(12,0)
> > null ,
> > PrimaryFaxID numeric(12,0)
> > null ,
> > Inactive char(1)
> > null ,
> > CCSA AS
> > case
> > when businesstypecode != 175 then
> > case
> > when BuyingGroup = 'S' then 'Y'
> > else 'N'
> > end
> > else dbo.f_CCSA_FCCQ(BusinessID, 'C',
> > getdate()) end ,
> > FCCQ AS
> > case
> > when businesstypecode != 175 then
> > case
> > when ProvincialCableGroup = 'A' then
> > 'Y' else 'N'
> > end
> > else dbo.f_CCSA_FCCQ(BusinessID, 'F',
> > getdate()) end
> > )
> >
> > I attached a couple of JPGs to show our app results.
> > The GUI uses a wizard to create a new busines of
> > sub-type SYSTEM as a hard coded tech code 202 = Cable
> > and subsequently I updated it to Tech code 201 which is
> > DTH or satellite so i now understand now that our app
> > didn't gen the right INSERT to set the column
> > appropriately but the subsequent UPDATE set = 201 should
> > have influenced the materialized column and set it to
> 'Mixed'. >
> > What i meant by 'everything evaluated perfectly' is that
> > when we generated the materilized column via Sybase
> > Central GUI which would have genned an ALTER Table add
> > computed materialized column syntax ... the value of the
> > column was calculated by the UDF exactly as we had
> > hoped/expected which was set out in the
> > f_inside_quebec_yes_no_mixed(systemid) function.
> >
> > Thanks again for your attention to detail and time to
> > review our issue.
>
>
> [computed_column.example.txt]
> If you're using ASE 15.x you can implement the
> create/update columns as materialized computed columns
> (mcc), though there is a small catch when using the mcc
> for the update columns.
>
> The mmc's will only get updated if the mcc definition
> references one of the other columns that are updated; this
> could lead to a lot of coding if you have a lot of columns
> , and of course would require maintenance of the mcc
> definition if you add/modify/drop columns. And of course
> (?) you need to mask the values of the updated columns so
> that they don't adversely affect the result of the mcc
> re-evaluation.
>
> I prefer to minimize the coding and maintenance hassles by
> insuring the table has a timestamp column, then reference
> the timestamp column in the mcc definition. [NOTE: The
> timestamp column is updated anytime any of the other
> columns in the table are updated, which is typically
> anytime you're looking to modify the update columns.]
> Since the timestamp is only used to trigger the mcc
> re-evaluation, we need to come up with a way to mask the
> timestamp column (ie, keep it from affecting the mcc's new
> value).
>
> Confused yet? Hopefully a mug of your favorite beverage
> and staring at the attached example for awhile will prove
> beneficial ...
>
> ================================
> create table t1
> (a tinyint
> ,b tinyint
> ,c timestamp
> ,cr_date compute getdate() materialized
> ,cr_user compute suser_name() materialized
> ,upd_date compute dateadd(ss,0*c,getdate()) materialized
> ,upd_user compute left('',0*c)+suser_name() materialized
> )
> go
>
> -- person doing insert
>
> select suser_name()
> go
>
> --------
> sa
>
>
> -- 1st insert
>
> insert t1 (a,b) values (1,2)
> select a,b,cr_date,cr_user,upd_date,upd_user from t1
> go
>
> a b cr_date cr_user upd_date
> upd_user
> --- --- ---------------------- --------
> ------------------- ---------
> 1 2 Aug 20 2011 4:57PM sa Aug 20 2011
> 4:57PM sa
>
>
> -- 2nd insert
>
> waitfor delay "00:02:00"
> insert t1 (a,b) values (10,20)
> select a,b,cr_date,cr_user,upd_date,upd_user from t1
> go
>
> a b cr_date cr_user upd_date
> upd_user
> --- --- ---------------------- --------
> ------------------- ---------
> 1 2 Aug 20 2011 4:57PM sa Aug 20 2011
> 4:57PM sa
> 10 20 Aug 20 2011 4:59PM sa Aug 20 2011
> 4:59PM sa
>
>
> -- person doing update
>
> select suser_name()
> go
>
> --------
> markp
>
>
> -- update
>
> waitfor delay "00:02:00"
> update t1 set b = b + 1
> select a,b,cr_date,cr_user,upd_date,upd_user from t1
> go
>
> a b cr_date cr_user upd_date
> upd_user
> --- --- ---------------------- --------
> ------------------- ---------
> 1 3 Aug 20 2011 4:57PM sa Aug 20 2011
> 5:01PM markp
> 10 21 Aug 20 2011 4:59PM sa Aug 20 2011
> 5:01PM markp
>
> ================================
>
> [Attachment: computed_column.example.txt]


Mark A. Parsons Posted on 2011-09-01 15:29:30.0Z
Sender: 438f.4e5d1e83.1804289383@sybase.com
From: Mark A. Parsons
Newsgroups: sybase.public.ase.general
Subject: Re: Problem with materialized computed columns not being updated after inserts/updates
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4e5fa4da.24d9.1681692777@sybase.com>
References: <4e5f8df3.1dfe.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 1 Sep 2011 08:29:30 -0700
X-Trace: forums-1-dub 1314890970 10.22.241.41 (1 Sep 2011 08:29:30 -0700)
X-Original-Trace: 1 Sep 2011 08:29:30 -0700, 10.22.241.41
Lines: 45
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30511
Article PK: 72693


> Your comment "I can tell you that computed column
> definitions will only fire when the referenced column(s)
> value changes." resolves our issue. We were not aware
> that this was the key point regarding what triggers an
> update. I was wondering how that "magic" was controlled.

Yeah, this particular piece of 'magic' isn't documented very
well (at all?) in the manuals.

> Thanks for your suggestions too. We do have a timestamp
> column on the table in question ... and frankly, your
> suggestion to include it to trigger the function firing is
> the first useful thing that I've ever found for a
> timestamp column! :)

For completeness sake ...

1 - The timestamp column is updated anytime any of the other
columns in the row are modified.

If a computed column does not need to be recomputed when
some/most of your columns are updated, and said columns are
updated a 'lot' of times, you could find that your computed
column logic is fired too often. In this scenario it may
make more sense to design the computed column definition to
reference just the necessary columns ... with the objective
being to cut down on unnecessary invocations of the computed
column definition.

2 - SQL UDFs are known to be performance hogs, eg, I've
measured some UDFs taking upwards of 300-1200% longer to run
than an equivalent in-line solution (ymmv).

If a computed column definition calls a SQL UDF, and your
computed column is being updated too often (eg, see item #1
- above), you could start to notice some degradation in
UPDATE performance due to the excessive overhead of the SQL
UDF calls.

> Thanks again for your help! Very much appreciated!

You're welcome.

Glad someone else has found a use for the timestamp column!
;-)