下面的查询将行中的一些数据放到列中:
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. iDB2DCF功能错误异常:'发生未预期的例外状况。类型:系统.访问违规异常,消息:试图读取或写入受保护的内存。这通常表示其他内存已损坏。'”
我有其他的查询可以很好地工作,但是这个不行。我甚至在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
而且很管用,我能做什么?
驱动程序:
客户端访问ODBC驱动程序(32位)12.00.00.00
iSeries访问ODBC驱动程序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
1条答案
按热度按时间t30tvxxf1#
好了,我终于找到了问题所在。由于某种原因,我无法将
CAST
转换为MAX
内部的浮点数。如果我从查询中删除这两个CAST
,异常就消失了,一切都正常了。我不知道为什么会发生这种情况,如果有人能解释一下,这将非常有帮助,因为我需要将这些值转换为浮点数。另外,如果还有SELECT
列,则需要将这些列添加到GROUP BY
语句中。不管怎样,下面是修改后的代码:编辑:找到了转换它们的方法。结果发现我得到了一些空值,一些空白字段,我试图给一个字符串赋值。现在的代码如下: