Search code examples
sqlamazon-redshiftwindow-functions

Invalid operation: Default parameter not be supported for window function lag;

I have MSSQL Server script, that I need to rewrite to Redshift

Here is original part from script 

    SELECT  cog.OrganizationId,
        cog.ClientId,
        REPLACE(REPLACE(AM, 'IAM: ', 'IMP: '),'AM: ', '') AS AM,
        u.ProcedureCodeString,
        t.MonthBilled,
        t.Month,
        t.Year,
        u.ClientCharges,
        LAG(u.ClientCharges,1,0) OVER (PARTITION BY cog.ClientId, u.ProcedureCodeString ORDER BY cog.ClientId,t.Year,t.Month) AS PreviosMonthCharges,
        LAG(u.ClientCharges,2,0) OVER (PARTITION BY cog.ClientId, u.ProcedureCodeString ORDER BY cog.ClientId,t.Year,t.Month) AS TwoMonthsAgoCharges,
        LAG(u.ClientCharges,3,0) OVER (PARTITION BY cog.ClientId, u.ProcedureCodeString ORDER BY cog.ClientId,t.Year,t.Month) AS ThreeMonthsAgoCharges,
        LAG(u.ClientCharges,4,0) OVER (PARTITION BY cog.ClientId, u.ProcedureCodeString ORDER BY cog.ClientId,t.Year,t.Month) AS FourMonthsAgoCharges,
        LAG(t.MonthBilled,3,0) OVER (PARTITION BY cog.ClientId, u.ProcedureCodeString ORDER BY cog.ClientId,t.Year,t.Month) AS ThreeMonthsAgoDate,
        DATEDIFF(d,CASE WHEN LAG(CONVERT(datetime,t.MonthBilled),3,0) OVER (PARTITION BY cog.ClientId, u.ProcedureCodeString ORDER BY cog.ClientId,t.Year,t.Month) = 0 THEN NULL ELSE LAG(CONVERT(datetime,t.MonthBilled),3,0) OVER (PARTITION BY cog.ClientId, u.ProcedureCodeString ORDER BY cog.ClientId,t.Year,t.Month) END,CONVERT(datetime,t.MonthBilled) ) AS DateDifCheck,
        b.FirstMonth,
        b.FirstFeature,
        CASE WHEN u.ClientCharges > 0
                AND LAG(u.ClientCharges,1,0) OVER (PARTITION BY cog.ClientId, u.ProcedureCodeString ORDER BY cog.ClientId,t.Year,t.Month) > 0
                AND LAG(u.ClientCharges,2,0) OVER (PARTITION BY cog.ClientId, u.ProcedureCodeString ORDER BY cog.ClientId,t.Year,t.Month) > 0
                AND LAG(u.ClientCharges,3,0) OVER (PARTITION BY cog.ClientId, u.ProcedureCodeString ORDER BY cog.ClientId,t.Year,t.Month) > 0
                AND LAG(u.ClientCharges,4,0) OVER (PARTITION BY cog.ClientId, u.ProcedureCodeString ORDER BY cog.ClientId,t.Year,t.Month) = 0
                AND LAG(t.MonthBilled,3,0) OVER (PARTITION BY cog.ClientId, u.ProcedureCodeString ORDER BY cog.ClientId,t.Year,t.Month) > b.FirstMonth
                AND DATEDIFF(d,CASE WHEN LAG(CONVERT(datetime,t.MonthBilled),3,0) OVER (PARTITION BY cog.ClientId, u.ProcedureCodeString ORDER BY cog.ClientId,t.Year,t.Month) = 0 THEN NULL ELSE LAG(CONVERT(datetime,t.MonthBilled),3,0) OVER (PARTITION BY cog.ClientId, u.ProcedureCodeString ORDER BY cog.ClientId,t.Year,t.Month) END,CONVERT(datetime,t.MonthBilled) ) < 95
                AND DATEDIFF(d,CASE WHEN LAG(CONVERT(datetime,t.MonthBilled),3,0) OVER (PARTITION BY cog.ClientId, u.ProcedureCodeString ORDER BY cog.ClientId,t.Year,t.Month) = 0 THEN NULL ELSE LAG(CONVERT(datetime,t.MonthBilled),3,0) OVER (PARTITION BY cog.ClientId, u.ProcedureCodeString ORDER BY cog.ClientId,t.Year,t.Month) END,CONVERT(datetime,t.MonthBilled) ) > 88
                AND u.ProcedureCodeString <> b.FirstFeature
                THEN 1 ELSE 0 END AS CommissionReportFlag

FROM    dbo.Contacts_ClientOrganization cog WITH(NOLOCK)

