SQL select statement error - 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 select statement error

Author

Topic

daniel50096230
Yak Posting Veteran

99 Posts

Posted - 2009-07-21 : 08:59:23

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 believe that all the fields are correct, so I suspect that my sql statement encounter syntax error.

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-21 : 09:10:30

Hi

Try this...you missed comma after Type column...

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(' ') "

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources