SQL Developer: Invalid Relational Operator Error, also formatting questions

Question

I am trying to add some formatted columns to a query to our db, but I think I'm getting the operators wrong in my CASE statement.

I'm also unclear on how to write formatting for the TO_CHAR operation. Is it the same as for the TO_DATE operation? (couldn't find in oracle documentation)

I'm also unsure as to whether I should be using TO_DATE or some other call to create an object that is stored as a time from a string of four numbers.

Most importantly, my code is producing errors so I'm not seeing anything.

I am getting this error:

ORA-00920: invalid relational operator
00920. 00000 -  "invalid relational operator"
*Cause:    
*Action:
Error at Line: 53 Column: 30

From this code

/* Code written for exercize 3 in the Banner Tutorials by Alex Ackroyd on 18april2013*/
/* Objective: use functions to transform and shape data in the result set*/
/* Tables: SSRMEET ( add to SPRIDEN, SFRSTCR, SSBSECT ) */
/* Select: in addition to the columns from exercize 2, add the following values: 
    - SSRMEET_ROOM_CODE, 
    - a computed column for the start time, 
    - a computed column for the end time, and
    - a computed column that shows the days the class meets*/
/* Hint: The computed columns are created by passing table columns through functions. 
          You can use any non-analytical database function documented by Oracle to transform data from one thing to another.
          You can also write your own functions! Whoa there Shadowfax, coming soon to the tutorial near you!*/


/* Note:ssrmeet_begin_time and ssrmeet_end_time are are in a 4 character string 24hr format
        so I'm trying to read that in, convert it to a datetime type variable, 
        then convert it to the final standard format am/pm (ex: 5:00 A.M. , 3:30 P.M., etc.*/
/* Start code from this exercize, 3*/

select 
  ssrmeet.ssrmeet_room_code, 
  TO_CHAR(
          TO_DATE( ssrmeet.ssrmeet_begin_time, 'HH24MI' )
          /*, char format*/
          ), 
  TO_CHAR(
          TO_DATE( ssrmeet.ssrmeet_end_time, 'HH24MI' ) 
          /*, char format*/
          ),

  CASE
    when
/* line 53*/   ssrmeet.ssrmeet_sun_day,
             | ssrmeet.ssrmeet_mon_day, 
             | ssrmeet.ssrmeet_tue_day,
             | ssrmeet.ssrmeet_wed_day,
             | ssrmeet.ssrmeet_thu_day,
             | ssrmeet.ssrmeet_fri_day,
             | ssrmeet.ssrmeet_sat_day
      IS NOT NULL   
    END

from ssrmeet

/* End code from this exercize, 3*/

Thanks in advance for any help.

What datatype is `ssrmeet_begin_time`? It's totally useless to apply `to_date()` on a `DATE` column. — , Apr 19 '13 at 06:04

score 0 · Answer 1 · answered Apr 19 '13 at 03:46

I think the CASE construct in the SELECT clause should have a THEN block as well. Like what you want to do if one or all of the weekdays IS NOT NULL. Please refer the below example -

select
CASE
  WHEN a < b THEN 'hello'
  WHEN d < e THEN 'goodbye'
END
from my_table;

So in your case it should be something like -

select 
ssrmeet.ssrmeet_room_code, 
TO_CHAR(
    TO_DATE( ssrmeet.ssrmeet_begin_time, 'HH24MI' )
      /*, char format*/
      ), 
TO_CHAR(
      TO_DATE( ssrmeet.ssrmeet_end_time, 'HH24MI' ) 
      /*, char format*/
      ),

CASE
when
/* line 53*/   ssrmeet.ssrmeet_sun_day IS NOT NULL   
       OR  ssrmeet.ssrmeet_mon_day IS NOT NULL   
       OR  ssrmeet.ssrmeet_tue_day IS NOT NULL   
       OR  ssrmeet.ssrmeet_wed_day IS NOT NULL   
       OR  ssrmeet.ssrmeet_thu_day IS NOT NULL   
       OR  ssrmeet.ssrmeet_fri_day IS NOT NULL   
       OR  ssrmeet.ssrmeet_sat_day IS NOT NULL   