And here is how I rewrote it

    SELECT  cog.organizationid,
        cog.clientid,
        REPLACE(REPLACE(AM, 'IAM: ', 'IMP: '),'AM: ', '') AS AM,
        u.ProcedureCodeString,
        t.MonthBilled,
        t.Month,
        t.Year,
        u.ClientCharges,
        LAG(u.ClientCharges,1,0) OVER (PARTITION BY cog.clientid, u.ProcedureCodeString ORDER BY cog.clientid,t.Year,t.Month) AS PreviosMonthCharges,
        LAG(u.ClientCharges,2,0) OVER (PARTITION BY cog.clientid, u.ProcedureCodeString ORDER BY cog.clientid,t.Year,t.Month) AS TwoMonthsAgoCharges,
        LAG(u.ClientCharges,3,0) OVER (PARTITION BY cog.ClientId, u.ProcedureCodeString ORDER BY cog.сlientid,t.Year,t.Month) AS ThreeMonthsAgoCharges,
        LAG(u.ClientCharges,4,0) OVER (PARTITION BY cog.clientid, u.ProcedureCodeString ORDER BY cog.сlientid,t.Year,t.Month) AS FourMonthsAgoCharges,
        LAG(t.MonthBilled,3,0) OVER (PARTITION BY cog.clientid, u.ProcedureCodeString ORDER BY cog.clientid,t.Year,t.Month) AS ThreeMonthsAgoDate,
        DATEDIFF(d,CASE WHEN LAG(CONVERT(TIMESTAMP,t.MonthBilled),3,0) OVER (PARTITION BY cog.clientid, u.ProcedureCodeString ORDER BY cog.clientid,t.Year,t.Month) = 0 THEN NULL ELSE LAG(CONVERT(TIMESTAMP,t.MonthBilled),3,0) OVER (PARTITION BY cog.clientid, u.ProcedureCodeString ORDER BY cog.clientid,t.Year,t.Month) END,CONVERT(TIMESTAMP,t.MonthBilled) ) AS DateDifCheck,
        b.FirstMonth,
        b.FirstFeature,
        CASE WHEN u.ClientCharges > 0
                AND LAG(u.ClientCharges,1,0) OVER (PARTITION BY cog.clientid, u.ProcedureCodeString ORDER BY cog.clientid,t.Year,t.Month) > 0
                AND LAG(u.ClientCharges,2,0) OVER (PARTITION BY cog.clientid, u.ProcedureCodeString ORDER BY cog.clientid,t.Year,t.Month) > 0
                AND LAG(u.ClientCharges,3,0) OVER (PARTITION BY cog.clientid, u.ProcedureCodeString ORDER BY cog.clientid,t.Year,t.Month) > 0
                AND LAG(u.ClientCharges,4,0) OVER (PARTITION BY cog.clientid, u.ProcedureCodeString ORDER BY cog.clientid,t.Year,t.Month) = 0
                AND LAG(t.MonthBilled,3,0) OVER (PARTITION BY cog.clientid, u.ProcedureCodeString ORDER BY cog.clientid,t.Year,t.Month) > b.FirstMonth
                AND DATEDIFF(d,CASE WHEN LAG(CONVERT(TIMESTAMP,t.MonthBilled),3,0) OVER (PARTITION BY cog.clientid, u.ProcedureCodeString ORDER BY cog.clientid,t.Year,t.Month) = 0 THEN NULL ELSE LAG(CONVERT(TIMESTAMP,t.MonthBilled),3,0) OVER (PARTITION BY cog.clientid, u.ProcedureCodeString ORDER BY cog.clientid,t.Year,t.Month) END,CONVERT(TIMESTAMP,t.MonthBilled) ) < 95
                AND DATEDIFF(d,CASE WHEN LAG(CONVERT(TIMESTAMP,t.MonthBilled),3,0) OVER (PARTITION BY cog.clientid, u.ProcedureCodeString ORDER BY cog.clientid,t.Year,t.Month) = 0 THEN NULL ELSE LAG(CONVERT(TIMESTAMP,t.MonthBilled),3,0) OVER (PARTITION BY cog.clientid, u.ProcedureCodeString ORDER BY cog.clientid,t.Year,t.Month) END,CONVERT(TIMESTAMP,t.MonthBilled) ) > 88
                AND u.ProcedureCodeString <> b.FirstFeature
                THEN 1 ELSE 0 END AS CommissionReportFlag

FROM    public.contacts_client_organization cog

But I get this error now

[42601][500310] Amazon Invalid operation: Default parameter not be supported for window function lag; java.lang.RuntimeException: com.amazon.support.exceptions.ErrorException: Amazon Invalid operation: Default parameter not be supported for window function lag;

How I can fix this?

Solution

  • I found answer to this problem in official docs

      LAG (value_expr [, offset ])
[ IGNORE NULLS | RESPECT NULLS ]
OVER ( [ PARTITION BY window_partition ] ORDER BY window_ordering )

    I have default value, but reshift says that I need only offset, so just removing default value, do the trick