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)
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.