Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

CASE Stmt giving ORA-00920: invalid relational operator

CeritoJan 4 2018 — edited Jan 4 2018

Hi Group - I've addded an AND condition to a CASE that is used as a column.  It was working before my change.  I also created a funct, is_consortium_existing, that returns a boolean value which was tested and works.   I now get compile error PL/SQL: ORA-00920: invalid relational operator  What am I overlloking?

Original

SELECT a,

CASE

WHEN  clpsh.clps_eff_frm_dt BETWEEN get_aff_eff_frm_dt(client.id, clpsh.eff_frm_dt,clpsh.eff_to_dt) 

              AND nvl(get_aff_to_dt(client.id,clpsh.eff_frm_dt,clpsh.eff_to_dt),to_date('31-DEC-2099','DD-MON-YYYY'))

THEN 'Y'

ELSE 'N'

END aff_ind,

c

From client, clh

Where ... ... ...

New

SELECT a,

CASE

WHEN clpsh.clps_eff_frm_dt BETWEEN get_aff_eff_frm_dt(client.id, clpsh.eff_frm_dt, clpsh.eff_to_dt) 

              AND nvl(get_aff_to_dt(client.id,clpsh.eff_frm_dt,clpsh.eff_to_dt),to_date('31-DEC-2099','DD-MON-YYYY'))

AND is_consortium_existing(clh.id, clh.prtnr,  clh.cl_ch)

THEN  'Y'

ELSE  'N'

END aff_ind,

c

From client, clh

Where ...  ... ...

This post has been answered by Cerito on Jan 4 2018
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 1 2018
Added on Jan 4 2018
15 comments
4,174 views