SQL Subquery Issue - 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

General SQL Server Forums

New to SQL Server Programming

SQL Subquery Issue

Author

Topic

daniel50096230
Yak Posting Veteran

99 Posts

Posted - 2009-07-27 : 02:29:23

I have the following sql code:

Code:



 sql1 = "SELECT Distinct(TraceID) FROM Trn_SO_Details WHERE SODt='" + 
         InvtDt + "' AND ProdCode='" + ProdCode + "' " 'TStatus Not 
         IN('Processed','PROCESSED') AND T.ErrorType Not 
         IN('Duplicate')"
 sql1 &= " AND TraceID=(select TraceID from Trn_SO Where Status NOT   
         IN('Processed','PROCESSED') AND ErrorType Not 
         IN('Duplicate')) "
                
objDB.OpenDataSet(ds, sql1)
ds.Tables(0).TableName = "InvtDetails"

But I get the following errors:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

If the subquery only return one result,then it works fine,but if the subquery return more than one results, then the error occured,how can I resolve this error?

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-07-27 : 02:31:39

sql1 = "SELECT Distinct(TraceID) FROM Trn_SO_Details WHERE SODt='" +
InvtDt + "' AND ProdCode='" + ProdCode + "' " 'TStatus Not
IN('Processed','PROCESSED') AND T.ErrorType Not
IN('Duplicate')"
sql1 &= " AND TraceID in (select TraceID from Trn_SO Where Status NOT
IN('Processed','PROCESSED') AND ErrorType Not
IN('Duplicate')) "

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-27 : 02:34:12

[code] sql1 = "SELECT Distinct(TraceID) FROM Trn_SO_Details WHERE SODt='" +
InvtDt + "' AND ProdCode='" + ProdCode + "' " 'TStatus Not
IN('Processed','PROCESSED') AND T.ErrorType Not
IN('Duplicate')"
sql1 &= " AND TraceID IN (select TraceID from Trn_SO Where Status NOT
IN('Processed','PROCESSED') AND ErrorType Not
IN('Duplicate')) "

[/code]

Also Refer
http://www.sqlteam.com/article/using-subqueries-to-select-records

-------------------------
R..

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2009-07-27 : 08:13:12

STOP DOING THAT!!!!

use parameterised queries (and SPs if you can) not dynamic SQL.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources