Hi,

I have a problem with executing an Any() linq statement on DB2, it's a pretty straightforward query:

var exists = linq.Opdracht.Any(o => o.NummerLm == 1 && o.Opdrachtnr == 1);

The resulting error is this:

SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException occurred
  Message=An exception was caught during the execution of a retrieval query: ERROR [42601] [IBM][DB2/AIX64] SQL0104N  An unexpected token "END-OF-STATEMENT" was found following "HTNR" = ?)))) THEN 1".  Expected tokens may include:  "END".  SQLSTATE=42601
. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.
  Source=SD.LLBLGen.Pro.ORMSupportClasses.NET20
  RuntimeBuild=11292011
  RuntimeVersion=3.1.0.0
  QueryExecuted=
    Query: SELECT CASE WHEN  EXISTS (SELECT "LPLA_1"."WERKBONNUMMER" AS "Werkbonnummer" FROM "RDCINST1"."OPDRACHT"  "LPLA_1" WHERE ( ( ( "LPLA_1"."NUMMER_LM" = ?) AND ( "LPLA_1"."OPDRACHTNR" = ?)))) THEN 1; ELSE 0; END CASE AS "LPFA_1" FROM "RDCINST1"."OPDRACHT"  "LPLA_1" FETCH FIRST 1 ROWS ONLY OPTIMIZE FOR 1 ROWS
    Parameter: @p1 : Int32. Length: 4. Precision: 10. Scale: 0. Direction: Input. Value: 943588.
    Parameter: @p2 : Int32. Length: 4. Precision: 10. Scale: 0. Direction: Input. Value: 61.

  StackTrace:
       at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Query\RetrievalQuery.cs:line 136
  InnerException: IBM.Data.DB2.DB2Exception
       Message=ERROR [42601] [IBM][DB2/AIX64] SQL0104N  An unexpected token "END-OF-STATEMENT" was found following "HTNR" = ?)))) THEN 1".  Expected tokens may include:  "END".  SQLSTATE=42601

       Source=IBM.Data.DB2
       ErrorCode=-2147467259
       StackTrace:
            at IBM.Data.DB2.DB2Command.ExecuteReaderObject(CommandBehavior behavior, String method, DB2CursorType reqCursorType, Boolean abortOnOptValueChg, Boolean skipDeleted, Boolean isResultSet, Int32 maxRows)
            at IBM.Data.DB2.DB2Command.ExecuteReaderObject(CommandBehavior behavior, String method)
            at IBM.Data.DB2.DB2Command.ExecuteReader(CommandBehavior behavior)
            at IBM.Data.DB2.DB2Command.ExecuteDbDataReader(CommandBehavior behavior)
            at SD.Tools.OrmProfiler.Interceptor.ProfilerDbCommand.ExecuteDbDataReader(CommandBehavior behavior)
            at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Query\RetrievalQuery.cs:line 112
       InnerException: 

It's easy to solve with a Count() > 0, but still it seems like a bug, or am I just using a DB2 version that's too old?

-W

When you run the query without the ';' in the CASE statement (directly onto DB2) it will likely return rows, could you check that? I wonder why the ';' is there... will check.

(edit). the ';' have to be there: http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=%2Fcom.ibm.db2.udb.admin.doc%2Fdoc%2Fr0005646.htm

very odd that it gives an 'end of statement'. Don't know how old your DB2 version is btw...

Otis wrote:

When you run the query without the ';' in the CASE statement (directly onto DB2) it will likely return rows, could you check that? I wonder why the ';' is there... will check.

(edit). the ';' have to be there: http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=%2Fcom.ibm.db2.udb.admin.doc%2Fdoc%2Fr0005646.htm

very odd that it gives an 'end of statement'. Don't know how old your DB2 version is btw...

Nope, running the query without ';' in DB2 gives the following error:

Error:ERROR [42601] [IBM][DB2/AIX64] SQL0104N An unexpected token "AS" was found following "EN 1 ELSE 0 END CASE". Expected tokens may include: ",". SQLSTATE=42601

However when I just remove the last CASE statement as well as the semicolons it seems to work...

wtijsma wrote:

Otis wrote:

When you run the query without the ';' in the CASE statement (directly onto DB2) it will likely return rows, could you check that? I wonder why the ';' is there... will check.

(edit). the ';' have to be there: http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=%2Fcom.ibm.db2.udb.admin.doc%2Fdoc%2Fr0005646.htm

very odd that it gives an 'end of statement'. Don't know how old your DB2 version is btw...

Nope, running the query without ';' in DB2 gives the following error:

Error:ERROR [42601] [IBM][DB2/AIX64] SQL0104N An unexpected token "AS" was found following "EN 1 ELSE 0 END CASE". Expected tokens may include: ",". SQLSTATE=42601

However when I just remove the last CASE statement as well as the semicolons it seems to work...

I'm in no way a DB2 expert, but it seems the link you specified is only for using CASE in an update statement (the docs state it can only be used in a stored procedure), in a normal select you don't need 'END CASE' you just specify 'END' (I think this is on par with ANSI SQL-92?)

Hmm, I think you're right: http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=%2Fcom.ibm.db2.udb.admin.doc%2Fdoc%2Fr0023458.htm

If you remove the 'case' after 'end' the query works? It's defined as the IIF() function mapping, so it's easy to fix.

However I'm very confused about the manual page linked above: no END CASE, just END, but also... no ';'...

edit: Ah ! found the explanation: http://www.sqlpl-guide.com/CASE

It's important to know that the CASE statement is different to the CASE expression which will be used in SELECT statements.

I.o.w.: the function mappings we have now are wrong in some cases (pun intended). I'll update the DB2 DQE for you for testing.

Could you try the attached dll?

Otis wrote:

Could you try the attached dll?

Sorry for the late response, I can't test the DLL as we're still on version 3.1, and we're not really in a position at the moment to update to 3.5...

I'll upload an updated v3.1 on monday.

Please try the attached DQE (for v3.1)

Otis wrote:

Please try the attached DQE (for v3.1)

It works! Thanks

wtijsma wrote:

Otis wrote:

Please try the attached DQE (for v3.1)

It works! Thanks

Will the fix be included in a new 3.1 build? If yes, when is the next build planned?

3.1 is in maintenance mode, so there will only be new builds when a fix was released and not that frequently. We'll release a new build tomorrow for v3.1, as your fix hasn't been available online yet.

We use 1 'current' code base per version so your fix is in all following builds (you have a release build, so you can keep using that)

Otis wrote:

3.1 is in maintenance mode, so there will only be new builds when a fix was released and not that frequently. We'll release a new build tomorrow for v3.1, as your fix hasn't been available online yet.

We use 1 'current' code base per version so your fix is in all following builds (you have a release build, so you can keep using that)

Thanks!