COALESCE types text and integer cannot be matched

错误sql:

 select cw.id,cw."name" ,cw.code,
      coalesce ((select min(cp2.seq) from cm_port cp2 where cp2.ware_id = cw.id),0) as minPortNo,
      (select max(cp1.seq)  from cm_port cp1 where cp1.ware_id = cw.id) maxPortNo
   from cm_ware cw,ce_ware_module_scc cwms 
    where cw.id = cwms.ware_id 

1. 出现原因：
   COALESCE组合的字段，类型不同，前面是text,后面是integer类型，
   cp.seq是varchar类型
2. 解决办法：进行类型转换。

修改后的sql:

 select cw.id,cw."name" ,cw.code,
   coalesce (cast((select min(cp2.seq) from cm_port cp2 where cp2.ware_id = cw.id) as integer),0) as minPortNo,
   (select max(cp1.seq)  from cm_port cp1 where cp1.ware_id = cw.id) maxPortNo
    from cm_ware cw,ce_ware_module_scc cwms 
    where cw.id = cwms.ware_id 

也可以是：

 select cw.id,cw."name" ,cw.code,
   coalesce ((select min(cp2.seq) from cm_port cp2 where cp2.ware_id = cw.id),'0') as minPortNo,
   (select max(cp1.seq)  from cm_port cp1 where cp1.ware_id = cw.id) maxPortNo
    from cm_ware cw,ce_ware_module_scc cwms 
    where cw.id = cwms.ware_id