can you solve my nightmare? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

can you solve my nightmare?

Author

Topic

Pace
Constraint Violating Yak Guru

264 Posts

Posted - 2006-10-12 : 10:31:44

Hey all,

I have this nightmare of a query in access;

INSERT INTO WordItemsToReOrder ( WorkCentre, ProductCode, Description, QtyInStock, ToOrder, Allocated )
SELECT Product.ProductionGroup, Product.[Product Code], Product.Description, Product.[Qty In Stock], IIf(Nz([Qty In Stock])-Nz([AllocationsNext60Days].[SumOfQty])+Nz([OnOrder])<0,(Nz([Qty In Stock])-Nz([AllocationsNext60Days].[SumOfQty])+Nz([OnOrder]))*-1+[ROQ],[ROQ]) AS ToOrder, AllocationsNext60Days.SumOfQty AS Allocated
FROM Product LEFT JOIN AllocationsNext60Days ON Product.[Product Code] = AllocationsNext60Days.[Product Code]
WHERE (((Product.Manufactured)=True) AND ((Product.ROQ)>0) AND ((Nz([Product].[Qty In Stock])-Nz([AllocationsNext60Days].[SumOfQty])+Nz([Product].[OnOrder]))<Nz([Product].[ROL])) AND ((Product.Obsolete)=False) AND ((Product.NoAutoGenerate)=False))
ORDER BY Product.ProductionGroup, Product.[Product Code];

Can someone help me to get it to work in SQL?

I managed;

SELECT     
	prod.ProductionGroup, prod.[Product Code], prod.Description, 
	prod.[Qty In Stock], 
		CASE WHEN 
			IsNull(prod.[Qty In Stock], 0) - IsNull(alloc.SumOfQty, 0) 
    + IsNull(prod.OnOrder, 0) < 0 THEN 
	IsNull(prod.[Qty In Stock], 0) - IsNull(alloc.SumOfQty, 0) + IsNull(prod.OnOrder, 0) 
    * - 1 + prod.ROQ + IsNull(tmpAmendment.AmendmentTotal, 0) 
	ELSE prod.ROQ + IsNull(tmpAmendment.AmendmentTotal, 0) 

	END AS ToOrder, alloc.SumOfQty

FROM         
		dbo.Product AS prod INNER JOIN
        dbo.v_WorksOrders_AllocationNext60Days AS alloc ON prod.[Product Code] = 
		alloc.[Product Code] AND ISNULL(prod.ROL, 0) 
        > ISNULL(prod.[Qty In Stock], 0) - ISNULL(alloc.SumOfQty, 0) 
		+ ISNULL(prod.OnOrder, 0) LEFT OUTER JOIN
        dbo.tmpWorksOrdersAmendments AS tmpAmendment ON tmpAmendment.productGroup = prod.ProductionGroup AND 
        tmpAmendment.productCode = prod.[Product Code]
WHERE     
		(prod.Manufactured = 1) AND (prod.ROQ > 0) AND 
		(prod.Obsolete = 0) AND (prod.NoAutoGenerate = 0) AND 
		(prod.ProductionGroup = 'EXP')

But the results are not the same... by a long way lol... I should add im trying to use the second query as a view, which is why there is no insert.

If you can help me I would be forever in your debt.

"Impossible is Nothing"

nr
SQLTeam MVY

12543 Posts

Posted - 2006-10-12 : 10:45:24