then 
/*Here comes the value that you want to show if one or all of the weekdays is not null*/
'WEEKDAY NOT NULL'
END
from ssrmeet;

Please let me know if this works for you.

Thanks, Aditya

Thank you! That was actually super helpful. – Alex Ackroyd Apr 22 '13 at 23:05 — Alex Ackroyd, Apr 22 '13 at 23:05

score 0 · Answer 2 · answered Apr 22 '13 at 23:04

Thank you for your help! You're right, that would be the proper way to use the case statement, but it didn't end up giving me the results I was looking for.

/* Code written for exercize 3 in the Banner Tutorials by Alex Ackroyd on 18april2013*/
/* Edited last: 22april2013*/
/* Objective: use functions to transform and shape data in the result set*/
/* Tables: SSRMEET ( add to SPRIDEN, SFRSTCR, SSBSECT) */
/* Select: in addition to the columns from exercize 2, add the following values: 
    - SSRMEET_ROOM_CODE, 
    - a computed column for the start time, 
    - a computed column for the end time, and
    - a computed column that shows the days the class meets*/
/* Hint: The computed columns are created by passing table columns through functions. 
          You can use any non-analytical database function documented by Oracle to transform data from one thing to another.
          You can also write your own functions! Whoa there Shadowfax, coming soon to the tutorial near you!*/

/* Start code from exercizes 1, 2, unt 3 that queries my student record*/

select 
  spriden.spriden_id, spriden.spriden_pidm,
  sfrstcr.sfrstcr_term_code, sfrstcr.sfrstcr_crn,
  ssbsect.ssbsect_subj_code, ssbsect.ssbsect_crse_numb, ssbsect.ssbsect_seq_numb,
    ssbsect.ssbsect_crn,
  ssrmeet.ssrmeet_term_code, ssrmeet.ssrmeet_crn, ssrmeet.ssrmeet_room_code, 

  TO_CHAR( TO_DATE( ssrmeet.ssrmeet_begin_time, 'HH24MI' ), 'HH12:MI PM') as start_time, 
  TO_CHAR( TO_DATE( ssrmeet.ssrmeet_end_time, 'HH24MI' ), 'HH12:MI PM') as end_time,

  ( nvl(       decode( ssrmeet.ssrmeet_sun_day, null, '', 'Su')
            || decode( ssrmeet.ssrmeet_mon_day, null, '', 'M')
            || decode( ssrmeet.ssrmeet_tue_day, null, '', 'T')
            || decode( ssrmeet.ssrmeet_wed_day, null, '', 'W')
            || decode( ssrmeet.ssrmeet_thu_day, null, '', 'Th')
            || decode( ssrmeet.ssrmeet_fri_day, null, '', 'F')
            || decode( ssrmeet.ssrmeet_sat_day, null, '', 'Sa'),
            'TBA')
    ) as meetDays

from spriden, sfrstcr, ssbsect, ssrmeet

where
  spriden.spriden_id = /* insert id code here ->'XXXXXXXX' */
  and
  sfrstcr.sfrstcr_pidm = spriden.spriden_pidm
  and
  ssbsect.ssbsect_crn = sfrstcr.sfrstcr_crn
  and
  ssbsect.ssbsect_term_code = sfrstcr.sfrstcr_term_code
  and
  ssbsect.ssbsect_term_code = ssrmeet.ssrmeet_term_code
  and 
  ssbsect.ssbsect_crn = ssrmeet.ssrmeet_crn
  ;

/* End code from exercizes 1, 2, unt 3 */

SQL Developer: Invalid Relational Operator Error, also formatting questions

2 Answers2