Thread: losing my mind about sytnax error in window clause

losing my mind about sytnax error in window clause

From

Rein Petersen

Date:

08 June 2022, 02:03:42

Hi Folks,

I've been reading the docs over and again, searching exhaustively for examples on the internet to help me here and I'm ready to give up.

I have a query using windows function:

SELECT
    last_value ( typechange )      OVER w,
    pindex,
    lid,
    last_value ( modified )   OVER w,
    last_value ( created )    OVER w,
    last_value ( modifiedby ) OVER w,
    last_value ( createby )   OVER w,
    last_value ( cost ) FILTER ( WHERE cost IS NOT NULL ) OVER w,
    last_value ( sell ) FILTER ( WHERE sell IS NOT NULL ) OVER w
FROM ps._delta_ext
WHERE pindex = Ppindex AND
    ( Plid IS NULL OR lid = Plid ) AND
    ( Ptimestamp IS NULL OR modified <= Ptimestamp )
GROUP BY lid, pindex
WINDOW w AS ( PARITION BY lid, pindex ORDER BY created );

Complains about syntax error in the WINDOW cause (last line) on [PARTITION] BY:

/* messages
ERROR:  syntax error at or near "BY"
LINE 16:  WINDOW w AS ( PARITION BY locationid, partindex ORDER BY cr...
                                 ^
SQL state: 42601
Character: 2724
*/

Maybe my error is clear to someone else, could really use helping hand, thanks.

Rein

Re: losing my mind about sytnax error in window clause

From

Tom Lane

Date:

08 June 2022, 02:40:26

Rein Petersen <email@reinpetersen.com> writes:
> ERROR:  syntax error at or near "BY"
> LINE 16:  WINDOW w AS ( PARITION BY locationid, partindex ORDER BY cr...
>                                  ^

If you really spelled it like that, "PARTITION BY" should work better.

            regards, tom lane

Re: losing my mind about sytnax error in window clause

From

Rob Sargent

Date:

08 June 2022, 02:45:24

On Jun 7, 2022, at 7:03 PM, Rein Petersen <email@reinpetersen.com> wrote:

Hi Folks,

I've been reading the docs over and again, searching exhaustively for examples on the internet to help me here and I'm ready to give up.

I have a query using windows function:

SELECT
last_value ( typechange ) OVER w,
pindex,
lid,
last_value ( modified ) OVER w,
last_value ( created ) OVER w,
last_value ( modifiedby ) OVER w,
last_value ( createby ) OVER w,
last_value ( cost ) FILTER ( WHERE cost IS NOT NULL ) OVER w,
last_value ( sell ) FILTER ( WHERE sell IS NOT NULL ) OVER w
FROM ps._delta_ext
WHERE pindex = Ppindex AND
( Plid IS NULL OR lid = Plid ) AND
( Ptimestamp IS NULL OR modified <= Ptimestamp )
GROUP BY lid, pindex
WINDOW w AS ( PARITION BY lid, pindex ORDER BY created );
Complains about syntax error in the WINDOW cause (last line) on [PARTITION] BY:
/* messages
ERROR: syntax error at or near "BY"
LINE 16: WINDOW w AS ( PARITION BY locationid, partindex ORDER BY cr...
^
SQL state: 42601
Character: 2724
*/

Maybe my error is clear to someone else, could really use helping hand, thanks.

Rein

Why doesn’t your colorizer pick up ‘window’? Perhaps it doesn’t pickup ‘partition' either (when properly spelled)?

Re: losing my mind about sytnax error in window clause

From

Bob Edwards

Date:

08 June 2022, 02:46:18

On 8/6/22 09:03, Rein Petersen wrote:
> Hi Folks,
> 
> I've been reading the docs over and again, searching exhaustively for 
> examples on the internet to help me here and I'm ready to give up.
> 
> I have a query using windows function:
> 
> SELECT
>      last_value ( typechange )      OVER w,
>      pindex,
>      lid,
>      last_value ( modified )   OVER w,
>      last_value ( created )    OVER w,
>      last_value ( modifiedby ) OVER w,
>      last_value ( createby )   OVER w,
>      last_value ( cost ) FILTER ( WHERE cost IS NOT NULL ) OVER w,
>      last_value ( sell ) FILTER ( WHERE sell IS NOT NULL ) OVER w
> FROM ps._delta_ext
> WHERE pindex = Ppindex AND
>      ( Plid IS NULL OR lid = Plid ) AND
>      ( Ptimestamp IS NULL OR modified <= Ptimestamp )
> GROUP BY lid, pindex
> WINDOW w AS ( PARITION BY lid, pindex ORDER BY created );
> Complains about syntax error in the WINDOW cause (last line) on 
> [PARTITION] BY:
> /* messages
> ERROR:  syntax error at or near "BY"
> LINE 16:  WINDOW w AS ( PARITION BY locationid, partindex ORDER BY cr...
>                                   ^
> SQL state: 42601
> Character: 2724
> */
> 
> Maybe my error is clear to someone else, could really use helping hand, 
> thanks.
> 
> Rein

PARITION <> PARTITION - check your spelling.

cheers,
Bob Edwards.

> 
> 
>

Re: losing my mind about sytnax error in window clause

From

Rein Petersen

Date:

08 June 2022, 03:14:32

Fixing the typo (*PARTITION) but bump into ERROR: FILTER is not implemented for non-aggregate window functions ....

What Im really trying to do is merge rows into a single row, allowing the later rows to take precedence. I thought it cracked...

On Tue, Jun 7, 2022 at 6:03 PM Rein Petersen <email@reinpetersen.com> wrote:

Hi Folks,

I've been reading the docs over and again, searching exhaustively for examples on the internet to help me here and I'm ready to give up.

I have a query using windows function:

SELECT
last_value ( typechange ) OVER w,
pindex,
lid,
last_value ( modified ) OVER w,
last_value ( created ) OVER w,
last_value ( modifiedby ) OVER w,
last_value ( createby ) OVER w,
last_value ( cost ) FILTER ( WHERE cost IS NOT NULL ) OVER w,
last_value ( sell ) FILTER ( WHERE sell IS NOT NULL ) OVER w
FROM ps._delta_ext
WHERE pindex = Ppindex AND
( Plid IS NULL OR lid = Plid ) AND
( Ptimestamp IS NULL OR modified <= Ptimestamp )
GROUP BY lid, pindex
WINDOW w AS ( PARITION BY lid, pindex ORDER BY created );
Complains about syntax error in the WINDOW cause (last line) on [PARTITION] BY:
/* messages
ERROR: syntax error at or near "BY"
LINE 16: WINDOW w AS ( PARITION BY locationid, partindex ORDER BY cr...
^
SQL state: 42601
Character: 2724
*/

Maybe my error is clear to someone else, could really use helping hand, thanks.

Rein

Re: losing my mind about sytnax error in window clause

From

Ben Tilly

Date:

08 June 2022, 03:48:53

I have a working solution to this, BUT there is enough overhead in user defined functions that it didn't perform well in practice for me. Figuring out how to create temporary tables then join them cleverly worked out better. However you'd just have to rewrite your FILTER lines as something like last_value_when( cost, cost is not null ) over w.

Pity, because if this performed well, it would be really convenient for some work I was doing...

CREATE OR REPLACE FUNCTION public.last_value_when_sfunc(state anyelement, value anyelement, cond BOOL) RETURNS anyelement AS
$$
SELECT CASE WHEN cond THEN value ELSE state END;
$$
LANGUAGE SQL;

COMMENT ON FUNCTION public.last_value_when_sfunc (anyelement, anyelement, bool) IS 'Helper function for tracking last matching in window';

DO
$$
BEGIN
IF 0 < (SELECT count(*)
FROM pg_proc p
LEFT JOIN pg_namespace n
ON p.pronamespace = n.oid
WHERE n.nspname = 'public' AND p.proname = 'last_value_when')
THEN
DROP AGGREGATE public.last_value_when(anyelement, bool);
END IF;
END
$$
language 'plpgsql';

CREATE AGGREGATE public.last_value_when(anyelement, bool) (
SFUNC = public.last_value_when_sfunc,
STYPE = anyelement);

COMMENT ON AGGREGATE public.last_value_when (anyelement, bool) IS 'Aggregate function for tracking the last value when a condition was true';

On Tue, Jun 7, 2022 at 5:14 PM Rein Petersen <email@reinpetersen.com> wrote:

Fixing the typo (*PARTITION) but bump into ERROR: FILTER is not implemented for non-aggregate window functions ....

What Im really trying to do is merge rows into a single row, allowing the later rows to take precedence. I thought it cracked...

On Tue, Jun 7, 2022 at 6:03 PM Rein Petersen <email@reinpetersen.com> wrote:
Hi Folks,

I've been reading the docs over and again, searching exhaustively for examples on the internet to help me here and I'm ready to give up.

I have a query using windows function:

SELECT
last_value ( typechange ) OVER w,
pindex,
lid,
last_value ( modified ) OVER w,
last_value ( created ) OVER w,
last_value ( modifiedby ) OVER w,
last_value ( createby ) OVER w,
last_value ( cost ) FILTER ( WHERE cost IS NOT NULL ) OVER w,
last_value ( sell ) FILTER ( WHERE sell IS NOT NULL ) OVER w
FROM ps._delta_ext
WHERE pindex = Ppindex AND
( Plid IS NULL OR lid = Plid ) AND
( Ptimestamp IS NULL OR modified <= Ptimestamp )
GROUP BY lid, pindex
WINDOW w AS ( PARITION BY lid, pindex ORDER BY created );
Complains about syntax error in the WINDOW cause (last line) on [PARTITION] BY:
/* messages
ERROR: syntax error at or near "BY"
LINE 16: WINDOW w AS ( PARITION BY locationid, partindex ORDER BY cr...
^
SQL state: 42601
Character: 2724
*/

Maybe my error is clear to someone else, could really use helping hand, thanks.

Rein