nvl2(count(*),count(*),0)提示ORA-01722无效数字
最近在公司接触项目部署和客户化工作，最让我郁闷的不是客户长期的更改，而是不同环境和结果数据产生的异常。因为这样的异常在我本地是没法测试的，只能通过正式库的数据和场景不断的推断，在本地进行环境模拟测试，首先场景模拟就很让我头疼，因为场景模拟就代表这你对该异常有一个初步的判断，但是对于sql语句提示你执行错误，你怎能知道哪里错误，为什么错误，而且在自己本地明明就没有错误，这时候只想说，天，你把我送走吧。
言归正传，直接上sql：
sql.append("select pmi.authority_id, pmi.pe_id, p.pe_visit_id, pmi.name, pmi.sex, pmi.date_of_birth, pmi.id_no, pmi.mobile, pmi.phone_number, p.pe_source, p.pe_pre_date , p.set_name_cust , p.audit_doctor_name as 医生姓名, p.result_status , fp.followup_fact_time, fp.OPERATOR_NAME as username, fp.TASK_STATUS, fp.TEL_STATUS, fp.id as taskId, nvl2(ppid.count,ppid.count,0) as count from phyexam.PE_VISIT p join phyexam.PE_MASTER_INDEX pmi on p.authority_id = pmi.authority_id and p.pe_id = pmi.pe_id left join interface.pat_master_index ipmi on ipmi.authority_id=pmi.authority_id and ipmi.pid=pmi.patient_id left join security.security_ihe_patient_id_list spl on spl.pid=ipmi.pid and spl.authority_id=ipmi.authority_id left join security.security_user_baseinfo sub on sub.id=spl.security_user_baseinfo_id left join (select a.authority_id,a.pe_id,a.pe_visit_id, nvl2(count(*),count(*),0) as count from phyexam.pe_result_dict a where a.abnormal_indicator ='H' group by a.authority_id,a.pe_id,a.pe_visit_id )ppid on ppid.authority_id=p.authority_id and ppid.pe_id=p.pe_id and ppid.pe_visit_id=p.pe_visit_id");
if(form.getDoctorType()!=null && form.getDoctorType().trim().equals("YES")){
sql.append(" left join (select ft.* from (select f.*, row_number() over(partition by f.visit_no,f.authority_id, f.pid order by f.followup_fact_time desc) rn")
.append(" from crm.crm_follow_up_record_main f, crm.callcenter_staff_id_list cl where f.followup_fact_time is not null and ( f.operator_id =cl.security_baseinfo_id or f.task_status='1') ");
if(form.getDeptCode()!=null && !"".equals(form.getDeptCode().trim())){
if(form.getDeptCode().trim().equals("0220") || form.getDeptCode().trim().equals("0209"))
sql.append(" and (cl.dept_code='0220' or cl.dept_code='0209')");
else
sql.append(" and cl.dept_code=:deptCodeDoc");
}
sql.append(" ) ft where rn = 1 and ft.followup_type = '2' ");
}else{
sql.append(" left join (select ft.* from (select f.*, row_number() over(partition by f.visit_no,f.authority_id,f.pid order by f.followup_fact_time desc) rn ")
.append(" from crm.crm_follow_up_record_main f where f.followup_fact_time is not null ) ft where rn = 1 and ft.followup_type = '2'");
}
sql.append(" ) fp on fp.authority_id=p.authority_id and fp.pid=p.pe_id and fp.visit_no=p.pe_visit_id")
.append(" where p.tenant_id=:tenantId and p.upload_flag=1");
if(form.getName()!=null && !form.getName().trim().equals("")){
sql.append(" and (sub.name like '%"+form.getName().trim()+"%' or sub.input_code like UPPER('%"+form.getName().trim()+"%') )");
}
if(form.getFollowupType()!=null && !form.getFollowupType().trim().equals("") && !form.getFollowupType().trim().equals("all") ){//0全部，1已随访，2未随访
if(form.getFollowupType().equals("0")){
sql.append(" and fp.task_status='0'");
}else if(form.getFollowupType().equals("1")){
sql.append(" and fp.task_status='1'");
}else{
sql.append(" and ((fp.task_status!='1' and fp.task_status!='0') or fp.task_status is null)");
}
}
if(form.getSource()!=null && !form.getSource().trim().equals("")){
sql.append(" and p.pe_source=:source");
}
if(form.getResultStatus()!=null && !form.getResultStatus().trim().equals("")){
sql.append(" and p.RESULT_STATUS=:resultStatus");
}
if(form.getStartTime()!=null && !form.getStartTime().trim().equals("")){
sql.append(" and p.pe_pre_date >=to_date(:startTime,'yyyy-MM-dd')");
}
if(form.getEndTime()!=null && !form.getEndTime().trim().equals("")){
sql.append(" and p.pe_pre_date <=to_date(:endTime,'yyyy-MM-dd')+1");
}
if(form.getPatientId()!=null && !"".equals(form.getPatientId().trim())){
sql.append(" and pmi.PATIENT_ID=:patientId");
}
sql.append(" order by p.pe_pre_date desc");
SQLQuery query=getSession().createSQLQuery(sql.toString());
if(form.getDeptCode()!=null && !"".equals(form.getDeptCode().trim()) && form.getDoctorType()!=null && form.getDoctorType().trim().equals("YES") && !form.getDeptCode().trim().equals("0220") && !form.getDeptCode().trim().equals("0209")){
query.setParameter("deptCodeDoc", form.getDeptCode().trim());
}
query.setParameter("tenantId", form.getTenantId());
if(form.getSource()!=null && !form.getSource().trim().equals("")){
query.setParameter("source", form.getSource().trim());
}
if(form.getResultStatus()!=null && !form.getResultStatus().trim().equals("")){
query.setParameter("resultStatus", form.getResultStatus().trim());
}
if(form.getStartTime()!=null && !form.getStartTime().trim().equals("")){
query.setParameter("startTime",form.getStartTime().trim());
}
if(form.getEndTime()!=null && !form.getEndTime().trim().equals("")){
query.setParameter("endTime",form.getEndTime().trim());
}
if(form.getPatientId()!=null && !"".equals(form.getPatientId().trim())){
query.setParameter("patientId", form.getPatientId().trim());
}
if(form.getPage()!=-1){
query.setMaxResults(form.getSIZE());
query.setFirstResult((form.getPage()-1)*form.getSIZE());
}
return query.list();
本地运行正确，但是在云端的就提示ORD-01722，无效数字，既然提示了，那没办法只能硬着头皮找原因了，初步分析原因如下：
1、参数类型赋值错误
2、参数赋值顺序不对
3、参数赋值个数不对
然后就跟着这3个原因一个一个的排查，可找了半天发现这3个原因都不是错误的产生，没法，只能跟着日志文件往上在看看，这一看我就蒙了，因为在该sql语句执行之前还执行了一个sql语句 select count(*) from ......
后边的都是一样的，相信一般的朋友应该还是晓得是啥子意思，那么看到这里我就开始天窗似的猜想，同样的语句只有查询内容不一样，那么也就是说问题是出在查询的内容当中，那么哪个内容会出现数据的转换呢？看了一圈目标锁定：
1、nvl2(ppid.count,ppid.count,0)
2、nvl2(count(*),count(*),0)
为了先验证是不是这两个原因，我就把这两个字段直接换为0，结果真的就没问题了（是拿云端库部署测试的，为了测试可是牺牲了我的美容时间@_@），那么对于这两个原因我就纳闷了，因为最总其实都是指向nvl2(count(*),count(*),0)，那么这个函数怎么会出现无效数字呢？原因指向->count(*)
跟网上看了一些朋友给出的结论，当然是根据他们遇到的情况表示的，说是在这个表里边有某个字段进行了隐形转换，将字符串转换为了数字，
‘1’转换为1肯定没有问题，但是‘1A’ 想要转换为数字肯定就不对了，所以就会提示无效数字，当时想了一下觉得还挺对，后来也针对这个问题请教了我们老大，老大就说了一句会不会存在NULL的情况，就是说count(*)查询出来后有一个值为NULL，因为nvl2判断的是“”不是NULL，至于是不是这个原因目前还没搞清楚，不过先把问题的思路放在这里，希望有相同情况的朋友可以参考，讨论，当然更重要的是或许哪天我就会把他解决了（^_^）
