MIME-Version: 1.0
From: Rein Petersen <email@reinpetersen.com>
Date: Tue, 7 Jun 2022 18:03:42 -0500
Message-ID: 
 <CAMj5WPQDan-kuny1GGC5GWFt6jcOyrSbQHd+g0Ga15vqs8XjbA@mail.gmail.com>
Subject: losing my mind about sytnax error in window clause
To: pgsql-sql@lists.postgresql.org
Content-Type: multipart/alternative; boundary="00000000000063da6c05e0e39a4a"
Archived-At: 
 <https://www.postgresql.org/message-id/CAMj5WPQDan-kuny1GGC5GWFt6jcOyrSbQHd%2Bg0Ga15vqs8XjbA%40mail.gmail.com>
Precedence: bulk

--00000000000063da6c05e0e39a4a
Content-Type: text/plain; charset="UTF-8"

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

--00000000000063da6c05e0e39a4a
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hi Folks,<br><div><br></div><div>I&#39;ve been reading the=
 docs over and again, searching exhaustively for examples on the internet t=
o help me here and I&#39;m ready to give up.</div><div><br></div><div>I hav=
e a query using windows function:<br><br><div style=3D"color:rgb(212,212,21=
2);background-color:rgb(30,30,30);font-family:Consolas,&quot;Courier New&qu=
ot;,monospace;font-size:16px;line-height:22px;white-space:pre"><div><span s=
tyle=3D"color:rgb(86,156,214)">SELECT</span></div><div>=C2=A0 =C2=A0 last_v=
alue ( typechange ) =C2=A0 =C2=A0 =C2=A0OVER w,</div><div>=C2=A0 =C2=A0 pin=
dex,</div><div>=C2=A0 =C2=A0 lid,</div><div>=C2=A0 =C2=A0 last_value ( modi=
fied ) =C2=A0 OVER w,</div><div>=C2=A0 =C2=A0 last_value ( created ) =C2=A0=
 =C2=A0OVER w,</div><div>=C2=A0 =C2=A0 last_value ( modifiedby ) OVER w,</d=
iv><div>=C2=A0 =C2=A0 last_value ( createby ) =C2=A0 OVER w,</div><div>=C2=
=A0 =C2=A0 last_value ( <span style=3D"color:rgb(86,156,214)">cost</span> )=
 FILTER ( <span style=3D"color:rgb(86,156,214)">WHERE</span> <span style=3D=
"color:rgb(86,156,214)">cost</span> <span style=3D"color:rgb(86,156,214)">I=
S</span> NOT <span style=3D"color:rgb(86,156,214)">NULL</span> ) OVER w,</d=
iv><div>=C2=A0 =C2=A0 last_value ( sell ) FILTER ( <span style=3D"color:rgb=
(86,156,214)">WHERE</span> sell <span style=3D"color:rgb(86,156,214)">IS</s=
pan> NOT <span style=3D"color:rgb(86,156,214)">NULL</span> ) OVER w</div><d=
iv><span style=3D"color:rgb(86,156,214)">FROM</span> ps._delta_ext</div><di=
v><span style=3D"color:rgb(86,156,214)">WHERE</span> pindex =3D Ppindex AND=
</div><div>=C2=A0 =C2=A0 ( Plid <span style=3D"color:rgb(86,156,214)">IS</s=
pan> <span style=3D"color:rgb(86,156,214)">NULL</span> OR lid =3D Plid ) AN=
D</div><div>=C2=A0 =C2=A0 ( Ptimestamp <span style=3D"color:rgb(86,156,214)=
">IS</span> <span style=3D"color:rgb(86,156,214)">NULL</span> OR modified &=
lt;=3D Ptimestamp )</div><div><span style=3D"color:rgb(86,156,214)">GROUP B=
Y</span> lid, pindex</div><div>WINDOW w <span style=3D"color:rgb(86,156,214=
)">AS</span> ( PARITION BY lid, pindex <span style=3D"color:rgb(86,156,214)=
">ORDER BY</span> created );</div></div></div><div>Complains about syntax e=
rror in the WINDOW cause (last line) on [PARTITION] BY:<br></div><div><div =
style=3D"color:rgb(212,212,212);background-color:rgb(30,30,30);font-family:=
Consolas,&quot;Courier New&quot;,monospace;font-size:16px;line-height:22px;=
white-space:pre"><div><span style=3D"color:rgb(106,153,85)">/* messages</sp=
an></div><div><span style=3D"color:rgb(106,153,85)">ERROR: =C2=A0syntax err=
or at or near &quot;BY&quot;</span></div><div><span style=3D"color:rgb(106,=
153,85)">LINE 16: =C2=A0WINDOW w AS ( PARITION BY locationid, partindex ORD=
ER BY cr...</span></div><div><span style=3D"color:rgb(106,153,85)">=C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0^</span></div><div><span style=3D"col=
or:rgb(106,153,85)">SQL state: 42601</span></div><div><span style=3D"color:=
rgb(106,153,85)">Character: 2724</span></div><div><span style=3D"color:rgb(=
106,153,85)">*/</span></div></div></div><div><br></div><div>Maybe my error =
is clear to someone else, could really use helping hand, thanks.</div><div>=
<br></div><div>Rein</div><div><br></div><div><br></div><div><br></div></div=
>

--00000000000063da6c05e0e39a4a--