1) 你看出区别在哪里了么？ 结果会一样么？
SELECT
(select first 1 longname from ivctrallocpurch a join jcivloc l on a.ivlocid=l.ivlocid and a.ctrid=A.ctrid) as mill
FROM CTR A
JOIN CTRSMRY CS ON (A.CTRID=CS.CTRID)
WHERE A.CTRID=3161
和
SELECT
(select first 1 longname from ivctrallocpurch a join jcivloc l on a.ivlocid=l.ivlocid and a.ctrid=3161) as mill
FROM CTR A
JOIN CTRSMRY CS ON (A.CTRID=CS.CTRID)
WHERE A.CTRID=3161
结果为什么不一样呢？
2） Integer overflow. The result of an integer operation caused the most significant bit of the result to carry.
select sum(rcpt.artothome * rate) as amt
from cashreceipt rcpt
join homerate rate on rcpt.currencyid=rate.currencyid
分析：
artothome @ arheader is : numeric(18,4)
rate @ .. is : numeric(10,10)
改进1 (not work)
select sum(cast(rcpt.artothome * rate as numeric(18,4))) as amt
from cashreceipt rcpt
join homerate rate on rcpt.currencyid=rate.currencyid
改进2 (works)
select sum(cast(cast(rcpt.artothome as numeric(18,4)) * cast(rate as numeric(18,4)) as numeric(18,4))) as amt
from cashreceipt rcpt
join homerate rate on rcpt.currencyid=rate.currencyid
reason, forward from http://www.firebirdfaq.org/faq207/
Integer overflow. The result of an integer operation caused the most significant bit of the result to carry.
Short explanation:
If you use fixed precision datatypes (smallint, integer, bigint, decimal and numeric), it is possible that the result of calculation doesn't fit the datatype. Try casting the values in complex expressions as double precision and see whether the error goes away. If it works and you don't care about being too precise, you can leave it at that. Otherwise you need to check every operation and calculate the result.
Details:
Here's an example: if you multiply 9.12 with 8.11 (both numeric(18,2)) you would get 73.9632. If Firebird would store that into numeric(18,2) datatype, we would lose 0.0032. Doesn't look much, but when you have complex calculations, you can easily loose thousands (dollars or euros). Therefore, the result is stored in numeric(18,4).
Problems are rarely seen with such low precision as 2. Let's use some bigger precision. For example, numeric(18,6) times numeric(18,6) yields numeric(18,12) result, meaning that maximal value it can store is 9223372.036854775807. If (for example) you wish to keep only 6 digits of precision, you could use something like:
cast(value1 as numeric(18,3)) * cast(value2 as numeric(18,3))
which would yield numeric(18,6) result, but it is quite possible that you would get more accurate result by casting to double:
cast(cast(value1 as double precision) * cast(value2 as double precision) as numeric(18,6))
Also, if you have mixed multiplications and divisions it helps to change the order of operations, so that the overflow doesn't happen.
String sql = "select mbrid from jcmbr where reference4=?";
假如没有记录
DynaBean aBean = CxcDataModule.getInstance().getRow(sql, new Object[]{reference4});
如果直接用aBean.get("reference4") 出错；
错误的原因是aBean 为null
Object obj = {spring jdbctemplate}.queryForObject(sql, new Object[]{reference4}, Integer.class);
if (obj instanceof Integer)
System.out.println("(Integer)obj>>>>>>>>>>" + (Integer)obj);
else if (obj instanceof Map)
System.out.println("(Integer)obj>>>>>>>>>>" + ((Map)obj).get("REFERENCE4"));
真是的流程是 if 分支
如果有数据，这是对的
如果没有数据，直接出错，因为有个假定有Integer值存在
int value = JcDataModuleUtils.getJdbcTemplate().queryForInt(sql, new Object[]{reference4});
出错原因是假定一定会有一个int值返回
15:09:00,438 ERROR [STDERR] Caused by: org.springframework.dao.IncorrectResultSizeDataAccessException: Incorrect result size:
expected 1, actual 0
15:09:00,438 ERROR [STDERR] at org.springframework.dao.support.DataAccessUtils.requiredUniqueResult(DataAccessUtils.java:
66)
15:09:00,469 ERROR [STDERR] at org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:620)
15:09:00,469 ERROR [STDERR] at org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:629)
15:09:00,469 ERROR [STDERR] at org.springframework.jdbc.core.JdbcTemplate.queryForInt(JdbcTemplate.java:656)