Need to add a field in a query from new table

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)

Need to add a field in a query from new table

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, adCmdText

The 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, adCmdText

for 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()

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources