public inbox for [email protected]
help / color / mirror / Atom feedlosing my mind about sytnax error in window clause
6+ messages / 5 participants
[nested] [flat]
* losing my mind about sytnax error in window clause
@ 2022-06-07 23:03 Rein Petersen <[email protected]>
2022-06-07 23:40 ` Re: losing my mind about sytnax error in window clause Tom Lane <[email protected]>
2022-06-07 23:45 ` Re: losing my mind about sytnax error in window clause Rob Sargent <[email protected]>
2022-06-07 23:46 ` Re: losing my mind about sytnax error in window clause Bob Edwards <[email protected]>
2022-06-08 00:14 ` Re: losing my mind about sytnax error in window clause Rein Petersen <[email protected]>
0 siblings, 4 replies; 6+ messages in thread
From: Rein Petersen @ 2022-06-07 23:03 UTC (permalink / raw)
To: [email protected]
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: losing my mind about sytnax error in window clause
2022-06-07 23:03 losing my mind about sytnax error in window clause Rein Petersen <[email protected]>
@ 2022-06-07 23:40 ` Tom Lane <[email protected]>
3 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2022-06-07 23:40 UTC (permalink / raw)
To: Rein Petersen <[email protected]>; +Cc: [email protected]
Rein Petersen <[email protected]> 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: losing my mind about sytnax error in window clause
2022-06-07 23:03 losing my mind about sytnax error in window clause Rein Petersen <[email protected]>
@ 2022-06-07 23:45 ` Rob Sargent <[email protected]>
3 siblings, 0 replies; 6+ messages in thread
From: Rob Sargent @ 2022-06-07 23:45 UTC (permalink / raw)
To: Rein Petersen <[email protected]>; +Cc: [email protected]
> On Jun 7, 2022, at 7:03 PM, Rein Petersen <[email protected]> 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)?
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: losing my mind about sytnax error in window clause
2022-06-07 23:03 losing my mind about sytnax error in window clause Rein Petersen <[email protected]>
@ 2022-06-07 23:46 ` Bob Edwards <[email protected]>
3 siblings, 0 replies; 6+ messages in thread
From: Bob Edwards @ 2022-06-07 23:46 UTC (permalink / raw)
To: [email protected]
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.
>
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: losing my mind about sytnax error in window clause
2022-06-07 23:03 losing my mind about sytnax error in window clause Rein Petersen <[email protected]>
@ 2022-06-08 00:14 ` Rein Petersen <[email protected]>
2022-06-08 00:48 ` Re: losing my mind about sytnax error in window clause Ben Tilly <[email protected]>
3 siblings, 1 reply; 6+ messages in thread
From: Rein Petersen @ 2022-06-08 00:14 UTC (permalink / raw)
To: [email protected]
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 protected]> 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
>
>
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: losing my mind about sytnax error in window clause
2022-06-07 23:03 losing my mind about sytnax error in window clause Rein Petersen <[email protected]>
2022-06-08 00:14 ` Re: losing my mind about sytnax error in window clause Rein Petersen <[email protected]>
@ 2022-06-08 00:48 ` Ben Tilly <[email protected]>
0 siblings, 0 replies; 6+ messages in thread
From: Ben Tilly @ 2022-06-08 00:48 UTC (permalink / raw)
To: Rein Petersen <[email protected]>; +Cc: [email protected]
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 protected]> 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 protected]>
> 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
>>
>>
>>
>>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2022-06-08 00:48 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-06-07 23:03 losing my mind about sytnax error in window clause Rein Petersen <[email protected]>
2022-06-07 23:40 ` Tom Lane <[email protected]>
2022-06-07 23:45 ` Rob Sargent <[email protected]>
2022-06-07 23:46 ` Bob Edwards <[email protected]>
2022-06-08 00:14 ` Rein Petersen <[email protected]>
2022-06-08 00:48 ` Ben Tilly <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox