从 Oracle 到 Mysql 调用本机函数"DATE_FORMAT"时参数计数不正确

Question

我有Oracle脚本，我想把它从Oracle查询转换成mysql查询。在SQLines和文档阅读的帮助下，我从Oracle Format:

nvl(TO_NUMBER(TO_CHAR(ADD_MONTHS(T156431.PERIOD_START, -6), 'yyyy'), '9999') , 1970) as c13

格式:

ifnull(cast(DATE_FORMAT(TIMESTAMPADD(MONTH, -6, T156431.PERIOD_START), '%Y') as SIGNED), 1970) as c13

但是当我尝试运行mysql格式时，我最终出现了这个错误，我不知道是什么导致的:

Incorrect parameter count in the call to native function 'DATE_FORMAT'

我哪里做错了?

---

这是完整的mysql脚本(问题在第14行)

SELECT 
sum(T156033.PO_AMOUNT / 1000000.0) as c1,
sum(T156033.PO_AMOUNT / 1000.0) as c2,
sum(T156033.PO_AMOUNT / 1.0) as c3,
concat(concat(T156375.ACTIVITIES_R, ' '), T156375.ACTIVITIES_ALIAS_R) as c4,
T156375.ACTIVITIES_R as c5,
T156375.ACTIVITIES_ALIAS_R as c6,
T155910.AP_CODE_R as c7,
concat(concat(T156431.JSP_PLAN_ID_WO_FY, ' '), T156431.JSP_PLAN_NAME) as c8,
T156431.PERIOD_START as c9,
IFNULL(T155910.AP_CATEGORY_R , 'Unknown A&P Category') as c10,
IFNULL(case IFNULL(T155910.AP_CAT_COMM , 'UNKNOWN') when 'OPEX_AP' then 'Opex' when 'CAPE' then 'Capex' else 'Unknown' end  , 'Unknown AP') as c11,
IFNULL(T156431.PLAN_TYPE , 'Unknown') as c12,
ifnull(cast(DATE_FORMAT(TIMESTAMPADD(MONTH, -6, T156431.PERIOD_START), '%Y') as SIGNED), 1970) as c13
FROM
(SELECT MFM_PROMOTION_HEADER.*, SUBSTRING(MFM_PROMOTION_HEADER.JSP_PLAN_ID,1, CASE WHEN LOCATE(MFM_PROMOTION_HEADER.JSP_PLAN_ID,'_') = 0 THEN CHAR_LENGTH(RTRIM(MFM_PROMOTION_HEADER.JSP_PLAN_ID)) ELSE LOCATE(MFM_PROMOTION_HEADER.JSP_PLAN_ID,'_') -1 END) AS JSP_PLAN_ID_WO_FY FROM MFM_PROMOTION_HEADER_VIEW MFM_PROMOTION_HEADER WHERE MFM_PROMOTION_HEADER.MARKET_CODE IN ('IT')) T156431,
(SELECT MARKET_CODE, AP_TREE, AP_TREE_R, AP_TREE_ALIAS, AP_TREE_ALIAS_R, AP_CATEGORY, AP_CATEGORY_R, AP_CATEGORY_ALIAS, AP_CATEGORY_ALIAS_R, AP_TYPE, AP_TYPE_R, AP_TYPE_ALIAS, AP_TYPE_ALIAS_R, AP_CODE, AP_CODE_R, AP_CODE_ALIAS, AP_CODE_ALIAS_R, FV_IND, AP_CAT_COMM, AP_CAT_COST FROM SGT_DIM_AP_CODE WHERE MARKET_CODE IN ('IT')) T155910,
(SELECT * FROM SGT_DIM_ACTIVITY WHERE MARKET_CODE IN ('IT')) T156375,
(SELECT RIGHT (REPEAT('0', 8) + LEFT ((CASE WHEN IFNULL(t2.customer, 'XXXX') = 'XXXX' THEN SUBSTRING (t1.CUSTOMER,1,LOCATE('_',t1.CUSTOMER)-1) ELSE SUBSTRING(t2.CUSTOMER,1,LOCATE('_',t2.CUSTOMER)-1) END), 8 ), 8 ) as customer_po, t1.*, (CASE WHEN t1.PO_EQUV_AMOUNT = 0 THEN 0 ELSE t1.PO_AMOUNT/t1.PO_EQUV_AMOUNT END) as ACTUAL_FX,  ( CASE WHEN t1.PO_CURRENCY = 'RMB' THEN t1.PO_EQUV_AMOUNT ELSE t1.PO_AMOUNT/1.260924 END ) AS PO_AMOUNT_DF, t1.PO_EXCH_RATE * t1.PAYMENT_PAID_AMOUNT as PAYMENT_PAID_AMOUNT_HKD from mfm_po_jsp t1 left join MFM_CUSTOMER_MAPPING t2 on t2.market_code = t1.market_code and RIGHT((REPEAT ('0', 8)) + LEFT (t2.sub_ledger, 8) , 8 )  =  RIGHT ((REPEAT ('0', 8)) + LEFT (SUBSTRING(t1.CUSTOMER,1,LOCATE('_',t1.CUSTOMER)-1), 8) , 8 ) ) T156033 
WHERE  T156033.JSP_PLAN_ID = T156431.JSP_PLAN_ID and T155910.AP_CODE = T156033.AP_CODE and T155910.MARKET_CODE = T156033.MARKET_CODE and T156033.MARKET_CODE = T156431.MARKET_CODE and T156033.ACTIVITIES = T156375.ACTIVITIES and T156033.MARKET_CODE = T156375.MARKET_CODE  and YEAR(DATE_FORMAT(TIMESTAMPADD(MONTH,-6,T156431.PERIOD_START))) = 2021 
GROUP BY T155910.AP_CODE_R, T156375.ACTIVITIES_ALIAS_R, T156375.ACTIVITIES_R, T156431.PERIOD_START, concat(concat(T156375.ACTIVITIES_R, ' '), T156375.ACTIVITIES_ALIAS_R), concat(concat(T156431.JSP_PLAN_ID_WO_FY, ' '), T156431.JSP_PLAN_NAME), IFNULL(T155910.AP_CATEGORY_R , 'Unknown A&P Category'), IFNULL(T156431.PLAN_TYPE , 'Unknown'), IFNULL(case IFNULL(T155910.AP_CAT_COMM , 'UNKNOWN') when 'OPEX_AP' then 'Opex' when 'CAPE' then 'Capex' else 'Unknown' end  , 'Unknown AP')

Answer 1

好的，所以看起来错误不是来自我突出显示的行，而是来自另一行。

From WHERE line

YEAR(DATE_FORMAT(TIMESTAMPADD(MONTH,-6,T156431.PERIOD_START))

，当然，它缺少一些参数来运行DATE_FORMAT函数，因此出现错误。