Nested Logic in Persisted Columns Formula

Question

I'm new to and having trouble with Formulae Syntax in Persisted Columns.

A - I need case when (CustomerAccountID IS NULL and MissCustNameMatched=0) OR errLicensingProgMissing=1 OR errLicensingSubMissing=1 then (1) else (0) end

This won't validate correctly.

B - Or can I do it somehow like this *

case

when [MissCustName] IS true then
- when [CustomerAccountName] IS NULL then
- - (1)
- else
- - (0)
- end
else
- (0)
end*

score 1 · Accepted Answer · answered Jun 13 '12 at 11:08

Your two cases don't match on column names but following persisted field declaration shows how it could be done using a CASE statement.

CREATE TABLE dbo.Test (
  CustomerAccountID INTEGER
  , MissCustNameMatched INTEGER
  , errLicensingProgMissing INTEGER
  , errLicensingSubMissing INTEGER
  , persistedField AS 
      CASE MissCustNameMatched WHEN 1 
      THEN 
        CASE CustomerAccountID WHEN 1 
        THEN 1 
        ELSE 0 
        END 
      ELSE 0 
      END PERSISTED
)

Nested Logic in Persisted Columns Formula

1 Answers1