CODEWITHSUNDEEP
sqloracle-database
avedominusnox
avedominusnox

Reputation: 21

Error ORA-00920: Invalid relational operator

I assume the error is coming from the case statement but I can't figure out what's giving it such a problem.

Here is my query:

SELECT aim.student_id,
       coordinator,
       ac_class,
       email_contact,
       doc_date,
       doc_exp_date,
       notes,
       ad.name as "Disability",
       aa.name as "Accommodation",
       PERSON_ID,
       tse.YEAR_TERM,
       ATT_CREDITS,
       PRIMARY_PROGRAM_DESCRIPTION,
       ACADEMIC_YEAR,
       MARITAL_STATUS,
       ENROLLMENT_STATUS_DESCRIPTION,
       CALENDAR_YEAR,
       YEAR_TERM_START_DATE,
       YEAR_TERM_END_DATE,
       CASE
         WHEN (doc_date <= year_term_end_date AND
              doc_exp_date >= year_term_end_date) THEN
          1
         WHEN (isnull(doc_exp_date)) THEN
          1
         ELSE
          0
       END AS "Valid Document"
  FROM striker.saasta_prod.accessibility_aim aim
  JOIN striker.saasta_prod.accessibility_std_disabilities asd
    ON aim.student_id = asd.student_id
  JOIN striker.saasta_prod.accessibility_disabilities ad
    ON asd.disability_id = ad.id
  LEFT JOIN striker.saasta_prod.accessibility_std_accommodations asa
    ON aim.student_id = asa.student_id
  LEFT JOIN striker.saasta_prod.accessibility_accommodations aa
    ON asa.accommodation_id = aa.id
  JOIN dwprd.ods.std_term_status_ext tse
    ON aim.student_id = tse.person_id
  JOIN dwprd.edw.year_term_dim ytd
    ON tse.year_term = ytd.year_term

Upvotes: 1

Views: 1415

Answers (3)

GMB
GMB

Reputation: 222702

As noted by Brian Leach, there is an isse with the way you are referring the tables : in Oracle, you can use my_schema.my_table, but using three dot-separated parts is not supported.

There is also an issue in the CASE expression:

WHEN (isnull(doc_exp_date)) THEN 1

There is no ISNULL() function in Oracle.

In SQL Server, ISNULL() is used to check if first argument is NULLand, in that event, replace it with the second argument. The Oracle equivalent is NVL().

But since you are using this expression as a condition, I suspect that you actually meant:

WHEN doc_exp_date IS NULL THEN 1

Upvotes: 0

Barbaros &#214;zhan
Barbaros &#214;zhan

Reputation: 65408

Because, you need to replace isnull() function with nullif() or nvl(), and the second argument needed for them. Since Oracle DB has no isnull() function while another DB such as SQL Server has.

All their usage logics are similar.

Upvotes: 0

Brian Leach
Brian Leach

Reputation: 2101

Your join table names have three arguments, only two are allowed:

INNER JOIN striker.saasta_prod.accessibility_std_disabilities asd

Did you copy this from SQL Server, where you are allowed to prefix the database name? This is not allowed in Oracle.

Upvotes: 0

Related Questions