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)