Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Trying to create a computed column on a view

Posted on 2009-05-04
2
Medium Priority
?
214 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

688 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