ORACLE PROCEDURE TO POSTGRES FUNCTION -ERROR IN THE OVER PART - Mailing list pgsql-sql
| From | Manu T |
|---|---|
| Subject | ORACLE PROCEDURE TO POSTGRES FUNCTION -ERROR IN THE OVER PART |
| Date | |
| Msg-id | SNT122-W297468F415FB960FDE49C2C1D90@phx.gbl Whole thread Raw |
| Responses |
Re: ORACLE PROCEDURE TO POSTGRES FUNCTION -ERROR IN THE OVER PART
(Thomas Kellerer <spam_eater@gmx.net>)
|
| List | pgsql-sql |
select d1.scheme_id,d1.value , d1.dr_cr_flg
INTO po_appl_scheme,po_scheme_val, po_dr_cr_flag
from ( select d.scheme_id,d.value, d.dr_cr_flg , d.rule_id ,
dense_rank() over (partition by d.rule_id order by to_number(d.value) desc ) rk
from ( select b.scheme_id,
b.rule_id,
to_number(decode(b.value_type,'%',to_number((
select nvl((b.scheme_value*a.BASE_MILES)/100,0)
from point_mtrx_acrul a
where a.ORG = pi_org
and a.DEST = pi_dest
and pi_flt_dt between a.EFF_DT and a.EXP_DT
and a.ARLN_NBR_CD = pi_arln_nbr_cd )),b.SCHEME_VALUE))
as value,
b.dr_cr_flg
from rule_matrix b ,scheme_mstr c
where b.rule_id = po_appl_rule
and b.scheme_id = c.scheme_id
and pi_flt_dt 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 pi_flt_dt between c.EFF_DT and c.EXP_DT
and rownum=1;
else
select d1.scheme_id,d1.value , d1.dr_cr_flg
INTO po_appl_scheme,po_scheme_val, po_dr_cr_flag
from ( select d.scheme_id,d.value, d.dr_cr_flg , d.rule_id ,
dense_rank() over (partition by d.rule_id order by to_number(d.value) desc ) rk
from ( select b.scheme_id,
b.rule_id,
to_number(decode(b.value_type,'%',to_number((
select nvl((b.scheme_value*(decode(pi_bok_cls,'F',a.f_miles,'C',c_miles,y_miles)))/100,0)
from point_mtrx_redem a
where a.ORG = pi_org
and a.DEST = pi_dest
and pi_flt_dt between a.EFF_DT and a.EXP_DT
and a.ARLN_NBR_CD = pi_arln_nbr_cd )),b.SCHEME_VALUE))
as value,
b.dr_cr_flg
from rule_matrix b ,scheme_mstr c
where b.rule_id = po_appl_rule
and b.scheme_id = c.scheme_id
and pi_flt_dt 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 pi_flt_dt between c.EFF_DT and c.EXP_DT
and rownum=1;
end if;
INTO po_appl_scheme,po_scheme_val, po_dr_cr_flag
from ( select d.scheme_id,d.value, d.dr_cr_flg , d.rule_id ,
dense_rank() over (partition by d.rule_id order by to_number(d.value) desc ) rk
from ( select b.scheme_id,
b.rule_id,
to_number(decode(b.value_type,'%',to_number((
select nvl((b.scheme_value*a.BASE_MILES)/100,0)
from point_mtrx_acrul a
where a.ORG = pi_org
and a.DEST = pi_dest
and pi_flt_dt between a.EFF_DT and a.EXP_DT
and a.ARLN_NBR_CD = pi_arln_nbr_cd )),b.SCHEME_VALUE))
as value,
b.dr_cr_flg
from rule_matrix b ,scheme_mstr c
where b.rule_id = po_appl_rule
and b.scheme_id = c.scheme_id
and pi_flt_dt 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 pi_flt_dt between c.EFF_DT and c.EXP_DT
and rownum=1;
else
select d1.scheme_id,d1.value , d1.dr_cr_flg
INTO po_appl_scheme,po_scheme_val, po_dr_cr_flag
from ( select d.scheme_id,d.value, d.dr_cr_flg , d.rule_id ,
dense_rank() over (partition by d.rule_id order by to_number(d.value) desc ) rk
from ( select b.scheme_id,
b.rule_id,
to_number(decode(b.value_type,'%',to_number((
select nvl((b.scheme_value*(decode(pi_bok_cls,'F',a.f_miles,'C',c_miles,y_miles)))/100,0)
from point_mtrx_redem a
where a.ORG = pi_org
and a.DEST = pi_dest
and pi_flt_dt between a.EFF_DT and a.EXP_DT
and a.ARLN_NBR_CD = pi_arln_nbr_cd )),b.SCHEME_VALUE))
as value,
b.dr_cr_flg
from rule_matrix b ,scheme_mstr c
where b.rule_id = po_appl_rule
and b.scheme_id = c.scheme_id
and pi_flt_dt 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 pi_flt_dt between c.EFF_DT and c.EXP_DT
and rownum=1;
end if;
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
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