Solved

Sql Computed Column Formula

Posted on 2008-10-28
6
1,753 Views
Last Modified: 2010-04-21
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
Comment
Question by:stacydr
  • 2
  • 2
  • 2
6 Comments
 
LVL 6

Expert Comment

by:openshac
Comment Utility
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
 
LVL 60

Expert Comment

by:chapmandew
Comment Utility
alter table tablename
add fieldname as case when  ( (field1+field2) - field3) >=0 then 'Yes' else 'No' end
0
 
LVL 2

Author Comment

by:stacydr
Comment Utility
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 60

Accepted Solution

by:
chapmandew earned 125 total points
Comment Utility
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
 
LVL 2

Author Closing Comment

by:stacydr
Comment Utility
Thank you soo much!!
0
 
LVL 6

Expert Comment

by:openshac
Comment Utility
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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now