From: Thomas Kellerer <spam_eater@gmx.net>
To: pgsql-sql@postgresql.org
Subject: Re: ORACLE PROCEDURE TO POSTGRES FUNCTION -ERROR IN THE OVER PART
Date: Fri, 11 Nov 2011 09:07:40 +0100
Message-ID: <j9il2o$257$1@dough.gmane.org> (raw)
In-Reply-To: <SNT122-W297468F415FB960FDE49C2C1D90@phx.gbl>
References: <SNT122-W545857C13D8050E1185C15C1D40@phx.gbl>
<SNT122-W297468F415FB960FDE49C2C1D90@phx.gbl>
Manu T, 07.11.2011 08:18:
> I am using this query in the procedure and i error is throwing as mentioned below.and i want to convert the same oracle query into Postgresql.
>
> ERROR-->
>
> *ERROR: syntax error at or near "OVER"
> LINE 1: ...heme_id,d.value, d.dr_cr_flg , d.rule_id , RANK() OVER (part...
> ^
> QUERY: SELECT d1.scheme_id,d1.value, d1.dr_cr_flg from ( select d.scheme_id,d.value, d.dr_cr_flg , d.rule_id , RANK() OVER (partition by d.rule_id order by to_number(d.value) desc) AS rk from ( select b.scheme_id, b.rule_id, to_number( CASE b.value_type WHEN '%' THEN to_number((select COALESCE((b.scheme_value * a.base_miles)/100,0) from point_mtrx_acrul a,rule_matrix b where a.ORG = $1 and a.DEST = $2 and $3 between a.EFF_DT and a.EXP_DT and a.ARLN_NBR_CD = $4 )) ELSE b.SCHEME_VALUE END ) as value1, b.dr_cr_flg from rule_matrix b ,scheme_mstr c where b.rule_id = $5 and b.scheme_id = c.scheme_id and $3 between c.EFF_DT and c.EXP_DT and b.value_type not in ('AWARD') ) d ) d1, scheme_mstr c where d1.rk<=1 and c.scheme_id=d1.scheme_id and $3 between c.EFF_DT and c.EXP_DT and rownum=1
> CONTEXT: SQL statement in PL/PgSQL function "rule_engine" near line 563
>
> ********** Error **********
>
> ERROR: syntax error at or near "OVER"
> SQL state: 42601
> Context: SQL statement in PL/PgSQL function "rule_engine" near line 563
Windowing functions where introduced in Version 8.4. Which version are you using?
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: spam_eater@gmx.net
Subject: Re: ORACLE PROCEDURE TO POSTGRES FUNCTION -ERROR IN THE OVER PART
In-Reply-To: <j9il2o$257$1@dough.gmane.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox