stacydr
asked on
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
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
alter table tablename
add fieldname as case when ( (field1+field2) - field3) >=0 then 'Yes' else 'No' end
add fieldname as case when ( (field1+field2) - field3) >=0 then 'Yes' else 'No' end
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you soo much!!
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
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