Solved

Add Calculated Field to SQL Results Output

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

707 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now