Computed Columns Formulas

Question

Computed Columns Formulas

whoopcg

SSC Enthusiast

Points: 127
More actions
July 22, 2019 at 7:18 pm

#3665194

So, I'm trying to figure out what's the best approach for this:
(FIelds:)
A - currency
B - Equals A
C - (A+B)/2
D - Checkbox: If on, C=C/2 (meaning, not a new field, just turns C into Half of C)
I need this fields to be "persisted", so that I can use them on reports.
Thought the best way was to create Computed Columns on SQL. Can I accomplish all this through this method?
Tried for example the field B, using the formula area for computed columns and typing A field's name.
It could be the wrong "formula", i could be missing something else...
Can anyone give me a push?
Thanks in advance.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

crow1969 Hall of Fame Points: 3001 More actions · Answer 1

Try to create this in a view to start with. Computed columns can not be used in the definition of other computed columns, so that will cause you some problems here.

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 2

The column D is a normal bit column, not computed. So the definition of column C could be:

ALTER TABLE dbo.mytable ADD C AS (A + B) / CASE D WHEN 1 THEN 4 ELSE 2 END;

That is a deterministic expression, so you could add PERSISTED at the end.

whoopcg SSC Enthusiast Points: 127 More actions · Answer 3

Thank you for your help, Scott

Worked like a charm! Sorry for the late feedback but I was away.

So, in a more complex field, where if C="WORD":

A = 51 if B is between 0 and 2000
A = 102 if B is between 2001 and 8000
A = 204 if B is higher than 8000
AND if D is ticked the A value turns into half

Multiple conditions are making me go round and round 🙂 Not working, off course.

ALTER TABLE dbo.tablename
  ADD FieldA AS (CASE WHEN FieldC = "Word" THEN
      CASE WHEN FieldB>0 AND FieldB<=2000 THEN 102
           WHEN FieldB>2000 AND FieldB<=8000 THEN 204
           WHEN FieldB>8000 THEN 306
        ELSE NULL         
    END,

This reply was modified 4 years, 8 months ago by whoopcg.

whoopcg SSC Enthusiast Points: 127 More actions · Answer 4

Thank you for your reply Crow, I'm trying to solve the more complex ones first and see if I can get around the only field that needs to be calculated upon another calculation by, maybe repeating the calculation inside of it...

This reply was modified 4 years, 8 months ago by whoopcg.

pietlinden SSC Guru Points: 63428 More actions · Answer 5

You should be able to do this (create calculated columns) inside SSRS... or are you not using SSRS?

whoopcg SSC Enthusiast Points: 127 More actions · Answer 6

Hi there, pietlinden.

No, I'm not using SSRS. I will need these values for some reports in the future but for now I'm just creating the columns and their calculations as they must be stored on the tables.

whoopcg SSC Enthusiast Points: 127 More actions · Answer 7

The code I showed was missing and "end" and a ")".

But was suggested to me that I would use this instead, and it works great!

Link

In case someone else needs it 🙂

ALTER TABLE dbo.tablename
ADD FieldA AS 
  (
    CASE 
      WHEN FieldC = "Word" THEN
        CASE 
          WHEN FieldB > 8000 
            THEN 204
          WHEN FieldB > 2000
            THEN 102
          WHEN FieldB >= 0 
            THEN 51
          ELSE NULL
        END
    END
    / POWER(2,FieldD) --<--- Or, possibly, POWER(2,CAST(FieldD AS tinyint))
   )

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 8

whoopcg wrote:

The code I showed was missing and "end" and a ")".

But was suggested to me that I would use this instead, and it works great!

Link

In case someone else needs it 🙂

ALTER TABLE dbo.tablename
ADD FieldA AS 
  (
    CASE 
      WHEN FieldC = "Word" THEN
        CASE 
          WHEN FieldB > 8000 
            THEN 204
          WHEN FieldB > 2000
            THEN 102
          WHEN FieldB >= 0 
            THEN 51
          ELSE NULL
        END
    END
    / POWER(2,FieldD) --<--- Or, possibly, POWER(2,CAST(FieldD AS tinyint))
   )

Don't forget to add the PERSISTED keyword or this will be as performance poor as if you used a scalar function to do the same thing. PERSISTED causes the column to materialize so it does take some extra memory/disk space but it almost always well worth it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

whoopcg SSC Enthusiast Points: 127 More actions · Answer 9

whoopcg

SSC Enthusiast

Points: 127

August 8, 2019 at 7:37 pm

#3670032

Will do! Thanks for the reminder

jcelko212 32090 SSCrazy Eights Points: 9294 More actions · Answer 10

First of all, in SQL a field is part of a column. For example, the year month and day are fields within a date column. The next misconception is that the columns of a table are created from left to right as if they were columns in a punchcard. SQL is set-oriented and the whole row comes into existence all at once. Each computation has to stand by itself without any dependency on the order of creation. Another example of this model of computation can be seen in the update statement

UPDATE Foobar

SET a = b, b = a;

This switches the values in the columns a and b because both assignments are done at the same time. However,, the more conventional procedural version:

BEGIN

UPDATE Foobar SET a = b;

UPDATE Foobar SET b = a;

END;

Will first set column a to the value of b, then column b set back to a, which in effect won't change it. You get the reverse situation if you started with the second update statement. Ed Dykstra did early work on this model of computation.

Please post DDL and follow ANSI/ISO standards when asking for help.