Invalid operation: function pg_catalog.pgdate_part("unknown", text) does not exist;

0
votes

I have SQL script for redshift

Here is part of the code

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.*

When I try to run the script , I get an error.

This one

[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;

But if I comment those rows

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,

All working great.

Sample table data (I added only one column, because of problem, I think related to it):

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

Where can be my trouble?

2
sqlamazon-redshift

2 Answers

0
votes

So . I think I find answer , how to fix it

Just use it like this

CAST(metaData.ClosingDatePM AS DATE) AS CloseDatePM,

    DATE_PART(YEAR, CloseDatePM) AS CloseYear,

And other like this

0
votes

This is because date_part only works on date type of columns. Below is your answer:

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.*