Solved

Add Calculated Field to SQL Results Output

Posted on 2008-10-06
3
685 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
[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
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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

739 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