CODEWITHSUNDEEP
sqloracle-database
Toddler
Toddler

Reputation: 95

ORA-00920: invalid relational operator while using case

I am writing a code using oracle developer to find if there is a login from a device for 3 consecutive days, I'm writing a code using case function but it is giving me invalid relational operator error. I don't know where I am wrong.

thanks in advance

SELECT *FROM
(SELECT a.*,substr(TEXT_USERNAME,2,length(TEXT_USERNAME)-2), row_number() 
over (partition by trunc(DTIME_LOGIN) order by TEXT_USERNAME ) rn

 FROM 
  OWNER_DWH.F_HOSEL_USER_LOGIN_TT a)

 WHERE

  (CASE WHEN trunc(dtime_login)=trunc(sysdate-1) then  'y'
  ELSE 'n' END ) D1
  and 
   (CASE WHEN trunc(dtime_login)=trunc(sysdate-2) then  'y'
  ELSE 'n' END) D2
  and 
   (CASE WHEN trunc(dtime_login)=trunc(sysdate-3) then  'y'
  ELSE 'n' END) D3

Upvotes: 0

Views: 6557

Answers (2)

unleashed
unleashed

Reputation: 771

When you are writing a query, you are asking a question of the data. The predicate, or where clause, is the specific question to limit the rows you want to return. This ...

(CASE WHEN trunc(dtime_login)=trunc(sysdate-1) then  'y'
      ELSE 'n' END ) D1

... is actually a valid predicate. Here, you are running a CASE statemtent that will return a value, but the value is never compared to a constant or other value. Instead, you seem to be trying to assign an alias, which is only for the select column.

Perhaps you were thinking about something like the following. Here, the CASE statement is evaluated and the resulting value is tested against the value 'y'.

(CASE WHEN trunc(dtime_login)=trunc(sysdate-1) then  'y'
      ELSE 'n' END ) = 'y'

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269613

This fixes your syntax error. But the code will return no rows because your logic is wrong:

WHERE 'y' = (CASE WHEN trunc(dtime_login) = trunc(sysdate-1) then 'y' ELSE 'n' END) AND
      'y' = (CASE WHEN trunc(dtime_login) = trunc(sysdate-2) then 'y' ELSE 'n' END) AND
      'y' = (CASE WHEN trunc(dtime_login) = trunc(sysdate-3) then 'y' ELSE 'n' END)

I would suggest that you ask another question with sample data and desired results and a clear explanation of what you want to accomplish.

Upvotes: 1

Related Questions