Solved

# SQL2K Query problem

Posted on 2005-05-16
Medium Priority
259 Views
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
Question by:Mike Miller
1 Comment

LVL 66

Accepted Solution

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

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
Course of the Month13 days, 17 hours left to enroll