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.
Author |
Topic |
itchocks
Starting Member
44 Posts |
Posted - 2003-10-15 : 04:58:26
|
Hai, My Database is Ms Sql 7, i am using VB as a front end and devoloping the application. when the i use the below query i am getting the output correctly.rst.Open "select stk.SC01001 as ItemCode,stk.SC01002 as Description,sum(stk.SC01042) as StkQty,(select sum(ST030100.ST03020) From ST030100 Where ST030100.ST03017 = stk.SC01001 And ST030100.ST03015 >='" & Text2.Text & "' And ST030100.ST03015 <='" & Text3.Text & "') as SoldQty from SC010100 stk Group By stk.SC01001,stk.SC01002 order by stk.SC01001", cnn, adOpenStatic, adLockReadOnly, adCmdTextThe below query is the same query as above with an additional field, the additional field is CustomerName, It is in separate table. So I need to link the curtomer Id with the Sales Table and i have to get the output. So i add the query for the customer name in the last of this query. The query is below.rst.Open "select stk.SC01001 as ItemCode,stk.SC01002 as Description,sum(stk.SC01042) as StkQty,(select sum(ST030100.ST03020) From ST030100 Where ST030100.ST03017 = stk.SC01001 And ST030100.ST03015 >='" & Text2.Text & "' And ST030100.ST03015 <='" & Text3.Text & "') as SoldQty,(select SL01001 from SL010100,ST030100 where SL010100.SL01001 = ST030100.ST03001) as CustomerName from SC010100 stk Group By stk.SC01001,stk.SC01002 order by stk.SC01001", cnn, adOpenStatic, adLockReadOnly, adCmdTextfor this query i am getting the below error.Run – time error ‘-2147467259(80004005)’:Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <=, >, >= or when the subquery is used as an expression .Kindly suggest me how to correct the query.Thank you,Chock.Chock |
|
TurdSpatulaWarrior
Starting Member
36 Posts |
Posted - 2003-10-15 : 10:39:09
|
Run the sub-query listed below by itself.select SL01001 from SL010100,ST030100 where SL010100.SL01001 = ST030100.ST03001 An inline sub-query like that isn't permitted to return more than one result. This one seems like it is returning more than one result. From a logical point of view, it makes sense. A row of data can't exist like the horribly crude diagram below shows:result result result result result result result result Ya smell what I'm sayin? |
|
|
drymchaser
Aged Yak Warrior
552 Posts |
Posted - 2003-10-15 : 10:42:24
|
It may be the same name repeated for every matching line in the order details so you could aggregate the field like your SUM(...) using MIN() or MAX() |
|
|
|
|
|
|
|