Question

无效操作：函数 pg_catalog.pgdate_part("unknown", text) 不存在；

2018-09-19

9282

sql amazon-redshift

我有用于 redshift 的 SQL 脚本

这是部分代码

SELECT  clo.name AS SalesManager,
    prospect.id AS ProspectId,
    prospect.fullname AS ProspectName,
    prospect.company,
    Prospect.Email,
    prospect.phonehome,
    prospect.phonecell,
    prospect.phonework,
    prospect.phoneworkext,
    pct.PercentClosing,
    pct.PercentClosingLearn,
    pct.PercentClosingLMS,
    COALESCE(CAST(paying.Paying AS VARCHAR), 'NO') AS Paying,
    COALESCE(CAST(paying.Paying AS VARCHAR), 'NO') AS PayingLearn,
    --CONVERT(DATE, metaData.ClosingDatePM) AS CloseDatePM,
    CAST(metaData.ClosingDatePM AS DATE) AS CloseDatePM,
    DATE_PART(YEAR, metaData.ClosingDatePM) AS CloseYear,
    DATE_PART(MONTH, metaData.ClosingDatePM) AS CloseMonth,
    CASE WHEN DATE_PART('MONTH', metaData.ClosingDatePM)||'/1/'||DATE_PART('YEAR', metaData.ClosingDatePM) = '/1/' THEN NULL ELSE DATE_PART('MONTH', metaData.ClosingDatePM)||'/1/'||DATE_PART(YEAR, metaData.ClosingDatePM) END AS MonthClose,
    metaData.*

当我尝试运行脚本时，出现错误。

这个

[42883][500310] Amazon Invalid operation: function pg_catalog.pgdate_part("unknown", text) does not exist; java.lang.RuntimeException: com.amazon.support.exceptions.ErrorException: Amazon Invalid operation: function pg_catalog.pgdate_part("unknown", text) does not exist;

但如果我注释掉那些行

DATE_PART(YEAR, metaData.ClosingDatePM) AS CloseYear,

    DATE_PART(MONTH, metaData.ClosingDatePM) AS CloseMonth,

    CASE WHEN DATE_PART('MONTH', metaData.ClosingDatePM)||'/1/'||DATE_PART('YEAR', metaData.ClosingDatePM) = '/1/' THEN NULL ELSE DATE_PART('MONTH', metaData.ClosingDatePM)||'/1/'||DATE_PART(YEAR, metaData.ClosingDatePM) END AS MonthClose,

一切正常。

示例表数据（由于问题，我只添加了一列，我认为与它有关）：

closingdatepm
-------------
9/16/2013
12/01/2017
3/1/2014
5/1/2015
01/01/2018
4/1/2014
8/1/2014
10/31/2017
01/01/2018
5/1/2015

我的问题出在哪里？

Answer 1

所以，我想我找到了答案，如何修复它

就这样使用它

CAST(metaData.ClosingDatePM AS DATE) AS CloseDatePM,

    DATE_PART(YEAR, CloseDatePM) AS CloseYear,

其他类似这样的

Answer 2

这是因为 date_part 仅适用于日期类型的列。以下是您的答案：

SELECT  clo.name AS SalesManager,
    prospect.id AS ProspectId,
    prospect.fullname AS ProspectName,
    prospect.company,
    Prospect.Email,
    prospect.phonehome,
    prospect.phonecell,
    prospect.phonework,
    prospect.phoneworkext,
    pct.PercentClosing,
    pct.PercentClosingLearn,
    pct.PercentClosingLMS,
    COALESCE(CAST(paying.Paying AS VARCHAR), 'NO') AS Paying,
    COALESCE(CAST(paying.Paying AS VARCHAR), 'NO') AS PayingLearn,
    --CONVERT(DATE, metaData.ClosingDatePM) AS CloseDatePM,
    CAST(metaData.ClosingDatePM AS DATE) AS CloseDatePM,
    DATE_PART(YEAR, cast(metaData.ClosingDatePM) as date) AS CloseYear,
    DATE_PART(MONTH, cast(metaData.ClosingDatePM) as date) AS CloseMonth,
    CASE WHEN DATE_PART('MONTH', cast(metaData.ClosingDatePM) as date)||'/1/'||DATE_PART('YEAR', cast(metaData.ClosingDatePM) as date) = '/1/' THEN NULL ELSE DATE_PART('MONTH', cast(metaData.ClosingDatePM) as date)||'/1/'||DATE_PART(YEAR, cast(metaData.ClosingDatePM) as date) END AS MonthClose,
    metaData.*