question

koptastic69 avatar image
koptastic69 asked

Computed Column

I have the following requirement for a computed column, based on the contents of another column in the same table. If the amount is less than 0, the value should be C otherwise it's value should be D.

I have used the following notation in the computed column specification;

CASE [monAmount] WHEN < 0 THEN 'C' ELSE 'D'

It will not validate. Why?

computed-column
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Pavel Pawlowski avatar image
Pavel Pawlowski answered

You should write the expression in this format:

CASE WHEN [monAmount] < 0 THEN 'C' ELSE 'D' END

and everything should be ok.

1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Pavel - many thanks
0 Likes 0 ·
Fatherjack avatar image
Fatherjack answered

Richard, try this:

CREATE TABLE tempja
(colA INT IDENTITY(-2,1),
colB  AS (CASE WHEN cola < 0 THEN 'C' ELSE 'D' end),
colC int)

run this 5 times:

INSERT INTO [dbo].[tempja]
        (  [colC] )
VALUES
        ( 
          1  -- colC - int
          )
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Peso avatar image
Peso answered

Also add the PERSISTED keyword to the column definition. It will remove the need to recalculate the formula over and over again, at the expense of some storage space.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.