Solved

Trying to create a computed column on a view

Posted on 2009-05-04
2
206 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 250 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 40

Assisted Solution

by:Sharath
Sharath earned 250 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

895 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

16 Experts available now in Live!

Get 1:1 Help Now