• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1805
  • Last Modified:

Sql Computed Column Formula

Hi-
I wanted to create a computed column in a table.

Example: if  ( (field1+field2) - field3) >=0 then "Yes" else "No"

Can someone help me with the syntax.
Thanks
0
stacydr
Asked:
stacydr
  • 2
  • 2
  • 2
1 Solution
 
openshacCommented:
Not sure which version of SQL you are using but try this for T-SQL
SELECT
CASE WHEN field1 + field2 - field3 >=0 THEN "Yes" ELSE "No" END

Open in new window

0
 
chapmandewCommented:
alter table tablename
add fieldname as case when  ( (field1+field2) - field3) >=0 then 'Yes' else 'No' end
0
 
stacydrAuthor Commented:
Hi-Thanks
I'm using Sql 2005 -- I'm still getting a validation error...

I wrote this in the (Formula) row in the design of my table cstd_commission_quota under Computed Column Specification..

SELECT CASE when [actual_opportunities] +[actual_qualitative_score] -[total_projected_quota] >=0 THEN "Yes" ELSE "No" END
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
chapmandewCommented:
take the SELECT off of it, and take away the double quotes

CASE when [actual_opportunities] +[actual_qualitative_score] -[total_projected_quota] >=0 THEN 'Yes' ELSE 'No' END
0
 
stacydrAuthor Commented:
Thank you soo much!!
0
 
openshacCommented:
Sorry misunderstood the question try this:

Have you thought about just doing it in the query, this will prevent de-normalised data in the table
--EXEC prQuantOptimiser_SelectSessionNumbers 15
 
 
CREATE TABLE #MyTable ( tb1 bit, tb2 bit, tbcalculatedcolumn AS CASE WHEN
tb1 = 1 then 1
WHEN tb2 = 1 then 2
END )
 
 
INSERT INTO #MyTable ( tb1, tb2 ) VALUES ( 1,1 )
INSERT INTO #MyTable ( tb1, tb2 ) VALUES ( 1,0 )
INSERT INTO #MyTable ( tb1, tb2 ) VALUES ( 0,1 )
 
SELECT * FROM #MyTable
/*
tb1 tb2 tbcalculatedcolumn
---- ---- ------------------
1 1 1
1 0 1
0 1 2
*/
 
DROP TABLE #MyTable 

Open in new window

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now