Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 3BF4D1B7A3EA for ; Mon, 7 Nov 2011 03:34:20 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 80065-02-3 for ; Mon, 7 Nov 2011 07:34:04 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.8.0-rc2 Received: from snt0-omc2-s36.snt0.hotmail.com (snt0-omc2-s36.snt0.hotmail.com [65.55.90.111]) by mail.postgresql.org (Postfix) with ESMTP id 5D900D7D1F6 for ; Mon, 7 Nov 2011 03:18:33 -0400 (AST) Received: from SNT122-W29 ([65.55.90.72]) by snt0-omc2-s36.snt0.hotmail.com with Microsoft SMTPSVC(6.0.3790.4675); Sun, 6 Nov 2011 23:18:30 -0800 Message-ID: Content-Type: multipart/alternative; boundary="_56d52f89-ba3b-4e29-a63e-a9c0a5855ad4_" X-Originating-IP: [49.248.12.66] From: Manu T To: Subject: ORACLE PROCEDURE TO POSTGRES FUNCTION -ERROR IN THE OVER PART Date: Mon, 7 Nov 2011 12:48:30 +0530 Importance: Normal In-Reply-To: References: MIME-Version: 1.0 X-OriginalArrivalTime: 07 Nov 2011 07:18:30.0192 (UTC) FILETIME=[735BF300:01CC9D1D] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.493 tagged_above=-5 required=5 tests=BAYES_00=-1.9, FREEMAIL_ENVFROM_END_DIGIT=0.1, FREEMAIL_FROM=0.001, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_NONE=-0.0001, RP_MATCHES_RCVD=-1.201, SUBJ_ALL_CAPS=1.506 X-Spam-Level: X-Archive-Number: 201111/52 X-Sequence-Number: 36025 --_56d52f89-ba3b-4e29-a63e-a9c0a5855ad4_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable select d1.scheme_id=2Cd1.value =2C d1.dr_cr_flg INTO po_appl_scheme=2Cpo_scheme_val=2C po_dr_cr_flag =20 from ( select d.scheme_id=2Cd.value=2C d.dr_cr_flg =2C d= .rule_id =2C dense_rank() over (partition by d.rule_id order by = to_number(d.value) desc ) rk=20 from ( select b.scheme_id=2C=20 b.rule_id=2C to_number(decode(b.value_type=2C'%'=2Cto_= number(( select nvl((b.scheme_value*a.BASE_MILES)/= 100=2C0) from point_mtrx_acrul a where a.ORG =3D pi_org and a.DEST =3D pi_dest and pi_flt_dt between a.EFF_DT and a.E= XP_DT=20 and a.ARLN_NBR_CD =3D pi_arln_nbr_cd= ))=2Cb.SCHEME_VALUE))=20 as value=2C b.dr_cr_flg =20 from rule_matrix b =2Cscheme_mstr c where b.rule_id =3D po_appl_rule and b.scheme_id =3D c.scheme_id and pi_flt_dt between c.EFF_DT and c.EXP_DT= =20 and b.value_type not in ('AWARD') )d=20 )d1=2C scheme_mstr c where d1.rk<=3D1 and c.scheme_id=3Dd1.scheme_id and pi_flt_dt between c.EFF_DT and c.EXP_DT and rownum=3D1=3B =20 =20 else =20 =20 select d1.scheme_id=2Cd1.value =2C d1.dr_cr_flg INTO po_appl_scheme=2Cpo_scheme_val=2C po_dr_cr_flag =20 from ( select d.scheme_id=2Cd.value=2C d.dr_cr_flg =2C d= .rule_id =2C dense_rank() over (partition by d.rule_id order by = to_number(d.value) desc ) rk=20 from ( select b.scheme_id=2C=20 b.rule_id=2C to_number(decode(b.value_type=2C'%'=2Cto_= number(( select nvl((b.scheme_value*(decode(pi_bok= _cls=2C'F'=2Ca.f_miles=2C'C'=2Cc_miles=2Cy_miles)))/100=2C0) from point_mtrx_redem a where a.ORG =3D pi_org and a.DEST =3D pi_dest and pi_flt_dt between a.EFF_DT and a.E= XP_DT=20 and a.ARLN_NBR_CD =3D pi_arln_nbr_cd= ))=2Cb.SCHEME_VALUE))=20 as value=2C b.dr_cr_flg =20 from rule_matrix b =2Cscheme_mstr c where b.rule_id =3D po_appl_rule and b.scheme_id =3D c.scheme_id and pi_flt_dt between c.EFF_DT and c.EXP_DT= =20 and b.value_type not in ('AWARD') )d=20 )d1=2C scheme_mstr c where d1.rk<=3D1 and c.scheme_id=3Dd1.scheme_id and pi_flt_dt between c.EFF_DT and c.EXP_DT and rownum=3D1=3B end if=3B =20 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=2Cd.value=2C d.dr_cr_flg =2C d.rule_id =2C RANK() OVER (= part... ^ QUERY: SELECT d1.scheme_id=2Cd1.value=2C d1.dr_cr_flg from ( select d.sch= eme_id=2Cd.value=2C d.dr_cr_flg =2C d.rule_id =2C RANK() OVER (partition by= d.rule_id order by to_number(d.value) desc) AS rk from ( select b.scheme_i= d=2C b.rule_id=2C to_number( CASE b.value_type WHEN '%' THEN to_number((sel= ect COALESCE((b.scheme_value * a.base_miles)/100=2C0) from point_mtrx_acrul= a=2Crule_matrix b where a.ORG =3D $1 and a.DEST =3D $2 and $3 betwee= n a.EFF_DT and a.EXP_DT and a.ARLN_NBR_CD =3D $4 )) ELSE b.SCHEME_VALUE E= ND ) as value1=2C b.dr_cr_flg from rule_matrix b =2Cscheme_mstr c where b.r= ule_id =3D $5 and b.scheme_id =3D c.scheme_id and $3 between c.EFF_DT a= nd c.EXP_DT and b.value_type not in ('AWARD') ) d ) d1=2C scheme_mstr c whe= re d1.rk<=3D1 and c.scheme_id=3Dd1.scheme_id and $3 between c.EFF_DT and = c.EXP_DT and rownum=3D1 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 = --_56d52f89-ba3b-4e29-a63e-a9c0a5855ad4_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
 =3B select d1.scheme_id=2Cd1.value =3B =3B =3B =3B = =2C d1.dr_cr_flg
 =3B =3B =3B =3B =3B =3B = =3B =3B =3B =3B =3B =3B =3B =3B =3B =3B=  =3B INTO po_appl_scheme=2Cpo_scheme_val=2C po_dr_cr_flag =3B
&= nbsp=3B =3B =3B =3B =3B =3B =3B =3B =3B&nbs= p=3B =3B =3B =3B =3B =3B =3B =3B from ( select = d.scheme_id=2Cd.value=2C d.dr_cr_flg =3B =2C d.rule_id =2C
 =3B&= nbsp=3B =3B =3B =3B =3B =3B =3B =3B =3B&nbs= p=3B =3B =3B =3B =3B =3B =3B =3B =3B = =3B =3B =3B =3B dense_rank() over (partition by d.rule_id order= by to_number(d.value) desc =3B ) rk
 =3B =3B =3B = =3B =3B =3B =3B =3B =3B =3B =3B =3B =3B=  =3B =3B =3B =3B =3B =3B =3B =3B =3B&nb= sp=3B from ( select =3B =3B =3B b.scheme_id=2C
 =3B&nbs= p=3B =3B =3B =3B =3B =3B =3B =3B =3B = =3B =3B =3B =3B =3B =3B =3B =3B =3B =3B=  =3B =3B =3B =3B =3B =3B =3B =3B =3B&nb= sp=3B =3B =3B =3B =3B =3B =3B =3B =3B = =3B =3B b.rule_id=2C
 =3B =3B =3B =3B =3B = =3B =3B =3B =3B =3B =3B =3B =3B =3B =3B=  =3B =3B =3B =3B =3B =3B =3B =3B =3B&nb= sp=3B =3B =3B =3B =3B =3B =3B =3B =3B to_nu= mber(decode(b.value_type=2C'%'=2Cto_number((
 =3B =3B =3B&nb= sp=3B =3B =3B =3B =3B =3B =3B =3B =3B = =3B =3B =3B =3B =3B =3B =3B =3B =3B =3B=  =3B =3B =3B =3B =3B =3B =3B =3B =3B&nb= sp=3B =3B select nvl((b.scheme_value*a.BASE_MILES)/100=2C0)
 =3B=  =3B =3B =3B =3B =3B =3B =3B =3B =3B&nb= sp=3B =3B =3B =3B =3B =3B =3B =3B =3B = =3B =3B =3B =3B =3B =3B =3B =3B =3B =3B=  =3B =3B =3B =3B from =3B =3B point_mtrx_acrul a =3B =3B =3B =3B =3B =3B =3B =3B =3B&n= bsp=3B =3B =3B =3B =3B =3B =3B =3B =3B = =3B =3B =3B =3B =3B =3B =3B =3B =3B =3B=  =3B =3B =3B =3B =3B where =3B a.ORG =3B = =3B =3B =3B =3B =3B =3B =3B =3B =3B =3D pi_= org
 =3B =3B =3B =3B =3B =3B =3B =3B&nbs= p=3B =3B =3B =3B =3B =3B =3B =3B =3B = =3B =3B =3B =3B =3B =3B =3B =3B =3B =3B=  =3B =3B =3B =3B =3B =3B and =3B =3B = =3B a.DEST =3B =3B =3B =3B =3B =3B =3B =3B&= nbsp=3B =3D pi_dest
 =3B =3B =3B =3B =3B =3B&nbs= p=3B =3B =3B =3B =3B =3B =3B =3B =3B = =3B =3B =3B =3B =3B =3B =3B =3B =3B =3B=  =3B =3B =3B =3B =3B =3B =3B =3B and = =3B =3B =3B pi_flt_dt between a.EFF_DT and a.EXP_DT
 =3B&nb= sp=3B =3B =3B =3B =3B =3B =3B =3B =3B = =3B =3B =3B =3B =3B =3B =3B =3B =3B =3B=  =3B =3B =3B =3B =3B =3B =3B =3B =3B&nb= sp=3B =3B =3B =3B and =3B =3B =3B a.ARLN_NBR_CD&nbs= p=3B =3B =3D pi_arln_nbr_cd ))=2Cb.SCHEME_VALUE))
 =3B =3B&= nbsp=3B =3B =3B =3B =3B =3B =3B =3B =3B&nbs= p=3B =3B =3B =3B =3B =3B =3B =3B =3B = =3B =3B =3B =3B =3B =3B =3B =3B =3B =3B=  =3B =3B =3B =3B =3B =3B =3B =3B =3B&nb= sp=3B as value=2C
 =3B =3B =3B =3B =3B =3B = =3B =3B =3B =3B =3B =3B =3B =3B =3B =3B=  =3B =3B =3B =3B =3B =3B =3B =3B =3B&nb= sp=3B =3B =3B =3B =3B =3B =3B =3B =3B = =3B =3B =3B =3B =3B =3B b.dr_cr_flg =3B =3B&nbs= p=3B =3B =3B =3B =3B =3B =3B =3B =3B = =3B =3B =3B =3B =3B =3B =3B =3B
 =3B&nb= sp=3B =3B =3B =3B =3B =3B =3B =3B =3B = =3B =3B =3B =3B =3B =3B =3B =3B =3B =3B=  =3B =3B =3B =3B =3B =3B =3B =3B =3B fr= om rule_matrix b =2Cscheme_mstr c
 =3B =3B =3B =3B = =3B =3B =3B =3B =3B =3B =3B =3B =3B =3B=  =3B =3B =3B =3B =3B =3B =3B =3B =3B&nb= sp=3B =3B =3B =3B =3B =3B where b.rule_id =3B = =3B =3D po_appl_rule
 =3B =3B =3B =3B =3B =3B&nb= sp=3B =3B =3B =3B =3B =3B =3B =3B =3B = =3B =3B =3B =3B =3B =3B =3B =3B =3B =3B=  =3B =3B =3B =3B and =3B =3B b.scheme_id =3D c.sche= me_id
 =3B =3B =3B =3B =3B =3B =3B =3B&n= bsp=3B =3B =3B =3B =3B =3B =3B =3B =3B = =3B =3B =3B =3B =3B =3B =3B =3B =3B =3B=  =3B =3B and =3B =3B pi_flt_dt between c.EFF_DT and c.EXP_D= T
 =3B =3B =3B =3B =3B =3B =3B =3B = =3B =3B =3B =3B =3B =3B =3B =3B =3B =3B=  =3B =3B =3B =3B =3B =3B =3B =3B =3B&nb= sp=3B =3B and =3B =3B b.value_type =3B not in ('AWARD')
=  =3B =3B =3B =3B =3B =3B =3B =3B =3B&nb= sp=3B =3B =3B =3B =3B =3B =3B =3B =3B = =3B =3B =3B =3B =3B =3B =3B =3B =3B =3B= )d
 =3B =3B =3B =3B =3B =3B =3B =3B&nb= sp=3B =3B =3B =3B =3B =3B =3B =3B =3B = =3B =3B =3B =3B =3B )d1=2C scheme_mstr c
 =3B = =3B =3B =3B =3B =3B =3B =3B =3B =3B =3B=  =3B =3B =3B =3B =3B where d1.rk<=3B=3D1
 =3B&= nbsp=3B =3B =3B =3B =3B =3B =3B =3B =3B&nbs= p=3B =3B =3B =3B =3B =3B and c.scheme_id=3Dd1.scheme_id=
 =3B =3B =3B =3B =3B =3B =3B =3B = =3B =3B =3B =3B =3B =3B =3B =3B and pi_flt_dt b= etween c.EFF_DT and c.EXP_DT
 =3B =3B =3B =3B =3B&nb= sp=3B =3B =3B =3B =3B =3B =3B =3B =3B = =3B =3B and rownum=3D1=3B
 =3B =3B =3B =3B =3B&n= bsp=3B =3B =3B =3B =3B =3B =3B =3B =3B = =3B
 =3B =3B =3B =3B =3B =3B =3B =3B&nb= sp=3B =3B =3B =3B =3B =3B =3B =3B =3B = =3B
 =3B =3B =3B =3B =3B =3B =3B =3B&nb= sp=3B =3B =3B =3B =3B =3B else =3B
 =3B&nbs= p=3B =3B =3B =3B =3B =3B =3B =3B =3B = =3B =3B
 =3B =3B =3B =3B =3B =3B =3B&nb= sp=3B =3B =3B =3B =3B =3B =3B =3B =3B = =3B select d1.scheme_id=2Cd1.value =3B =3B =3B =3B =2C d1.d= r_cr_flg
 =3B =3B =3B =3B =3B =3B =3B = =3B =3B =3B =3B =3B =3B =3B =3B =3B =3B= INTO po_appl_scheme=2Cpo_scheme_val=2C po_dr_cr_flag =3B
 =3B&= nbsp=3B =3B =3B =3B =3B =3B =3B =3B =3B&nbs= p=3B =3B =3B =3B =3B =3B =3B from ( select d.scheme= _id=2Cd.value=2C d.dr_cr_flg =3B =2C d.rule_id =2C
 =3B =3B&= nbsp=3B =3B =3B =3B =3B =3B =3B =3B =3B&nbs= p=3B =3B =3B =3B =3B =3B =3B =3B =3B = =3B =3B =3B dense_rank() over (partition by d.rule_id order by to_n= umber(d.value) desc =3B ) rk
 =3B =3B =3B =3B = =3B =3B =3B =3B =3B =3B =3B =3B =3B =3B=  =3B =3B =3B =3B =3B =3B =3B =3B =3B fr= om ( select =3B =3B =3B b.scheme_id=2C
 =3B =3B&nbs= p=3B =3B =3B =3B =3B =3B =3B =3B =3B = =3B =3B =3B =3B =3B =3B =3B =3B =3B =3B=  =3B =3B =3B =3B =3B =3B =3B =3B =3B&nb= sp=3B =3B =3B =3B =3B =3B =3B =3B =3B = =3B b.rule_id=2C
 =3B =3B =3B =3B =3B =3B = =3B =3B =3B =3B =3B =3B =3B =3B =3B =3B=  =3B =3B =3B =3B =3B =3B =3B =3B =3B&nb= sp=3B =3B =3B =3B =3B =3B =3B =3B to_number(dec= ode(b.value_type=2C'%'=2Cto_number((
 =3B =3B =3B =3B&nb= sp=3B =3B =3B =3B =3B =3B =3B =3B =3B = =3B =3B =3B =3B =3B =3B =3B =3B =3B =3B=  =3B =3B =3B =3B =3B =3B =3B =3B =3B&nb= sp=3B select nvl((b.scheme_value*(decode(pi_bok_cls=2C'F'=2Ca.f_miles=2C'C'= =2Cc_miles=2Cy_miles)))/100=2C0)
 =3B =3B =3B =3B = =3B =3B =3B =3B =3B =3B =3B =3B =3B =3B=  =3B =3B =3B =3B =3B =3B =3B =3B =3B&nb= sp=3B =3B =3B =3B =3B =3B =3B =3B =3B = =3B from =3B =3B point_mtrx_redem a
 =3B =3B =3B&nbs= p=3B =3B =3B =3B =3B =3B =3B =3B =3B = =3B =3B =3B =3B =3B =3B =3B =3B =3B =3B=  =3B =3B =3B =3B =3B =3B =3B =3B =3B&nb= sp=3B =3B where =3B a.ORG =3B =3B =3B =3B =3B&n= bsp=3B =3B =3B =3B =3B =3D pi_org
 =3B =3B = =3B =3B =3B =3B =3B =3B =3B =3B =3B =3B=  =3B =3B =3B =3B =3B =3B =3B =3B =3B&nb= sp=3B =3B =3B =3B =3B =3B =3B =3B =3B = =3B =3B =3B and =3B =3B =3B a.DEST =3B =3B = =3B =3B =3B =3B =3B =3B =3B =3D pi_dest
 =3B=  =3B =3B =3B =3B =3B =3B =3B =3B =3B&nb= sp=3B =3B =3B =3B =3B =3B =3B =3B =3B = =3B =3B =3B =3B =3B =3B =3B =3B =3B =3B=  =3B =3B =3B =3B and =3B =3B =3B pi_flt_dt betw= een a.EFF_DT and a.EXP_DT
 =3B =3B =3B =3B =3B = =3B =3B =3B =3B =3B =3B =3B =3B =3B =3B=  =3B =3B =3B =3B =3B =3B =3B =3B =3B&nb= sp=3B =3B =3B =3B =3B =3B =3B =3B =3B and&n= bsp=3B =3B =3B a.ARLN_NBR_CD =3B =3B =3D pi_arln_nbr_cd ))= =2Cb.SCHEME_VALUE))
 =3B =3B =3B =3B =3B =3B&nb= sp=3B =3B =3B =3B =3B =3B =3B =3B =3B = =3B =3B =3B =3B =3B =3B =3B =3B =3B =3B=  =3B =3B =3B =3B =3B =3B =3B =3B =3B&nb= sp=3B =3B =3B =3B =3B =3B as value=2C
 =3B = =3B =3B =3B =3B =3B =3B =3B =3B =3B =3B=  =3B =3B =3B =3B =3B =3B =3B =3B =3B&nb= sp=3B =3B =3B =3B =3B =3B =3B =3B =3B = =3B =3B =3B =3B =3B =3B =3B =3B =3B =3B=  =3B b.dr_cr_flg =3B =3B =3B =3B =3B =3B = =3B =3B =3B =3B =3B =3B =3B =3B =3B =3B=  =3B =3B =3B
 =3B =3B =3B =3B =3B = =3B =3B =3B =3B =3B =3B =3B =3B =3B =3B=  =3B =3B =3B =3B =3B =3B =3B =3B =3B&nb= sp=3B =3B =3B =3B =3B from rule_matrix b =2Cscheme_mstr c =3B =3B =3B =3B =3B =3B =3B =3B =3B&= nbsp=3B =3B =3B =3B =3B =3B =3B =3B =3B&nbs= p=3B =3B =3B =3B =3B =3B =3B =3B =3B = =3B =3B where b.rule_id =3B =3B =3D po_appl_rule
 =3B&nb= sp=3B =3B =3B =3B =3B =3B =3B =3B =3B = =3B =3B =3B =3B =3B =3B =3B =3B =3B =3B=  =3B =3B =3B =3B =3B =3B =3B =3B =3B an= d =3B =3B b.scheme_id =3D c.scheme_id
 =3B =3B =3B&n= bsp=3B =3B =3B =3B =3B =3B =3B =3B =3B = =3B =3B =3B =3B =3B =3B =3B =3B =3B =3B=  =3B =3B =3B =3B =3B =3B =3B and =3B = =3B pi_flt_dt between c.EFF_DT and c.EXP_DT
 =3B =3B =3B&nb= sp=3B =3B =3B =3B =3B =3B =3B =3B =3B = =3B =3B =3B =3B =3B =3B =3B =3B =3B =3B=  =3B =3B =3B =3B =3B =3B =3B and =3B = =3B b.value_type =3B not in ('AWARD')
 =3B =3B =3B = =3B =3B =3B =3B =3B =3B =3B =3B =3B =3B=  =3B =3B =3B =3B =3B =3B =3B =3B =3B&nb= sp=3B =3B =3B =3B =3B =3B )d
 =3B =3B = =3B =3B =3B =3B =3B =3B =3B =3B =3B =3B=  =3B =3B =3B =3B =3B =3B =3B =3B =3B&nb= sp=3B )d1=2C scheme_mstr c
 =3B =3B =3B =3B =3B = =3B =3B =3B =3B =3B =3B =3B =3B =3B =3B=  =3B where d1.rk<=3B=3D1
 =3B =3B =3B =3B =3B&= nbsp=3B =3B =3B =3B =3B =3B =3B =3B =3B&nbs= p=3B =3B and c.scheme_id=3Dd1.scheme_id
 =3B =3B =3B&nbs= p=3B =3B =3B =3B =3B =3B =3B =3B =3B = =3B =3B =3B =3B and pi_flt_dt between c.EFF_DT and c.EXP_DT
=  =3B =3B =3B =3B =3B =3B =3B =3B =3B&nb= sp=3B =3B =3B =3B =3B =3B =3B and rownum=3D1=3B
=  =3B =3B =3B =3B =3B =3B =3B =3B =3B&nb= sp=3B =3B =3B =3B =3B =3B end if=3B
 =3B&nbs= p=3B =3B =3B =3B =3B =3B =3B =3B =3B = =3B =3B =3B =3B =3B
I am using th= is query in the procedure and i error is throwing as mentioned below.and i = want to convert the same oracle =3B query into Postgresql.

ERROR-->=3B

ERROR: =3B syntax error at or near "OVER"
LINE 1: ...heme_id=2Cd.value=2C d.dr_cr_flg =2C d.rule_id =2C = RANK() OVER (part...
 =3B =3B =3B =3B =3B&n= bsp=3B =3B =3B =3B =3B =3B =3B =3B =3B = =3B =3B =3B =3B =3B =3B =3B =3B =3B =3B=  =3B =3B =3B =3B =3B =3B =3B =3B =3B&nb= sp=3B =3B =3B =3B =3B =3B =3B =3B =3B = =3B =3B =3B =3B =3B =3B =3B =3B =3B =3B=  =3B =3B =3B =3B =3B =3B =3B =3B ^
QUERY: =3B SELECT =3B d1.scheme_id=2Cd1.value=2C d1.dr_cr_flg f= rom ( select d.scheme_id=2Cd.value=2C d.dr_cr_flg =2C d.rule_id =2C RANK() = OVER (partition by d.rule_id order by to_number(d.value) desc) AS rk from (= select b.scheme_id=2C b.rule_id=2C to_number( CASE b.value_type WHEN '%' T= HEN to_number((select COALESCE((b.scheme_value * a.base_miles)/100=2C0) fro= m point_mtrx_acrul a=2Crule_matrix b where a.ORG =3D =3B $1 =3B and= a.DEST =3D =3B $2 =3B and =3B $3 =3B between a.EFF_DT and = a.EXP_DT and a.ARLN_NBR_CD =3D =3B $4 =3B )) ELSE b.SCHEME_VALUE EN= D ) as value1=2C b.dr_cr_flg from rule_matrix b =2Cscheme_mstr c where b.ru= le_id =3D =3B $5 =3B and b.scheme_id =3D c.scheme_id and =3B $3=  =3B between c.EFF_DT and c.EXP_DT and b.value_type not in ('AWARD') ) = d ) d1=2C scheme_mstr c where d1.rk<=3B=3D1 and c.scheme_id=3Dd1.scheme_i= d and =3B $3 =3B between c.EFF_DT and c.EXP_DT and rownum=3D1
CONTEXT: =3B SQL statement in PL/PgSQL function "rule_engine" ne= ar line 563

*****= ***** Error **********
=
ERROR: syntax error at or near "OVER"
SQL state: 42601
Context: SQL statement in PL/PgSQL function "rule_engine" near li= ne 563


<= /div> = --_56d52f89-ba3b-4e29-a63e-a9c0a5855ad4_--