CODEWITHSUNDEEP
sqlamazon-redshift
Eugene Sukh
Eugene Sukh

Reputation: 2737

Invalid operation: syntax error at or near "Delta"

I have redshift script

Here is the code of part of a script

    SELECT  cog.organizationid,
        cog.clientid,
        t.MonthBilled,
        t.Month,
        t.Year,
        u.ClientCharges,
        cp.supervisorcontactid AS Principal,
        cc.fullname AS CSL,

        LAG(u.ClientCharges,1,0) OVER (PARTITION BY cog.clientid ORDER BY cog.clientid,t.Year,t.Month) AS PreviosMonthCharges,

        CASE WHEN u.ClientCharges IS NULL THEN 0 ELSE u.ClientCharges END -
                                CASE WHEN LAG(u.ClientCharges,1,0) OVER (PARTITION BY cog.clientid ORDER BY cog.clientid,t.Year,t.Month) IS NULL THEN 0
                                ELSE LAG(u.ClientCharges,1,0) OVER (PARTITION BY cog.clientid ORDER BY cog.clientid,t.Year,t.Month)
                                END AS Delta,

        CASE WHEN (CASE WHEN u.ClientCharges IS NULL THEN 0 ELSE u.ClientCharges END - CASE WHEN LAG(u.ClientCharges,1,0) OVER (PARTITION BY cog.clientid ORDER BY cog.clientid,t.Year,t.Month) IS NULL THEN 0
                                ELSE LAG(u.ClientCharges,1,0) OVER (PARTITION BY cog.clientid ORDER BY cog.clientid,t.Year,t.Month) END) > 0 THEN 'Gain'
             WHEN CASE WHEN u.ClientCharges IS NULL THEN 0 ELSE u.ClientCharges END - CASE WHEN LAG(u.ClientCharges,1,0) OVER (PARTITION BY cog.clientid ORDER BY cog.clientid,t.Year,t.Month) IS NULL THEN 0
                                ELSE LAG(u.ClientCharges,1,0) OVER (PARTITION BY cog.clientid ORDER BY cog.clientid,t.Year,t.Month) END < 0 THEN 'Loss'
                                ELSE NULL END AS GainsLosses,
        CASE WHEN u.ClientCharges >=0 AND LAG(u.ClientCharges,1,0) OVER (PARTITION BY cog.clientid ORDER BY cog.clientid,t.Year,t.Month) IS NULL AND t.MonthBilled <> '1/1/2015' THEN 'New' ELSE 'Existing' END AS ClientType,

        CASE WHEN u.ClientCharges IS NULL THEN 'InActive' ELSE 'Active' END AS ActiveStatus,

        CASE WHEN LAG(u.ClientCharges,1,0) OVER (PARTITION BY cog.clientid ORDER BY cog.clientid,t.Year,t.Month) IS NOT NULL AND t.MonthBilled <> '1/1/2015' THEN 1 ELSE NULL END AS ActiveLastMonth,

        CASE WHEN u.ClientCharges IS NULL AND (LAG(u.ClientCharges,1,0) OVER (PARTITION BY cog.clientid ORDER BY cog.clientid,t.Year,t.Month) <> 0 AND LAG(u.ClientCharges,1,0) OVER (PARTITION BY cog.clientid ORDER BY cog.clientid,t.Year,t.Month) IS NOT NULL) AND t.MonthBilled <> '1/1/2015' THEN 1 ELSE NULL END AS OneMonthChurn,

        u2.FeatureKeyCount,

        CASE WHEN u.ClientCharges IS NOT NULL AND u.ClientCharges > 0 THEN 1 ELSE 0 END AS OrgCounter
FROM    public.contacts_client_organization cog

I have this error message when trying to run it

[42601][500310] Amazon Invalid operation: syntax error at or near "Delta" Position: 747; java.lang.RuntimeException: com.amazon.support.exceptions.ErrorException: Amazon Invalid operation: syntax error at or near "Delta" Position: 747;

But I not understood, what's wrong?

