Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nyjLW-0000xS-TR for pgsql-sql@arkaria.postgresql.org; Wed, 08 Jun 2022 00:14:51 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1nyjLV-0006uc-AD for pgsql-sql@arkaria.postgresql.org; Wed, 08 Jun 2022 00:14:49 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nyjLU-0006uT-SX for pgsql-sql@lists.postgresql.org; Wed, 08 Jun 2022 00:14:49 +0000 Received: from mail-wm1-x32d.google.com ([2a00:1450:4864:20::32d]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1nyjLS-0007kn-7f for pgsql-sql@lists.postgresql.org; Wed, 08 Jun 2022 00:14:48 +0000 Received: by mail-wm1-x32d.google.com with SMTP id l2-20020a05600c4f0200b0039c55c50482so2492552wmq.0 for ; Tue, 07 Jun 2022 17:14:45 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=reinpetersen-com.20210112.gappssmtp.com; s=20210112; h=mime-version:references:in-reply-to:from:date:message-id:subject:to; bh=CPJ7+70aBke0CXJ5cG2j1OCRTKcW1zBstuXs3QFcntk=; b=5g8NNAcOjc+Fl3H/CssSXxWjsQRTW0jq27/PMGUMTVRCfhjZGPBsWpVYEogC5l863T d9NMAG5hn8iXcdX8PBWPUZZ9kIdiKmQNblGcu+jv9HE7CGm8FjpOVlxDmHAcy5Og75LX 9MeBxPZOp5HQtOCKXXAWAqG3wRZA3abryAUAog7sh/xeN+0CrlllAlpOzZVsm9fJlFdR o3c5eFpIBQtz7NizykEkrbQPmEH2cfeEgZhvFcLF//o6kRtxFykenPaZjZL2l83U7+52 YYo5yfHKvy8xcQa0FbNguzkQdQXxVYecsdPb5JgUNvnOH26FrDZzIVjK0zs34KnoGpUw t0tw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:mime-version:references:in-reply-to:from:date :message-id:subject:to; bh=CPJ7+70aBke0CXJ5cG2j1OCRTKcW1zBstuXs3QFcntk=; b=gSjvJZ9ezoxCSgiDBM8gD+0tLZXkn2865BJKE/x2uJmTgnMEf6/ZQZy0g6yfyO9WzW AYAM32IJbT+SGMVU6OWSDJoAOIJZzlJjaP52HpoJgFmTxujlwVQsCgc5waieslgJqX0S bkgxOPXkBchdE47nXw+YKz2fqS2Dg25bvoU32sQNqOIFOE18V+4KbqX8BTqRgIf1fE81 EkXrzQLkC56+UZNtjVRyrP0ZPWyKfdM6Ai6PC9oaAIRA41dFoO0ZjWCfPZx95Jt7UZxU 8H6RMRLPXtT8wJ/GSOo+Kp7vEmb8TzQOs3nulgUQlS8G2eIbj/068QIkuvcWKQyFE18w 3bTA== X-Gm-Message-State: AOAM532sytW6uwueMZQUN4epQesAo6TafI8EH85Ag5/a0YyPn4YbQcvW x/5lMM2Y9AniQ1iIHQiODmujxesWuLSKQ+J1MZYHS1Y7uR1ZDrhW X-Google-Smtp-Source: ABdhPJz2rBfhSSgT0R4EbeZFF9XIpMlrvVkFDkKBiGWWx0zsFwtykLqT4+omMCF0NnyOT/31/wQxmQluTJgLYVzxUnM= X-Received: by 2002:a05:600c:4e94:b0:397:63d7:1f6c with SMTP id f20-20020a05600c4e9400b0039763d71f6cmr31844686wmq.150.1654647283863; Tue, 07 Jun 2022 17:14:43 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Rein Petersen Date: Tue, 7 Jun 2022 19:14:32 -0500 Message-ID: Subject: Re: losing my mind about sytnax error in window clause To: pgsql-sql@lists.postgresql.org Content-Type: multipart/alternative; boundary="0000000000009568e905e0e4973c" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000009568e905e0e4973c Content-Type: text/plain; charset="UTF-8" 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 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 > > > > --0000000000009568e905e0e4973c Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Fixing the typo (*PARTITION) but bump into=C2=A0ERROR= : =C2=A0FILTER is not implemented for non-aggregate window functions ....= =C2=A0

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

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 ex= haustively for examples on the internet to help me here and I'm ready t= o give up.

I have a query using windows function:<= br>
SELECT
=C2=A0 =C2=A0 last_value ( typechange ) =C2=A0 = =C2=A0 =C2=A0OVER w,
=C2=A0 =C2=A0 pindex,
=C2=A0 =C2= =A0 lid,
=C2=A0 =C2=A0 last_value ( modified ) =C2=A0 OVER w,
=C2=A0 =C2=A0 last_value ( created ) =C2=A0 =C2=A0OVER w,
= =C2=A0 =C2=A0 last_value ( modifiedby ) OVER w,
=C2=A0 =C2=A0 las= t_value ( createby ) =C2=A0 OVER w,
=C2=A0 =C2=A0 last_value ( cost ) FILTER ( WHERE co= st IS NOT NULL ) OVER w,
=C2=A0 =C2=A0 la= st_value ( sell ) FILTER ( WHERE sell IS NOT NULL ) OVER w
FROM ps._delta_ext
WHERE pindex =3D Ppindex AND
=C2=A0 =C2=A0= ( Plid IS NULL OR lid =3D Plid ) AND
=C2=A0 =C2= =A0 ( Ptimestamp IS NULL OR modified <=3D Ptimestamp )
GROUP BY lid, pindex
WINDOW w AS ( PARITIO= N BY lid, pindex ORDER BY crea= ted );
Complains about syntax error in the WINDOW cau= se (last line) on [PARTITION] BY:
=
/* messages
ERROR: =C2=A0syntax error at or near &qu= ot;BY"
LINE 16:= =C2=A0WINDOW w AS ( PARITION BY locationid, partindex ORDER BY cr...
=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^
SQL state: 42601
C= haracter: 2724
*/

Maybe my error is clear to someon= e else, could really use helping hand, thanks.

Rei= n



--0000000000009568e905e0e4973c--