SQL Issue
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 4 of 4

Thread: SQL Issue

  1. #1
    Join Date
    Jan 2009
    Posts
    160

    SQL Issue

    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?

  2. #2
    Join Date
    Jun 2002
    Location
    Clane, Ireland
    Posts
    766

    Re: SQL Issue

    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:

    Code:
    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
    JP

    Please remember to rate all postings.

  3. #3
    Join Date
    Jan 2006
    Location
    Chicago, IL
    Posts
    14,982

    Re: SQL Issue

    What DB are you using?
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  4. #4
    Join Date
    Feb 2008
    Location
    Bangalore
    Posts
    149

    Re: SQL Issue

    change query like below and try
    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')) "
                    objDB.OpenDataSet(ds, sql1)
                    ds.Tables(0).TableName = "InvtDetails"
    Encourage the efforts of fellow members by rating

    Lets not Spoon Feed and create pool of lazy programmers

    - ComIT Solutions

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center