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?




--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Reply via email to