oracle数据库查询中错误--格式化出现两次

今天刚做了一个异常数据显示报表,可是编辑好sql执行时出现错误:格式化出现错误,

下面是我的代码

oracle数据库查询中错误--格式化出现两次
 1 select bmg.material_group_code as vehicleCode,
 2        colInfo.lotId as lotId, 
 3        info.erp_product_id as productCode, 
 4        info.erp_product_name as productName,
 5        colInfo.startDeviceName as startDeviceName,
 6        colInfo.endDeviceName as endDeviceName,
 7        colInfo.startDeviceCollectionDate as startDeviceCollectionDate,
 8        colInfo.endDeviceCollectionDate as endDeviceCollectionDate
 9 from  (select trac.lot_id as lotId,
10              max(decode(dev.gid,40288b8f3c8486e4013c8980a8f401cc,dev.description)) as startDeviceName,
11              max(decode(dev.gid,40288b8f3c8486e4013c8980a8f401cc,trac.collection_time)) as startDeviceCollectionDate,
12              max(decode(dev.gid,40288b8f3c8486e4013c897eb77501c7,dev.description)) as endDeviceName,
13              max(decode(dev.gid,40288b8f3c8486e4013c897eb77501c7,trac.collection_time)) as endDeviceCollectionDate
14         from pro_vehicle_traking trac left join ins_device dev on trac.device_gid = dev.gid
15        where trac.collection_time between to_date(2014-02-10,yyyy-MM-dd hh:mm:ss)
16        and to_date(2014-03-10,yyyy-MM-dd hh:mm:ss)
17         group by trac.lot_id
18        ) colInfo
19 left join pro_item_with_lot lot on colInfo.lotId = lot.lot_id
20 left join pro_order pro on pro.gid = lot.order_gid
21 left join mtb_vehicle_info info on info.gid = pro.vehicle_info_gid
22 left join binf_material_group bmg on bmg.material_group_id = info.special_car_type_gid
23 left join mtb_material_group_class mmgc on mmgc.gid = bmg.material_group_type_gid
24 where mmgc.material_group_class_id =CXZ
25 and  colInfo.startDeviceCollectionDate is null
26 or colInfo.endDeviceCollectionDate < colInfo.startDeviceCollectionDate
27 ;
oracle数据库查询中错误--格式化出现两次

其中如果将15行和16行的语句去除则执行正常,经过查找资料,找到错误原因所在

经常处理日期的的java程序员可能都会将日期转换的格式与oracle特有的搞混淆,其中java的一般为yyyy-MM-dd hh:mm:ss,而Oracle数据库中则以

yyyy-mm-dd hh24:mi:ss来表示相应的格式,所以将上述的错误改变一下就可以执行正常了。

正确的sql语句如下

oracle数据库查询中错误--格式化出现两次
 1 select bmg.material_group_code as vehicleCode,
 2        colInfo.lotId as lotId, 
 3        info.erp_product_id as productCode, 
 4        info.erp_product_name as productName,
 5        colInfo.startDeviceName as startDeviceName,
 6        colInfo.endDeviceName as endDeviceName,
 7        colInfo.startDeviceCollectionDate as startDeviceCollectionDate,
 8        colInfo.endDeviceCollectionDate as endDeviceCollectionDate
 9 from  (select trac.lot_id as lotId,
10              max(decode(dev.gid,40288b8f3c8486e4013c8980a8f401cc,dev.description)) as startDeviceName,
11              max(decode(dev.gid,40288b8f3c8486e4013c8980a8f401cc,trac.collection_time)) as startDeviceCollectionDate,
12              max(decode(dev.gid,40288b8f3c8486e4013c897eb77501c7,dev.description)) as endDeviceName,
13              max(decode(dev.gid,40288b8f3c8486e4013c897eb77501c7,trac.collection_time)) as endDeviceCollectionDate
14         from pro_vehicle_traking trac left join ins_device dev on trac.device_gid = dev.gid
15        where trac.collection_time between to_date(2014-02-10,yyyy-MM-dd hh:mi:ss)
16        and to_date(2014-03-10,yyyy-MM-dd hh:mi:ss)
17         group by trac.lot_id
18        ) colInfo
19 left join pro_item_with_lot lot on colInfo.lotId = lot.lot_id
20 left join pro_order pro on pro.gid = lot.order_gid
21 left join mtb_vehicle_info info on info.gid = pro.vehicle_info_gid
22 left join binf_material_group bmg on bmg.material_group_id = info.special_car_type_gid
23 left join mtb_material_group_class mmgc on mmgc.gid = bmg.material_group_type_gid
24 where mmgc.material_group_class_id =CXZ
25 and  colInfo.startDeviceCollectionDate is null
26 or colInfo.endDeviceCollectionDate < colInfo.startDeviceCollectionDate
27 ;
oracle数据库查询中错误--格式化出现两次

oracle数据库查询中错误--格式化出现两次,布布扣,bubuko.com

oracle数据库查询中错误--格式化出现两次

上一篇:ExtJs读取服务器端数据 spring+springJDBC+struts2+extjs技术


下一篇:Oracle存储过程学习备忘