UPDATE

Here is rewritten part with valid LAG, but I still have the same problem

 SELECT  cog.organizationid,
        cog.clientid,
        t.MonthBilled,
        t.Month,
        t.Year,
        u.ClientCharges,
        cp.supervisorcontactid AS Principal,
        cc.fullname AS CSL,

        LAG(u.ClientCharges,1) OVER (PARTITION BY cog.clientid ORDER BY cog.clientid,t.Year,t.Month) AS PreviosMonthCharges,

        CASE WHEN u.ClientCharges IS NULL THEN 0 ELSE u.ClientCharges END -
                                CASE WHEN LAG(u.ClientCharges,1) OVER (PARTITION BY cog.clientid ORDER BY cog.clientid,t.Year,t.Month) IS NULL THEN 0
                                ELSE LAG(u.ClientCharges,1) OVER (PARTITION BY cog.clientid ORDER BY cog.clientid,t.Year,t.Month)
                                END AS Delta,

        CASE WHEN (CASE WHEN u.ClientCharges IS NULL THEN 0 ELSE u.ClientCharges END - CASE WHEN LAG(u.ClientCharges,1) OVER (PARTITION BY cog.clientid ORDER BY cog.clientid,t.Year,t.Month) IS NULL THEN 0
                                ELSE LAG(u.ClientCharges,1) OVER (PARTITION BY cog.clientid ORDER BY cog.clientid,t.Year,t.Month) END) > 0 THEN 'Gain'
             WHEN CASE WHEN u.ClientCharges IS NULL THEN 0 ELSE u.ClientCharges END - CASE WHEN LAG(u.ClientCharges,1) OVER (PARTITION BY cog.clientid ORDER BY cog.clientid,t.Year,t.Month) IS NULL THEN 0
                                ELSE LAG(u.ClientCharges,1) OVER (PARTITION BY cog.clientid ORDER BY cog.clientid,t.Year,t.Month) END < 0 THEN 'Loss'
                                ELSE NULL END AS GainsLosses,
        CASE WHEN u.ClientCharges >=0 AND LAG(u.ClientCharges,1) OVER (PARTITION BY cog.clientid ORDER BY cog.clientid,t.Year,t.Month) IS NULL AND t.MonthBilled <> '1/1/2015' THEN 'New' ELSE 'Existing' END AS ClientType,

        CASE WHEN u.ClientCharges IS NULL THEN 'InActive' ELSE 'Active' END AS ActiveStatus,

        CASE WHEN LAG(u.ClientCharges,1) OVER (PARTITION BY cog.clientid ORDER BY cog.clientid,t.Year,t.Month) IS NOT NULL AND t.MonthBilled <> '1/1/2015' THEN 1 ELSE NULL END AS ActiveLastMonth,

        CASE WHEN u.ClientCharges IS NULL AND (LAG(u.ClientCharges,1) OVER (PARTITION BY cog.clientid ORDER BY cog.clientid,t.Year,t.Month) <> 0 AND LAG(u.ClientCharges,1) OVER (PARTITION BY cog.clientid ORDER BY cog.clientid,t.Year,t.Month) IS NOT NULL) AND t.MonthBilled <> '1/1/2015' THEN 1 ELSE NULL END AS OneMonthChurn,

        u2.FeatureKeyCount,

        CASE WHEN u.ClientCharges IS NOT NULL AND u.ClientCharges > 0 THEN 1 ELSE 0 END AS OrgCounter
FROM    public.contacts_client_organization cog

Upvotes: 1

Views: 1329

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

I'm not sure if this is the problem, but lag() doesn't accept a third argument for the default in Redshift.

I would write this as:

    (COALESCE(u.ClientCharges, 0) -
     COALESCE(LAG(u.ClientCharges, 1) OVER (PARTITION BY cog.clientid ORDER BY cog.clientid, t.Year, t.Month), 0)
    ) as my_Delta,

DELTA is an Amazon Redshift reserved word, so give it a different name.

Upvotes: 1

Related Questions