AnsweredAssumed Answered

Less Than (<) relational operator in Chart Query

Question asked by William May on Jul 31, 2018
Latest reply on Aug 1, 2018 by William May

I'm getting the following error while trying to use a '<' operator in a multi-series chart report definition in the GUI. I'm also able to replicate the error by putting it in a tabular report definition as well. It works fine in SQL Developer:

 

java.sql.SQLSyntaxErrorException: ORA-00920: invalid relational operator

 

Was able to narrow it down to the less than operator by simplifying the query and trying various permutations until I figured out that it just doesn't like '<'

 

Curious if someone could pop the SQL into their lab and see if it runs. I'm on 7.0.2 P07

(unrelated - why aren't these types of reports out of box?? I hate having to develop these basic things...)

SELECT bu.NAME,
  sum(
  CASE
    WHEN acc.orphaned_date>SYSDATE-20
    THEN 1
    ELSE 0
  END) AS "Attention Needed",
  sum(
  CASE
    WHEN acc.orphaned_date<SYSDATE -19
    AND acc.orphaned_date > SYSDATE-30
    THEN 1
    ELSE 0
  END) AS "At Risk",
  sum(
  CASE
    WHEN acc.orphaned_date<SYSDATE-30
    THEN 1
    ELSE 0
  END) AS "Breach"
FROM avuser.pv_business_unit bu
LEFT OUTER JOIN avuser.pv_application app
ON app.business_unit_id =bu.ID
LEFT OUTER JOIN avuser.pv_account acc
ON app.ID                =acc.application_id
WHERE acc.orphaned_date IS NOT NULL
GROUP BY bu.NAME

Outcomes