Solved

# SQL2K Query problem

Posted on 2005-05-16
208 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:mwmiller78
1 Comment

LVL 65

Accepted Solution

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

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.