[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Add Calculated Field to SQL Results Output

Posted on 2008-10-06
3
Medium Priority
?
700 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:Glenn Stearns
  • 2
3 Comments
 
LVL 60

Accepted Solution

by:
chapmandew earned 1200 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:Glenn Stearns
ID: 22649357
Works just right...thanks!
0
 

Author Closing Comment

by:Glenn Stearns
ID: 31503379
Really appreciate the quick reply!
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
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 to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

868 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