Please help with Select Statement (Zip File)
in [Programming SQL]
|
Prev: question on indexing
Next: Query Help -
From: pvong on 22 Jul 2008 17:37 After digging around, I found the source of the problem and I don't know how to fix it. I've recreated my DB and it's in this zip file. The zip file is here and it's really small. http://www5.upload2.net/download/6e8cfbfa2d31009e2afe94e46b67a255/48864815/YAHQQhodIvOktxg/Test.zip I just created 3 little table with no more than 2-3 columns just so I can show you what's going on. I had this on SQL 2005. If you load this DB and run just this Select Statement: SELECT MoxyOrder.OrderID, CONVERT(VarChar, MoxyOrder.OrderDate, 23) AS TradeDate, MoxyOrder.Symbol, SUM(MoxyAllocation.AllocQty) AS NumShares FROM MoxyOrder INNER JOIN MoxyAllocation ON MoxyOrder.OrderID = MoxyAllocation.OrderID GROUP BY MoxyOrder.OrderID, CONVERT(VarChar, MoxyOrder.OrderDate, 23), MoxyOrder.Symbol, MoxyAllocation.OmnibusID HAVING (MoxyAllocation.OmnibusID = 3) You'll see that the NumShares = 1095 Now if you just attach the MoxyFill table: SELECT MoxyOrder.OrderID, CONVERT(VarChar, MoxyOrder.OrderDate, 23) AS TradeDate, MoxyOrder.Symbol, SUM(MoxyAllocation.AllocQty) AS NumShares FROM MoxyOrder INNER JOIN MoxyAllocation ON MoxyOrder.OrderID = MoxyAllocation.OrderID INNER JOIN MoxyFill ON MoxyOrder.OrderID = MoxyFill.OrderID GROUP BY MoxyOrder.OrderID, CONVERT(VarChar, MoxyOrder.OrderDate, 23), MoxyOrder.Symbol, MoxyAllocation.OmnibusID HAVING (MoxyAllocation.OmnibusID = 3) You'll see that the NumShares = 6570 This is my problem. I don't want NumShares to = 6570. I want it to stay at 1095. The reason I need to use the MoxyFill table is because I need to grab the last Filled time as seen in this Select: SELECT MoxyOrder.OrderID, CONVERT(VarChar, MoxyOrder.OrderDate, 23) AS TradeDate, MoxyOrder.Symbol, SUM(MoxyAllocation.AllocQty) AS NumShares, MAX(MoxyFill.FillDate) AS ExecTime FROM MoxyOrder INNER JOIN MoxyAllocation ON MoxyOrder.OrderID = MoxyAllocation.OrderID INNER JOIN MoxyFill ON MoxyOrder.OrderID = MoxyFill.OrderID GROUP BY MoxyOrder.OrderID, CONVERT(VarChar, MoxyOrder.OrderDate, 23), MoxyOrder.Symbol, MoxyAllocation.OmnibusID HAVING (MoxyAllocation.OmnibusID = 3) The above gets me exactly what I need except for he fact that my NumShares is now wrong. Can you help me? I'm really stuck. Thanks!
From: Erland Sommarskog on 22 Jul 2008 18:27 pvong (phillip*at*yahoo*dot*com) writes: > After digging around, I found the source of the problem and I don't know > how to fix it. I've recreated my DB and it's in this zip file. The zip > file is here and it's really small. Unfortunately, I get "The connection to the server was reset while the page was loading." when I try to access it. > Now if you just attach the MoxyFill table: > > SELECT MoxyOrder.OrderID, CONVERT(VarChar, MoxyOrder.OrderDate, 23) AS > TradeDate, MoxyOrder.Symbol, SUM(MoxyAllocation.AllocQty) > AS NumShares > FROM MoxyOrder INNER JOIN > MoxyAllocation ON MoxyOrder.OrderID = > MoxyAllocation.OrderID INNER JOIN > MoxyFill ON MoxyOrder.OrderID = MoxyFill.OrderID > GROUP BY MoxyOrder.OrderID, CONVERT(VarChar, MoxyOrder.OrderDate, 23), > MoxyOrder.Symbol, MoxyAllocation.OmnibusID > HAVING (MoxyAllocation.OmnibusID = 3) > > You'll see that the NumShares = 6570 Supposedly this is because is because there are more the one row per order in MoxyFill. And since there supposedly is also more than one row per order in the allocation table, you get a cartesian product between Fills and Allocation. > This is my problem. I don't want NumShares to = 6570. I want it to > stay at 1095. The reason I need to use the MoxyFill table is because I > need to grab the last Filled time as seen in this Select: > > SELECT MoxyOrder.OrderID, CONVERT(VarChar, MoxyOrder.OrderDate, 23) AS > TradeDate, MoxyOrder.Symbol, SUM(MoxyAllocation.AllocQty) AS NumShares, > MAX(MoxyFill.FillDate) AS ExecTime > FROM MoxyOrder INNER JOIN > MoxyAllocation ON MoxyOrder.OrderID = > MoxyAllocation.OrderID INNER JOIN > MoxyFill ON MoxyOrder.OrderID = MoxyFill.OrderID > GROUP BY MoxyOrder.OrderID, CONVERT(VarChar, MoxyOrder.OrderDate, 23), > MoxyOrder.Symbol, MoxyAllocation.OmnibusID > HAVING (MoxyAllocation.OmnibusID = 3) I moved the two aggregations to CTEs (common table expressions), and then I join with these. I also cleaned up the query and introduced aliases for better legibility. WITH MaxMoxyFill (OrderID, ExecTime) AS ( SELECT OrderID, MAX(FillDate) FROM MoxyFill GROUP BY OrderID ), WITH MoxyTotalAlloc (OrderID, NumShares) AS ( SELECT OrderID, SUM(AllocQty) FROM MoxyAllocation GROUP BY OrderID ), SELECT MO.OrderID, CONVERT(VarChar, MO.OrderDate, 23) AS TradeDate, MO.Symbol, MTA.NumShares, MMF.ExecTime FROM MoxyOrder MO JOIN MoxyTotalAlloc MTA ON MO.OrderID = MA.OrderID JOIN MaxMoxyFill MMF ON MO.OrderID = MMF.OrderID WHERE MA.OmnibusID = 3 -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: pvong on 22 Jul 2008 20:39 When I ran this in SQL Server Mgmt Studio, I get this message and I did make sure it's running this query against correct DB. Msg 156, Level 15, State 1, Line 6 Incorrect syntax near the keyword 'WITH'. Msg 319, Level 15, State 1, Line 6 Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon. Msg 102, Level 15, State 1, Line 10 Incorrect syntax near ','. "pvong" <phillip*at*yahoo*dot*com> wrote in message news:e5H8JLE7IHA.1204(a)TK2MSFTNGP04.phx.gbl... > After digging around, I found the source of the problem and I don't know > how to fix it. I've recreated my DB and it's in this zip file. The zip > file is here and it's really small. > http://www5.upload2.net/download/6e8cfbfa2d31009e2afe94e46b67a255/48864815/YAHQQhodIvOktxg/Test.zip > I just created 3 little table with no more than 2-3 columns just so I can > show you what's going on. I had this on SQL 2005. > > If you load this DB and run just this Select Statement: > > SELECT MoxyOrder.OrderID, CONVERT(VarChar, MoxyOrder.OrderDate, 23) AS > TradeDate, MoxyOrder.Symbol, SUM(MoxyAllocation.AllocQty) > AS NumShares > FROM MoxyOrder INNER JOIN > MoxyAllocation ON MoxyOrder.OrderID = > MoxyAllocation.OrderID > GROUP BY MoxyOrder.OrderID, CONVERT(VarChar, MoxyOrder.OrderDate, 23), > MoxyOrder.Symbol, MoxyAllocation.OmnibusID > HAVING (MoxyAllocation.OmnibusID = 3) > > You'll see that the NumShares = 1095 > > Now if you just attach the MoxyFill table: > > SELECT MoxyOrder.OrderID, CONVERT(VarChar, MoxyOrder.OrderDate, 23) AS > TradeDate, MoxyOrder.Symbol, SUM(MoxyAllocation.AllocQty) > AS NumShares > FROM MoxyOrder INNER JOIN > MoxyAllocation ON MoxyOrder.OrderID = > MoxyAllocation.OrderID INNER JOIN > MoxyFill ON MoxyOrder.OrderID = MoxyFill.OrderID > GROUP BY MoxyOrder.OrderID, CONVERT(VarChar, MoxyOrder.OrderDate, 23), > MoxyOrder.Symbol, MoxyAllocation.OmnibusID > HAVING (MoxyAllocation.OmnibusID = 3) > > You'll see that the NumShares = 6570 > This is my problem. I don't want NumShares to = 6570. I want it to stay > at 1095. The reason I need to use the MoxyFill table is because I need to > grab the last Filled time as seen in this Select: > > SELECT MoxyOrder.OrderID, CONVERT(VarChar, MoxyOrder.OrderDate, 23) AS > TradeDate, MoxyOrder.Symbol, SUM(MoxyAllocation.AllocQty) AS NumShares, > MAX(MoxyFill.FillDate) AS ExecTime > FROM MoxyOrder INNER JOIN > MoxyAllocation ON MoxyOrder.OrderID = > MoxyAllocation.OrderID INNER JOIN > MoxyFill ON MoxyOrder.OrderID = MoxyFill.OrderID > GROUP BY MoxyOrder.OrderID, CONVERT(VarChar, MoxyOrder.OrderDate, 23), > MoxyOrder.Symbol, MoxyAllocation.OmnibusID > HAVING (MoxyAllocation.OmnibusID = 3) > > The above gets me exactly what I need except for he fact that my NumShares > is now wrong. > > Can you help me? I'm really stuck. > > Thanks! > > > >
From: Tom Cooper on 23 Jul 2008 00:04 I can't download your file either, but try the following slight correction to Erland's query: WITH MaxMoxyFill (OrderID, ExecTime) AS ( SELECT OrderID, MAX(FillDate) FROM MoxyFill GROUP BY OrderID ), MoxyTotalAlloc (OrderID, NumShares) AS ( SELECT OrderID, SUM(AllocQty) FROM MoxyAllocation WHERE OmnibusID = 3 GROUP BY OrderID ) SELECT MO.OrderID, CONVERT(VarChar, MO.OrderDate, 23) AS TradeDate, MO.Symbol, MTA.NumShares, MMF.ExecTime FROM MoxyOrder MO JOIN MoxyTotalAlloc MTA ON MO.OrderID = MTA.OrderID JOIN MaxMoxyFill MMF ON MO.OrderID = MMF.OrderID (I removed the extra WITH, removed the comma at the end of the CTE declarations, and moved the check on OmnibusID to the MoxyTotalAlloc CTE.) When you run this, if this statement is not the first (or only) statement in the batch, make sure the previous statement before the WITH is terminated with a ; That is, PRINT 'Running my test' WITH MaxMoxyFill (OrderID, ExecTime) AS (... will give you syntax errors, but PRINT 'Running my test'; WITH MaxMoxyFill (OrderID, ExecTime) AS (... will be OK. Tom "pvong" <phillip*at*yahoo*dot*com> wrote in message news:%2305wuwF7IHA.4192(a)TK2MSFTNGP06.phx.gbl... > When I ran this in SQL Server Mgmt Studio, I get this message and I did > make sure it's running this query against correct DB. > > Msg 156, Level 15, State 1, Line 6 > > Incorrect syntax near the keyword 'WITH'. > > Msg 319, Level 15, State 1, Line 6 > > Incorrect syntax near the keyword 'with'. If this statement is a common > table expression or an xmlnamespaces clause, the previous statement must > be terminated with a semicolon. > > Msg 102, Level 15, State 1, Line 10 > > Incorrect syntax near ','. > > > > "pvong" <phillip*at*yahoo*dot*com> wrote in message > news:e5H8JLE7IHA.1204(a)TK2MSFTNGP04.phx.gbl... >> After digging around, I found the source of the problem and I don't know >> how to fix it. I've recreated my DB and it's in this zip file. The zip >> file is here and it's really small. >> http://www5.upload2.net/download/6e8cfbfa2d31009e2afe94e46b67a255/48864815/YAHQQhodIvOktxg/Test.zip >> I just created 3 little table with no more than 2-3 columns just so I can >> show you what's going on. I had this on SQL 2005. >> >> If you load this DB and run just this Select Statement: >> >> SELECT MoxyOrder.OrderID, CONVERT(VarChar, MoxyOrder.OrderDate, 23) >> AS TradeDate, MoxyOrder.Symbol, SUM(MoxyAllocation.AllocQty) >> AS NumShares >> FROM MoxyOrder INNER JOIN >> MoxyAllocation ON MoxyOrder.OrderID = >> MoxyAllocation.OrderID >> GROUP BY MoxyOrder.OrderID, CONVERT(VarChar, MoxyOrder.OrderDate, 23), >> MoxyOrder.Symbol, MoxyAllocation.OmnibusID >> HAVING (MoxyAllocation.OmnibusID = 3) >> >> You'll see that the NumShares = 1095 >> >> Now if you just attach the MoxyFill table: >> >> SELECT MoxyOrder.OrderID, CONVERT(VarChar, MoxyOrder.OrderDate, 23) >> AS TradeDate, MoxyOrder.Symbol, SUM(MoxyAllocation.AllocQty) >> AS NumShares >> FROM MoxyOrder INNER JOIN >> MoxyAllocation ON MoxyOrder.OrderID = >> MoxyAllocation.OrderID INNER JOIN >> MoxyFill ON MoxyOrder.OrderID = MoxyFill.OrderID >> GROUP BY MoxyOrder.OrderID, CONVERT(VarChar, MoxyOrder.OrderDate, 23), >> MoxyOrder.Symbol, MoxyAllocation.OmnibusID >> HAVING (MoxyAllocation.OmnibusID = 3) >> >> You'll see that the NumShares = 6570 >> This is my problem. I don't want NumShares to = 6570. I want it to stay >> at 1095. The reason I need to use the MoxyFill table is because I need >> to grab the last Filled time as seen in this Select: >> >> SELECT MoxyOrder.OrderID, CONVERT(VarChar, MoxyOrder.OrderDate, 23) >> AS TradeDate, MoxyOrder.Symbol, SUM(MoxyAllocation.AllocQty) AS >> NumShares, >> MAX(MoxyFill.FillDate) AS ExecTime >> FROM MoxyOrder INNER JOIN >> MoxyAllocation ON MoxyOrder.OrderID = >> MoxyAllocation.OrderID INNER JOIN >> MoxyFill ON MoxyOrder.OrderID = MoxyFill.OrderID >> GROUP BY MoxyOrder.OrderID, CONVERT(VarChar, MoxyOrder.OrderDate, 23), >> MoxyOrder.Symbol, MoxyAllocation.OmnibusID >> HAVING (MoxyAllocation.OmnibusID = 3) >> >> The above gets me exactly what I need except for he fact that my >> NumShares is now wrong. >> >> Can you help me? I'm really stuck. >> >> Thanks! >> >> >> >> > >
From: pvong on 23 Jul 2008 09:32 That's perfect!!! Thanks guys!!!! "Tom Cooper" <tomcooper(a)comcast.no.spam.please.net> wrote in message news:uivM2kH7IHA.1204(a)TK2MSFTNGP04.phx.gbl... >I can't download your file either, but try the following slight correction >to Erland's query: > > WITH MaxMoxyFill (OrderID, ExecTime) AS ( > SELECT OrderID, MAX(FillDate) > FROM MoxyFill > GROUP BY OrderID > ), > MoxyTotalAlloc (OrderID, NumShares) AS ( > SELECT OrderID, SUM(AllocQty) > FROM MoxyAllocation > WHERE OmnibusID = 3 > GROUP BY OrderID > ) > SELECT MO.OrderID, CONVERT(VarChar, MO.OrderDate, 23) AS TradeDate, > MO.Symbol, MTA.NumShares, MMF.ExecTime > FROM MoxyOrder MO > JOIN MoxyTotalAlloc MTA ON MO.OrderID = MTA.OrderID > JOIN MaxMoxyFill MMF ON MO.OrderID = MMF.OrderID > > (I removed the extra WITH, removed the comma at the end of the CTE > declarations, and moved the check on OmnibusID to the MoxyTotalAlloc CTE.) > > When you run this, if this statement is not the first (or only) statement > in the batch, make sure the previous statement before the WITH is > terminated with a ; That is, > > PRINT 'Running my test' > WITH MaxMoxyFill (OrderID, ExecTime) AS (... > > will give you syntax errors, but > > PRINT 'Running my test'; > WITH MaxMoxyFill (OrderID, ExecTime) AS (... > > will be OK. > > Tom > "pvong" <phillip*at*yahoo*dot*com> wrote in message > news:%2305wuwF7IHA.4192(a)TK2MSFTNGP06.phx.gbl... >> When I ran this in SQL Server Mgmt Studio, I get this message and I did >> make sure it's running this query against correct DB. >> >> Msg 156, Level 15, State 1, Line 6 >> >> Incorrect syntax near the keyword 'WITH'. >> >> Msg 319, Level 15, State 1, Line 6 >> >> Incorrect syntax near the keyword 'with'. If this statement is a common >> table expression or an xmlnamespaces clause, the previous statement must >> be terminated with a semicolon. >> >> Msg 102, Level 15, State 1, Line 10 >> >> Incorrect syntax near ','. >> >> >> >> "pvong" <phillip*at*yahoo*dot*com> wrote in message >> news:e5H8JLE7IHA.1204(a)TK2MSFTNGP04.phx.gbl... >>> After digging around, I found the source of the problem and I don't know >>> how to fix it. I've recreated my DB and it's in this zip file. The zip >>> file is here and it's really small. >>> http://www5.upload2.net/download/6e8cfbfa2d31009e2afe94e46b67a255/48864815/YAHQQhodIvOktxg/Test.zip >>> I just created 3 little table with no more than 2-3 columns just so I >>> can show you what's going on. I had this on SQL 2005. >>> >>> If you load this DB and run just this Select Statement: >>> >>> SELECT MoxyOrder.OrderID, CONVERT(VarChar, MoxyOrder.OrderDate, 23) >>> AS TradeDate, MoxyOrder.Symbol, SUM(MoxyAllocation.AllocQty) >>> AS NumShares >>> FROM MoxyOrder INNER JOIN >>> MoxyAllocation ON MoxyOrder.OrderID = >>> MoxyAllocation.OrderID >>> GROUP BY MoxyOrder.OrderID, CONVERT(VarChar, MoxyOrder.OrderDate, 23), >>> MoxyOrder.Symbol, MoxyAllocation.OmnibusID >>> HAVING (MoxyAllocation.OmnibusID = 3) >>> >>> You'll see that the NumShares = 1095 >>> >>> Now if you just attach the MoxyFill table: >>> >>> SELECT MoxyOrder.OrderID, CONVERT(VarChar, MoxyOrder.OrderDate, 23) >>> AS TradeDate, MoxyOrder.Symbol, SUM(MoxyAllocation.AllocQty) >>> AS NumShares >>> FROM MoxyOrder INNER JOIN >>> MoxyAllocation ON MoxyOrder.OrderID = >>> MoxyAllocation.OrderID INNER JOIN >>> MoxyFill ON MoxyOrder.OrderID = MoxyFill.OrderID >>> GROUP BY MoxyOrder.OrderID, CONVERT(VarChar, MoxyOrder.OrderDate, 23), >>> MoxyOrder.Symbol, MoxyAllocation.OmnibusID >>> HAVING (MoxyAllocation.OmnibusID = 3) >>> >>> You'll see that the NumShares = 6570 >>> This is my problem. I don't want NumShares to = 6570. I want it to >>> stay at 1095. The reason I need to use the MoxyFill table is because I >>> need to grab the last Filled time as seen in this Select: >>> >>> SELECT MoxyOrder.OrderID, CONVERT(VarChar, MoxyOrder.OrderDate, 23) >>> AS TradeDate, MoxyOrder.Symbol, SUM(MoxyAllocation.AllocQty) AS >>> NumShares, >>> MAX(MoxyFill.FillDate) AS ExecTime >>> FROM MoxyOrder INNER JOIN >>> MoxyAllocation ON MoxyOrder.OrderID = >>> MoxyAllocation.OrderID INNER JOIN >>> MoxyFill ON MoxyOrder.OrderID = MoxyFill.OrderID >>> GROUP BY MoxyOrder.OrderID, CONVERT(VarChar, MoxyOrder.OrderDate, 23), >>> MoxyOrder.Symbol, MoxyAllocation.OmnibusID >>> HAVING (MoxyAllocation.OmnibusID = 3) >>> >>> The above gets me exactly what I need except for he fact that my >>> NumShares is now wrong. >>> >>> Can you help me? I'm really stuck. >>> >>> Thanks! >>> >>> >>> >>> >> >> > >
|
Pages: 1 Prev: question on indexing Next: Query Help - |