Sql Limit clause based in input Parameter

Question

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.

user_10506185 · Answer

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

Sql Limit clause based in input Parameter

1 Answers