[SOLVED] Sql Limit clause based in input Parameter

Sql Limit clause based in input Parameter

I have been trying to find a solution for a limit-clause based on an input parameter from a Json-File. The current code looks somewhat like this

With myJsonTable (JsonText)
as (
Select JsonText)

Select * from Data
Where...

Limit 
  Case
   WHEN (Select JSON_VALUE(JsonText, '$."Amount"') From myJsonTable is not null
     THEN (Select JSON_VALUE(JsonText, '$."Amount"') From myJsonTable)
     ELSE (10000000)
  END

Which I cant seem to get work. The Output I am getting is

Non-negative integeter value expected in LIMIT clause

Is there a way to cast the select done? Trying different Selects anywhere in the Case clause caused the same error.

Solution

Exasol only allows constant expression in the limit clause, so it's not directly possible to specify a select statement that references myJsonTable there.

However, you can workaround this issue by using a approach similar to SQL query for top 5 results without the use of LIMIT/ROWNUM/TOP