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 1nyitE-00081L-QN for pgsql-sql@arkaria.postgresql.org; Tue, 07 Jun 2022 23:45:36 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1nyitD-00027N-LQ for pgsql-sql@arkaria.postgresql.org; Tue, 07 Jun 2022 23:45:35 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nyitD-00027E-61 for pgsql-sql@lists.postgresql.org; Tue, 07 Jun 2022 23:45:35 +0000 Received: from mail-pf1-x42d.google.com ([2607:f8b0:4864:20::42d]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1nyit6-0006ym-Jh for pgsql-sql@lists.postgresql.org; Tue, 07 Jun 2022 23:45:34 +0000 Received: by mail-pf1-x42d.google.com with SMTP id bo5so16864774pfb.4 for ; Tue, 07 Jun 2022 16:45:28 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=from:message-id:mime-version:subject:date:in-reply-to:cc:to :references; bh=mot5lbdqo2D1D/oPr0PHBcNaNzP6iNhS0EEDdnB+81Y=; b=eOdPQ5dB8IkOCRkALCRlCYiHLw1G9Wmi8SU20Uiu3iEoMc5HnPLHCqBRz2EUPGCZMG Y3nxvh5PnAF40A8eBt0qW80eZ95dW5Ph4LA4ZNIjsgzPKzuAtOPVOlj8UpsGSjtgFXzG rkI1e+VzXb8+/RcjhO4vQsVzth72sU6feFNRqEDET48S/+pNa4G70zeDhgBkPgAuTk/Q u3JFoqgTj68FodGC4XEh1dUN0At+jFUkGFLLQfjCIeDp2BafNhqHD7Gso2+Pem5x2/nD MvnLFRJ+YALFsm0k1DfzPjubU/lbUbvBCRfhYOzM3XAdj8tOZ6xQNa27f5dppUor1GUL 8yAQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:from:message-id:mime-version:subject:date :in-reply-to:cc:to:references; bh=mot5lbdqo2D1D/oPr0PHBcNaNzP6iNhS0EEDdnB+81Y=; b=jOnwrVWZms8ZgaRSKlyboqtkNdM2csE1YSIPJ8q3f1AsXb1CMSphZOyQYIBaDSiewc pUlmxzcQU3zSvoDmN+JjR0r8/QjsuvD4HsA9hNgk9rYHErEPvtRxQILjAPUPyDVGdcU+ fE7hYX5CaCBFVraOaXfwTJdsRT9B1XN71ejyVFl1MY/6TCfE3Qggtei/D2Y3q81tMCqR KVoqjprer4oUuhoBblrYcHGyxktd99UJ7NSBWPhuJlLMMwyv8LKzqCp89s31PMWORoS2 bjZZZlT/C/P+vqC6Z1abFAphzaBYGsKRqFkJS7cD8jxNlA4itLngsRgAabv5qBAzQtDi 6UeQ== X-Gm-Message-State: AOAM533Qr8/c4ZVjrCF/sKNaancZfYOGrvoCehNn7WOy4PzdZ+qmG+2O wVOqa2vxe9IkNFi7or3NAxk= X-Google-Smtp-Source: ABdhPJxciQQem/SMWzq9pkS3Q3q1YMunQa223XJxV/5JwN0nKsI0CSSHd09oe38v460YKgpzbVWzjA== X-Received: by 2002:a63:8a42:0:b0:3fc:f8b5:291f with SMTP id y63-20020a638a42000000b003fcf8b5291fmr24547673pgd.550.1654645527396; Tue, 07 Jun 2022 16:45:27 -0700 (PDT) Received: from smtpclient.apple ([155.98.131.1]) by smtp.gmail.com with ESMTPSA id c20-20020a621c14000000b0051bc5f4df1csm11582093pfc.154.2022.06.07.16.45.26 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Tue, 07 Jun 2022 16:45:26 -0700 (PDT) From: Rob Sargent Message-Id: <884E562F-4402-44B6-90C5-F50EEFA99320@gmail.com> Content-Type: multipart/alternative; boundary="Apple-Mail=_7B84D5EE-C2EE-4197-BD4C-AE9B4783A8C1" Mime-Version: 1.0 (Mac OS X Mail 14.0 \(3654.120.0.1.13\)) Subject: Re: losing my mind about sytnax error in window clause Date: Tue, 7 Jun 2022 19:45:24 -0400 In-Reply-To: Cc: pgsql-sql@lists.postgresql.org To: Rein Petersen References: X-Mailer: Apple Mail (2.3654.120.0.1.13) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_7B84D5EE-C2EE-4197-BD4C-AE9B4783A8C1 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 > On Jun 7, 2022, at 7:03 PM, Rein Petersen = wrote: >=20 > Hi Folks, >=20 > 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. >=20 > I have a query using windows function: >=20 > 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 =3D Ppindex AND > ( Plid IS NULL OR lid =3D Plid ) AND > ( Ptimestamp IS NULL OR modified <=3D 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 > */ >=20 > Maybe my error is clear to someone else, could really use helping = hand, thanks. >=20 > Rein >=20 >=20 Why doesn=E2=80=99t your colorizer pick up =E2=80=98window=E2=80=99? = Perhaps it doesn=E2=80=99t pickup =E2=80=98partition' either (when = properly spelled)? >=20 --Apple-Mail=_7B84D5EE-C2EE-4197-BD4C-AE9B4783A8C1 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8

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 =3D = Ppindex AND
    ( Plid IS NULL OR lid =3D Plid ) = AND
    ( Ptimestamp IS NULL OR modified <=3D= 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=E2=80=99t your = colorizer pick up =E2=80=98window=E2=80=99?  Perhaps it doesn=E2=80=99= t pickup =E2=80=98partition' either (when properly spelled)?


= --Apple-Mail=_7B84D5EE-C2EE-4197-BD4C-AE9B4783A8C1--