Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Error: OBIEE Prompt showing an error with this Datetime Format

Received Response
21
Views
3
Comments
Hemant_Sharma
Hemant_Sharma Rank 2 - Community Beginner
edited Aug 13, 2024 5:07PM in Oracle Analytics Cloud and Server

SELECT  CASE 

 WHEN "dimension"."START_DATE" LIKE '%13-05-2022 17:00:00%' THEN  'Quiz on ABC'

  WHEN "dimension"."START_DATE" LIKE '%30-05-2022 08:00:00%' THEN  'Quiz on XYZ'

  WHEN "dimension"."START_DATE" LIKE '%01-07-2022 14:00:00%' THEN  'Quiz on QRT'

  ELSE  'Quiz Not Found'  END FROM  "quiz" FETCH FIRST 65001 ROWS ONLY

Hi All,

I am using this query in prompt's SQL Result to group data but it is showing this error, may be due to datetime format.

Plz Help.

Thanks in advance

Answers

  • Gianni Ceresa
    Gianni Ceresa Mod

    What data type is your "dimension"."START_DATE" column? Because the LIKE %...% syntax only works on strings, if it's a real date you can't just expect it will work with some magic conversion from date/time to text.

  • Hemant_Sharma
    Hemant_Sharma Rank 2 - Community Beginner

    It is Timestamp in RPD, but how will i compare this timestamp, means what is the format of timestamp for comparing in OBIEE in database start_date is datatype date '28-06-2022 17:00:00' and timestamp in RPD.

    pls provide format for comparing like :- "dimension"."START_DATE"='28-06-2022 17:00:00' or something else format

  • Gianni Ceresa
    Gianni Ceresa Mod

    It's actually a = that you can use for timestamps.

    The easier way to get what you are after is to build an analysis in that subject area, and set a filter on that "dimension"."START_DATE" : if you look at the LSQL of the analysis you will get the exact syntax for a = with your timestamp/date.