Need syntax help
-
Hello,
I have the following query in an Access 2002 database (using SQL-92) that I would like to move to a SQL Server 7.0 stored proc but I'm having trouble figuring out the syntax nuances.
Access:
SELECT ITEM_H.ItemNo, (INV_LVLS.MaxOH-Sum(IIf(IsNull([PID.PQty])=True,0,[PID.PQty]))) AS QtyReq, "Rush - 4 Weeks" AS TimeFrame
FROM (ITEM_H LEFT JOIN (INV LEFT JOIN PID ON INV.PID = PID.PID) ON ITEM_H.ItemNo = INV.ItemNo) LEFT JOIN INV_LVLS ON ITEM_H.ItemNo = INV_LVLS.ItemNo
WHERE (((ITEM_H.ItemCat)="RM"))
GROUP BY ITEM_H.ItemNo, "Rush - 4 Weeks", INV_LVLS.MinOH, INV_LVLS.MaxOH
HAVING (((Sum(IIf(IsNull([PID.PQty])=True,0,[PID.PQty])))<[INV_LVLS].[MinOH]));
SQLServer 7.0:
SELECT ITEM_H.ItemNo, ([INV_LVLS].[MaxOH]-Sum(IsNull([PID.PQty],0))) AS QtyReq, 'Rush - 4 Weeks' AS TimeFrame
FROM (ITEM_H LEFT JOIN (INV LEFT JOIN PID ON INV.PID = PID.PID) ON ITEM_H.ItemNo = INV.ItemNo) LEFT JOIN INV_LVLS ON ITEM_H.ItemNo = INV_LVLS.ItemNo
WHERE (((ITEM_H.ItemCat)='RM'))
GROUP BY ITEM_H.ItemNo, 'Rush - 4 Weeks', INV_LVLS.MinOH, INV_LVLS.MaxOH
HAVING ((Sum(IsNull([PID.PQty],0))<[INV_LVLS].[MinOH]))
I'm sure my problem lies within the Sum(IsNull(... portion of my query but I have no idea how to correct my problem. Any help would be greatly appreciated.
Thank You,
Sam Guffey
-
This will give you a syntax error: [PID.PQty]
I think you may want inner joins instead of left joins, but I have no way to know that for sure.
Try this out:
SELECT ITEM_H.ItemNo,
INV_LVLS.MaxOH - (Select Sum(IsNull(PID.PQty,0)) From PID Where PID.PID = INV.PID) AS QtyReq,
'Rush - 4 Weeks' AS TimeFrame
FROM ITEM_H
LEFT JOIN INV ON ITEM_H.ItemNo = INV.ItemNo
LEFT JOIN INV_LVLS ON ITEM_H.ItemNo = INV_LVLS.ItemNo
WHERE ITEM_H.ItemCat = 'RM'
GROUP BY ITEM_H.ItemNo, TimeFrame, INV_LVLS.MinOH, INV_LVLS.MaxOH
Having INV_LVLS.MaxOH > (Select Sum(IsNull(PID.PQty,0)) From PID Where PID.PID = INV.PID)
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
Hello and thank you for the suggestions. You are absolutely correct about my error. My code, as posted, results in an error stating that the group by expressions must refer to column names that appear in the select list. If I remove the group by I get invalid column name PID.PQty. I just tried your sample and received the message invalid column name TimeFrame. If I remove the group by and having clauses it does run however so that's a start!
What I'm trying to do is check to see if an item has fallen below it's minimum on hand balance requirements and if so report an order qty that brings it up to its maximum on hand balance. Easy enough but we track everything by it's weight which is associated to a package id (PID) so the true on hand balance for a given Item is the sum of all PIDs containing that Item. If no PIDs exist I still need to place an order but without the LEFT join I drop any items that have no PIDs at all. I'm beginning to think I might have to break this process up into seperate steps.
Again, thanks for your suggestions!
Sam
-
Sorry, I should have left the 'Rush - 4 Weeks' in the query instead of using TimeFrame.
You can use the alias in the Orber By but not in the Group By.
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
I believe you should be able to use something like this....
SELECT ITEM_H.ItemNo
, INV_LVLS.MaxOH - Sum(IsNull(PID.PQty, 0)) AS QtyReq
, "Rush - 4 Weeks" AS TimeFrame
FROM (ITEM_H
LEFT JOIN (INV
LEFT JOIN PID ON INV.PID = PID.PID)
ON ITEM_H.ItemNo = INV.ItemNo)
LEFT JOIN INV_LVLS ON ITEM_H.ItemNo = INV_LVLS.ItemNo
WHERE ITEM_H.ItemCat = "RM"
GROUP BY ITEM_H.ItemNo, "Rush - 4 Weeks", INV_LVLS.MinOH, INV_LVLS.MaxOH
HAVING Sum(IsNull(PID.PQty,0)) < INV_LVLS.MinOH
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB EngineerThis posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
-
Mr. Johnson,
Thanks for the suggestion! I've tried it and I'm still getting "GROUP BY expressions must refer to column names that appear in the select list". I have to admit that this is the first time I've come across something I could pull off in access but not T-SQL. I assume it's Access' IIF function that's making all of the difference here but I can't figure out a way to duplicate that functionality on the server. Usually it's the other way around. Again, thanks for your help!
Sam Guffey
-
OK, I'll try
It is this what you are looking for?
SELECT h.ItemNo, l.MaxOH - SUM(ISNULL(p.PQty,0)) AS QtyReq, 'Rush - 4 Weeks' AS TimeFrame
FROM ITEM_H h LEFT JOIN INV i ON h.ItemNo = i.ItemNo
LEFT JOIN PID p ON i.PID = p.PID
LEFT JOIN INV_LVLS l ON h.ItemNo = l.ItemNo
WHERE h.ItemCat = 'RM'
GROUP BY h.ItemNo, l.MinOH, l.MaxOH
HAVING SUM(ISNULL(p.PQty,0)) < l.MinOH
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
By Jove Frank I think you've got it! I see the join change but I also noticed when I changed my code that without the alias' I get an error. What's the secret to your success here if I may ask.
Thanks again to all of you for your assistance!
Sam
-
Frank,
I had written it that way at first and then I thought about the way that the join was being done in Access and changed it back to that join. I'm still curious why mine would give the group by error and yours doesn't. I don't see anything really different in the two other than the join. Maybe I'm just being blind!
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB EngineerThis posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
-
Gary,
the solution I posted does not include the 'Rush - 4 weeks' AS TimeFrame in the GROUP BY clause and has a slightly different placement of the brackets (although I'm not sure if that is a huge difference)
Anyway, finally we found a solution
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Frank,
Now that I actually READ the error that was posted I see that your were correct in leaving the listeral out of the Group By Clause. Of course had I been thinking I would have removed it myself! That's what lack of sleep and too much work will do to you I guess!
I'm off to bed. Have a great weekend!
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB EngineerThis posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
-
Hey, today is Friday !!!
Are you a 4 days a week worker ?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Don't I wish! More like 6 days a week!
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB EngineerThis posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply