今天在写项目的时候,遇到了一个比价头疼的问题:
ifnull.............
No function matches the given name and argument types. You might need to add explicit type casts
现在公司用的是postgrepsql数据库,自己用navicat连接的,屁颠屁颠的还当是mysql的函数语法来用的,扣了半天,原来才发现,pg数据库中根本就没有ifnull这个函数,
可以使用COALESCE函数代替。
这是改正确之后的sql语句:
SELECT tt.NAME, tt.sex, tt.idcardtype, tt.idcard, tt.mobile , tt.birthday, tt.ID, tt.vipid, tt.cardno, tt.cardtypename , tt.cardtypecode, tt.gradetypename, tt.status, tt.TYPE, tt.changecardno , tt.changecardnoflag, tt.PASSWORD, tt.accountmoneytype, tt.networktype, tt.usertype , tt.money, tt.points, tt.couponcnt, tt.relatedcnt, COALESCE ( tt.comname, tt.unitname ) as unitname, tt.refereecode, tt.insuretype, tt.starteffectdate, tt.endeffectdate, tt.uniagentcode, tt.unisaleschannel , tt.unisalesbusitype FROM ( SELECT vip.NAME, vip.sex, vip.idcardtype, vip.idcard, vip.mobile , vip.birthday, acc.ID, acc.vipid , CASE WHEN acc.cardno IS NULL THEN acc.changecardno ELSE acc.cardno END AS cardno, acc.cardtypename, acc.cardtypecode, acc.gradetypename, acc.status , acc.TYPE, acc.changecardno, acc.changecardnoflag, acc.PASSWORD, acc.accountmoneytype , acc.networktype, acc.usertype, mon.money, mon.points , CASE WHEN cp.couponcnt IS NULL THEN 0 ELSE cp.couponcnt END AS couponcnt , ( SELECT COUNT(0) FROM vip_accountrelated T WHERE T.vipid = vip.ID AND T.accountid = acc.ID AND T.dr = 0 ) AS relatedcnt , ( SELECT t1.unit_name::VARCHAR FROM up_org_unit t1, up_org_unit_ext t2 WHERE t1.unit_id = t2.unit_id AND t2.unit_uncode = vip.unitid ) AS unitname , ( SELECT t1.unit_name::VARCHAR FROM up_org_unit t1, up_org_unit_ext t2 WHERE t1.unit_id = t2.unit_id AND t2.unit_uncode = acc.sendunitid ) AS comname, acc.refereecode, vip.insuretype, acc.starteffectdate, acc.endeffectdate , acc.uniagentcode, acc.unisaleschannel, acc.unisalesbusitype FROM VIP_ACCOUNT acc, VIP_INFO vip, VIP_ACCOUNTMONEY mon, ( SELECT ac.accountid, COUNT(0) AS couponcnt FROM VIP_ACCOUNTCOUPON ac, VIP_COUPON cou WHERE ac.couponcode = cou.code AND cou.status = 1 AND ac.dr = 0 AND cou.dr = 0 GROUP BY ac.accountid ) cp WHERE acc.vipid = vip.ID AND acc.ID = mon.ID AND acc.ID = cp.accountid AND acc.dr = 0 AND vip.dr = 0 AND mon.dr = 0 ) tt ORDER BY tt.usertype OFFSET ( ?- 1 ) * ? LIMIT ?
推荐一个网址:产看pgsql常用的函数:
https://www.cnblogs.com/personblog/p/11042105.html