I am trying to show all data when filter is not in use

[William]

• Goal: I am trying to show all data when filter is not in use.

• Steps: I am following the format in this link. https://community.retool.com/t/sql-cheatsheet-best-practices-for-different-flavors-of-sql-syntax/23902. I want to use this in my HAVING clause instead of the WHERE clause.

• Details: I tried the following codes.
  select * from users group by id having 1=1 and ({{ !textInput1.value }} OR id = {{ textInput1.value }}) order by id

select * from users group by id having 1=1 and (true OR id = {{ textInput1.value }}) order by id

I am getting this error for both code.
invalid input syntax for type integer: ""

I am not sure if this is a bug or if it is something wrong on my end.

[pyrrho]

Hello @William, welcome to the forums!

I believe your textInput.value is a string but your id field is an integer. You should either parse the value in between the {{ }} or CONVERT the result in the select query and see if that resolves the error.

[William]

Hi @pyrrho , thanks!

select * from users 
group by id 
having 1=1 and ({{ !textInput1.value }} or id = {{ Number(textInput1.value) }})
order by id

I converted it and it works!
Not sure why though. I set the ID column to string so I thought it should be able to read textInput1.value as a string.