SQL开发人员:无效的关系操作符错误，也是格式化问题

Question

我正试图将一些格式化的列添加到对我们的数据库的查询中，但我认为我在CASE语句中得到了错误的操作符。

我也不清楚如何为TO_CHAR操作编写格式化。它与TO_DATE操作相同吗?(在oracle文档中找不到)

我也不确定是否应该使用TO_DATE或其他调用来创建一个对象，该对象存储为四个数字的字符串的时间。

最重要的是，我的代码正在产生错误，所以我看不到任何东西。

我得到这个错误:

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

从这个代码

/* 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*/

提前感谢您的帮助。

Answer 1

我认为SELECT子句中的CASE结构也应该有THEN块。就像你想做的，如果一个或所有的工作日不是空的。请参考下面的例子-

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

在你的例子中应该是-

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;

请让我知道这是否适合你。

谢谢,Aditya

Answer 2

感谢您的帮助!你说得对，这是使用case语句的正确方法，但它没有得到我想要的结果。

/* 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 */