Solved

Add Calculated Field to SQL Results Output

Posted on 2008-10-06
3
682 Views
Last Modified: 2010-04-21
Here's the current script:

SELECT soitem.fsono, soitem.fpartno, soitem.fdesc, somast.fcompany, soitem.fquantity, soitem.fduedate,
shitem.fshipqty, shmast.fshipno
FROM somast
INNER JOIN soitem on somast.fsono = soitem.fsono
INNER JOIN shmast on somast.fsono = shmast.fcsono
INNER JOIN shitem on somast.fsono = LEFT(shitem.fsokey,6) AND  shitem.fshipno = shmast.fshipno
  AND shitem.fpartno = soitem.fpartNo
where somast.fstatus = 'OPEN'
 AND (somast.fsono>= '100000' AND somast.fsono <= '199999'
 OR somast.fsono >= '400000' AND somast.fsono<= '499999'
 OR somast.fsono>= '700000' AND somast.fsono <= '799999')
order by somast.fsono, soitem.fpartno

When this runs, I need another column to be included in the resulting output. The column would be QTYAVAILABLE and would be equal to SOITEM.FQUANTITY - SHITEM.FSHIPQTY.  I don't want to create a new table or add a new field to an existing table - just want to get the calculated field included in the result. How do I add this to my script?
0
Comment
Question by:glennes
  • 2
3 Comments
 
LVL 60

Accepted Solution

by:
chapmandew earned 300 total points
ID: 22649300

SELECT soitem.fsono, soitem.fpartno, soitem.fdesc, somast.fcompany, soitem.fquantity, soitem.fduedate,
shitem.fshipqty, shmast.fshipno, QTYAVAILABLE  = (SOITEM.FQUANTITY - SHITEM.FSHIPQTY)
FROM somast
INNER JOIN soitem on somast.fsono = soitem.fsono
INNER JOIN shmast on somast.fsono = shmast.fcsono
INNER JOIN shitem on somast.fsono = LEFT(shitem.fsokey,6) AND  shitem.fshipno = shmast.fshipno
  AND shitem.fpartno = soitem.fpartNo
where somast.fstatus = 'OPEN'
 AND (somast.fsono>= '100000' AND somast.fsono <= '199999'
 OR somast.fsono >= '400000' AND somast.fsono<= '499999'
 OR somast.fsono>= '700000' AND somast.fsono <= '799999')
order by somast.fsono, soitem.fpartno
0
 

Author Comment

by:glennes
ID: 22649357
Works just right...thanks!
0
 

Author Closing Comment

by:glennes
ID: 31503379
Really appreciate the quick reply!
0

Featured Post

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

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

Suggested Solutions

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
I have a large data set and a SSIS package. How can I load this file in multi threading?
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.
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

685 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