INSERT INTO WordItemsToReOrder ( WorkCentre, ProductCode, Description, QtyInStock, ToOrder, Allocated )
SELECT Product.ProductionGroup, Product.[Product Code], Product.Description, Product.[Qty In Stock],
case when (coalesce([Qty In Stock],0)-coalesce([AllocationsNext60Days].[SumOfQty],0)+coalesce([OnOrder],0)<0 then (coalesce([Qty In Stock],0)-coalesce([AllocationsNext60Days].[SumOfQty],0)+coalesce([OnOrder],0))*-1+[ROQ] else [ROQ] end AS ToOrder,
AllocationsNext60Days.SumOfQty AS Allocated
FROM Product
LEFT JOIN AllocationsNext60Days
ON Product.[Product Code] = AllocationsNext60Days.[Product Code]
WHERE (((Product.Manufactured)=1)
AND ((Product.ROQ)>0)
AND ((coalesce([Product].[Qty In Stock],0)-coalesce([AllocationsNext60Days].[SumOfQty],0)+coalesce([Product].[OnOrder],0))<coalesce([Product].[ROL],0)) AND ((Product.Obsolete)=0) AND ((Product.NoAutoGenerate)=0))
ORDER BY Product.ProductionGroup, Product.[Product Code]

But I suspect that's the same as your code.
Could be a difference in the data?
Maybe the representation of booleans? Arithmetic differences?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Pace
Constraint Violating Yak Guru

264 Posts

Posted - 2006-10-12 : 10:54:37

Hey nr.

Thank you so much for your code!

It looks much better however, im getting error near keyword "then"

I think its around here, a bracket maybe, im not sure =S

)<0 then (coalesce([Qty In Stock],0)-coalesce([AllocationsNext60Days].[SumOfQty],0)+coalesce([OnOrder],0))*-1+[ROQ] else [ROQ] end AS ToOrder

"Impossible is Nothing"

nr
SQLTeam MVY

12543 Posts

Posted - 2006-10-12 : 11:09:22

Had an extra starting bracket.

INSERT INTO WordItemsToReOrder ( WorkCentre, ProductCode, Description, QtyInStock, ToOrder, Allocated )
SELECT Product.ProductionGroup, Product.[Product Code], Product.Description, Product.[Qty In Stock],
case when coalesce([Qty In Stock],0)-coalesce([AllocationsNext60Days].[SumOfQty],0)+coalesce([OnOrder],0)<0 then (coalesce([Qty In Stock],0)-coalesce([AllocationsNext60Days].[SumOfQty],0)+coalesce([OnOrder],0))*-1+[ROQ] else [ROQ] end AS ToOrder,
AllocationsNext60Days.SumOfQty AS Allocated
FROM Product
LEFT JOIN AllocationsNext60Days
ON Product.[Product Code] = AllocationsNext60Days.[Product Code]
WHERE (((Product.Manufactured)=1)
AND ((Product.ROQ)>0)
AND ((coalesce([Product].[Qty In Stock],0)-coalesce([AllocationsNext60Days].[SumOfQty],0)+coalesce([Product].[OnOrder],0))<coalesce([Product].[ROL],0)) AND ((Product.Obsolete)=0) AND ((Product.NoAutoGenerate)=0))
ORDER BY Product.ProductionGroup, Product.[Product Code]

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Pace
Constraint Violating Yak Guru

264 Posts

Posted - 2006-10-13 : 07:46:36

Hey there...

My eventual solution... I was missing a square bracket on the modified query for SQL, therefore messing the number up;

SELECT     prod.ProductionGroup, prod.[Product Code], prod.Description, prod.[Qty In Stock], CASE WHEN IsNull(prod.[Qty In Stock], 0) - IsNull(alloc.SumOfQty, 0) 
                      + IsNull(prod.OnOrder, 0) < 0 THEN (IsNull(prod.[Qty In Stock], 0) - IsNull(alloc.SumOfQty, 0) + IsNull(prod.OnOrder, 0)) * - 1 + prod.ROQ + IsNull(tmpAmendment.AmendmentTotal, 0)  END AS ToOrder, 
                      alloc.SumOfQty
FROM         dbo.Product AS prod INNER JOIN
                      dbo.v_WorksOrders_AllocationNext60Days AS alloc ON prod.[Product Code] = alloc.[Product Code] AND ISNULL(prod.ROL, 0) 
                      > ISNULL(prod.[Qty In Stock], 0) - ISNULL(alloc.SumOfQty, 0) + ISNULL(prod.OnOrder, 0) LEFT OUTER JOIN
                      dbo.tmpWorksOrdersAmendments AS tmpAmendment ON tmpAmendment.productGroup = prod.ProductionGroup AND 
                      tmpAmendment.productCode = prod.[Product Code]
WHERE     (prod.Manufactured = 1) AND (prod.ROQ > 0) AND (prod.Obsolete = 0) AND (prod.NoAutoGenerate = 0) AND (prod.ProductionGroup = 'EXP')

"Impossible is Nothing"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources