SQL Issue

[daniel50096230]

I have the following sql 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?

[jp140768]

I'm not really sure what you're trying to achieve here, but instead of having a sub query, could you replace it with an inner join? Refer to the table in the first From statement as a (for example), and the second table as b.

eg:

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

Where did the T.ErrorType come from or is this a typo?

HTH

JP

[dglienna]

What DB are you using?

[ComITSolutions]

change query like below and try

 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')) "
                objDB.OpenDataSet(ds, sql1)
                ds.Tables(0).TableName = "InvtDetails"