Solved

Add Calculated Field to SQL Results Output

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SYbase 4 31
sql select record as one long string 21 24
SQL Syntax: How to force case sensitive query? 2 30
SQL server vNext 18 29
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.

803 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