在查询中使用 MAX(CASE WHEN) 时出现奇怪的异常：DB2.iSeries.iDB2DCFunctionErrorException："发生意外异常

Question

我有以下查询将一些数据从行到列:

string qry3 =
"SELECT TABLENAME1.SLONO AS ORDER, TABLENAME1.SLLNNO AS LINE " +
",  MAX(CASE WHEN TABLENAME2.CZVRNM in ('SLEEVEDEPTH', 'LENGTH') THEN CAST(RTRIM(TABLENAME2.CZREFD) AS FLOAT(53)) END) AS SLEEVE " +
",  MAX(CASE WHEN TABLENAME2.CZVRNM in ('INSTALLATION') THEN CAST(RTRIM(TABLENAME2.CZREFD) AS FLOAT(53)) END) AS DAMPER_AI " +
"FROM LOCATION.LOCATION2.TABLENAME1 TABLENAME1 LEFT JOIN LOCATION.LOCATION2.TABLENAME2 TABLENAME2 " +
"     ON (TABLENAME1.SLONO = TABLENAME2.SPONO AND TABLENAME1.SLLNNO = TABLENAME2.SPLNNO)  " +
"GROUP BY TABLENAME1.SLONO, TABLENAME1.SLLNNO ";

但是当我尝试在以下代码中运行它时:

try
{
conAS400.Open();
iDB2Command command = conAS400.CreateCommand();
command.CommandText = qry3;
command.CommandTimeout = 0;
iDB2DataReader reader = command.ExecuteReader();
dtAS400 = new DataTable();
dtAS400.Load(reader); //<-- The exception occurs here. I can't even catch it
conAS400.Close();
retrieved = true;
}
catch (Exception ex)
{
MessageBox.Show("Could not retrive the information. Exception: " + ex.Message);
retrieved = false;
}
finally
{
conAS400.Close();
}

我得到了这个异常:

"IBM.Data.DB2.iSeries。iDB2DCFunctionErrorException: '意外异常发生。类型:系统。AccessViolationException消息:试图读写受保护的内存。这通常是指示其他内存已损坏…'">

我有其他的查询工作很好，但这一个没有。我甚至尝试在SQL上查询如下(归功于Gordon Linoff提供的代码):

SELECT 
A.IDA, A.SomeInfo,
MAX(CASE WHEN VarName in ('Depth', 'Depth2') THEN VarValue END) AS Depth,
MAX(CASE WHEN VarName in ('Length') THEN VarValue END) AS Length
FROM 
A
LEFT JOIN 
B ON A.IDA = B.IDA
GROUP BY 
A.IDA, A.SomeInfo

它起作用了。我能做什么?

司机:

Client Access ODBC Driver(32位)12.00.00.00

iSeries Access ODBC Driver 12.00.00.00

编辑:这是sql查询没有一个字符串。

SELECT TABLENAME1.SLONO AS ORDER, TABLENAME1.SLLNNO AS LINE, 
MAX(CASE WHEN TABLENAME2.CZVRNM in ('SLEEVEDEPTH', 'LENGTH') THEN CAST(RTRIM(TABLENAME2.CZREFD) AS FLOAT(53)) END) AS SLEEVE, 
MAX(CASE WHEN TABLENAME2.CZVRNM in ('INSTALLATION') THEN CAST(RTRIM(TABLENAME2.CZREFD) AS FLOAT(53)) END) AS DAMPER_AI 
FROM LOCATION.LOCATION2.TABLENAME1 TABLENAME1 LEFT JOIN LOCATION.LOCATION2.TABLENAME2 TABLENAME2 
ON (TABLENAME1.SLONO = TABLENAME2.SPONO AND TABLENAME1.SLLNNO = TABLENAME2.SPLNNO)  
GROUP BY TABLENAME1.SLONO, TABLENAME1.SLLNNO 

Answer 1

好了，我终于找到问题所在了。由于某种原因，我不能让CAST浮在MAX里。如果我从查询中删除CAST，则异常消失，一切正常。我不确定为什么会发生这种情况，如果有人能解释这一点，这将是非常有帮助的，因为我需要将这些值转换为浮动。此外，如果我SELECT更多的列，则有必要将这些列添加到GROUP BY语句中。无论如何，下面是修改后的代码:

编辑:找到了转换它们的方法。我得到了一些空字段，我试图给一个字符串赋值。下面是代码:

SELECT TABLENAME1.SLONO AS ORDER, TABLENAME1.SLLNNO AS LINE, TABLENAME1.PROMISEDT,
CAST(MAX(CASE WHEN RTRIM(TABLENAME2.CZREFD) IS NULL OR RTRIM(TABLENAME2.CZREFD) = '' THEN '0.000' WHEN TABLENAME2.CZVRNM = 'SLEEVEDEPTH' OR TABLENAME2.CZVRNM = 'LENGTH' THEN RTRIM(TABLENAME2.CZREFD) END) AS FLOAT(53)) AS SLEEVE,  
MAX(CASE WHEN TABLENAME2.CZVRNM in ('INSTALLATION') THEN RTRIM(TABLENAME2.CZREFD) END) AS DAMPER_AI 
FROM LOCATION.LOCATION2.TABLENAME1 TABLENAME1 LEFT JOIN LOCATION.LOCATION2.TABLENAME2 TABLENAME2 
ON (TABLENAME1.SLONO = TABLENAME2.SPONO AND TABLENAME1.SLLNNO = TABLENAME2.SPLNNO)  
GROUP BY TABLENAME1.SLONO, TABLENAME1.SLLNNO, TABLENAME1.PROMISEDT --remember to add every column from the SELECT here