Case statement

User_IL246

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)

Gianni Ceresa

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' .