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!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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,119 views