Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2009
    Posts
    9

    Unanswered: sql select statement error

    Basically, I have the following sql select statement in vb.net.


    sql = "SELECT TraceID, TraceNo, Status, ErrorType, IsDeleted,
    IsReaded, count(D.TraceID) As [Type] "
    sql &= " CASE WHEN A.IsArchived = 0 THEN 'Open' ELSE 'Closed' END AS
    IsComplete, A.createddt, "
    sql &= " B.CoName, B.DistID, A.DistID AS DistID2 FROM TRN_Invt A WITH
    (READPAST), Trn_Invt_Details D WITH (READPAST) "
    sql &= " LEFT JOIN TB_Company B WITH (NOLOCK) ON A.DistID=B.DistID
    WHERE ISNULL(A.IsDeleted,0) = 0 AND UPPER(A.Status)
    <>'PROCESSED' "
    sql &= " AND A.TraceID=D.TraceID AND (D.ErrorType IS Not Null) AND
    D.ErrorType Not IN(' ') "




    From the above statement, I would like retrieve several data and the count of Traceid from trn_Invt_Details where the traceid in trn_Invt_Details match with traceid in trn_Invt. By the way, it will also based on the criteria where the field of ErrorType in Trn_Invt_Details is Null or contain empty string. Then it will retrieve out the count of traceid.Finally, all the results will save to dataset in vb.net

    But I get the error "incorrect syntax near CASE"....ANyone can help me?I am sure that all the fields are correct, so I suspect that the error occured in my select statement.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You need a comma before the CASE.

    When debugging inline\ dynamic SQL always ALWAYS view the value of the string variable - it is usually easier to debug from that than your inline code.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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