?
Solved

SQL2K Query problem

Posted on 2005-05-16
1
Medium Priority
?
259 Views
Last Modified: 2010-03-19
Can someone help me rewrite this??

SELECT SUM(CAST(cast(imMTownStock.Cost as float) * cast(imMTownStock.Quantity as float) - cast(MainWIP.Cost as float) * CAST(MainWIP.Quantity as float) AS DECIMAL(20, 2))) AS NetTotal FROM MainWIP LEFT JOIN imMTownStock ON ([MainWIP].PART_NUMBER = [imPiawipiof].ShopOrder AND [MainWIP].Quantity <> [imPiawipiof].Quantity or [MainWIP].PART_NUMBER = [imPiawiproic].RepOrder AND [MainWIP].Quantity <> [imPiawiproic].Quantity or [MainWIP].PART_NUMBER = [imPifin].ROIC AND [MainWIP].Quantity <> [imPifin].Quantity or [MainWIP].PART_NUMBER = [imPirrb].PartNumber AND [MainWIP].Quantity <> [imPirrb].Quantity or [MainWIP].PART_NUMBER = [imPiwip].RepOrder AND [MainWIP].Quantity <> [imPiwip].Quantity)


It's not recognizing the tables imPiawipiof, imPiawiproic, imPifin, imPirrb, imPiwip
0
Comment
Question by:Mike Miller
1 Comment
 
LVL 66

Accepted Solution

by:
Jim Horn earned 2000 total points
ID: 14011281
SELECT SUM(CAST(cast(imMTownStock.Cost as float) * cast(imMTownStock.Quantity as float) - cast(MainWIP.Cost as float) * CAST(MainWIP.Quantity as float) AS DECIMAL(20, 2))) AS NetTotal
FROM MainWIP
LEFT JOIN imMTownStock ON ([MainWIP].PART_NUMBER = [imPiawipiof].ShopOrder AND [MainWIP].Quantity <> [imPiawipiof].Quantity or [MainWIP].PART_NUMBER = [imPiawiproic].RepOrder AND [MainWIP].Quantity <> [imPiawiproic].Quantity or [MainWIP].PART_NUMBER = [imPifin].ROIC AND [MainWIP].Quantity <> [imPifin].Quantity or [MainWIP].PART_NUMBER = [imPirrb].PartNumber AND [MainWIP].Quantity <> [imPirrb].Quantity or [MainWIP].PART_NUMBER = [imPiwip].RepOrder AND [MainWIP].Quantity <> [imPiwip].Quantity)

You need to add some JOIN clauses to accurately reflect the relationship between MainWIP and test tables.  Something like...

INNER JOIN imPiawipof ON MainWIP.SomeIDField = imPiawipof.SomeIDField
INNER JOIN imPiawiproic ON MainWIP.SomeIDField = imPiawiproic.SomeIDField
etc.

At the moment, the only tables you are joining, therefore SQL can recognize, is MainWIP and imMTownStock

Hope this helps.
-Jim
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

809 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