Forum Stats

  • 3,855,080 Users
  • 2,264,458 Discussions
  • 7,905,891 Comments

Discussions

Case statement

I need to create a dashboard prompt based on a condition using two time tables. In edit prompt using the following sql statement in 'SQL Result' :

SELECT CASE WHEN "Time_city"."Date" <="Time_city2"."Date" THEN 'Sold' ELSE 'Not sold' END FROM "Time_city" LEFT JOIN "Time_city2" ON "Time_city"."Ordernr" = "Time_city2"."Ordernr"

But it doesn't show 'Sold' or 'Not sold' in filter( getting an SQL error). Should I add something to the statement? Is it correct to use SQL statement here in 'Choise List Values' or should I use it in Column (fx)

Answers

  • Gianni Ceresa
    Gianni Ceresa Managing Director | Oracle ACE Director Member Posts: 6,690 Blue Diamond

    Because it is in a dashboard prompt, what behavior do you expect?

    Your prompt could have various visual appearances but in the end only 2 possible values: why to write a query that will perform an operation to only return a long list of only 2 possible values?

    Not even going into the logic of the query, what you are trying to do is a huge waste of resources for something that will probably not work as you expect because you have a different need than what you are trying to do.

    With only 2 possible values, your prompt should not be "SQL Result" sourced but defined using "Custom Values" where you enter 'Sold' and 'Not sold' .