Improve company productivity with a Business Account.Sign Up

x
?
Solved

Trying to create a computed column on a view

Posted on 2009-05-04
2
Medium Priority
?
220 Views
Last Modified: 2012-05-06
I have a view I am trying to add a column to. The new column will be computed by taking 3 other columns. Essentially I want a sum of (ColA -(ColB+ColC)) . In my ALTER View statement I am trying to create a column  by using the statement below. Any idea as to how to do this.  I should mention i want totals to be per row and not a grand total. Any help would be greatly appreciated.
ALTER VIEW [dbo].[v_viewinvoicetot]
AS
SELECT SUM(orderTbl.totprod_nm - (orderTbl.sandh_nm + orderTbl.tax_d)) AS totprodnm_custom
 
FROM         invoiceTbl INNER JOIN
                      orderTbl ON invoiceTbl.orderid_i = orderTbl.orderid_i

Open in new window

0
Comment
Question by:jazzcatone
2 Comments
 
LVL 60

Accepted Solution

by:
chapmandew earned 1000 total points
ID: 24298948
try this:

ALTER VIEW [dbo].[v_viewinvoicetot]
AS
SELECT *, orderTbl.totprod_nm - orderTbl.sandh_nm + orderTbl.tax_d AS totprodnm_custom
 
FROM         invoiceTbl INNER JOIN
                      orderTbl ON invoiceTbl.orderid_i = orderTbl.orderid_i
 


0
 
LVL 41

Assisted Solution

by:Sharath
Sharath earned 1000 total points
ID: 24299636

Asker - do you want (ColA -(ColB+ColC)) or ColA-ColB+ColC ? chapmandew query will calculate ColA-ColB+ColC.
If you want ColA-(ColB+ColC), then try like this.
ALTER VIEW [dbo].[v_viewinvoicetot]
AS
SELECT *, orderTbl.totprod_nm - orderTbl.sandh_nm - orderTbl.tax_d AS totprodnm_custom
  FROM invoiceTbl 
 INNER JOIN orderTbl ON invoiceTbl.orderid_i = orderTbl.orderid_i

Open in new window

0

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

595 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