Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Trying to create a computed column on a view

Posted on 2009-05-04
2
Medium Priority
?
216 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
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

916 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