Conditions in a SQL query

[Nicolashp]

I'm getting trouble trying to create a SQL query passing conditions based on the value of Retool components.

select sum(total_price), date(created_at) from shopify-analysis.shopify_integration.orders WHERE customer.orders_count {{(checkbox3.value==1 && checkbox4.value==0) ? "=1":">1"}} AND source_name="web" and created_at>=timestamp({{ datetimepicker1.formattedString}}) and created_at<timestamp({{ datetimepicker2.formattedString}}) group by date(created_at)

I'm getting the error : * message:"Syntax error: Expected end of input but got "?" at [3:30]"

has someone got an idea from where comes the problem ?

[ben]

Hi @Nicolashp! Thanks for writing in & welcome to the forum!

In your resource setup, do you have Disable converting queries to prepared statements selected? This will allow you to use JS in your query.

[Nicolashp]

hi @ben so many thanks for your answser !

My problem was solved after changing the ressource setup but then I got a problem with the datepicker value, I get the error : “No matching signature for function TIMESTAMP for argument types: INT64. Supported signatures: TIMESTAMP(STRING, [STRING]); TIMESTAMP(DATE, [STRING]); TIMESTAMP(DATETIME, [STRING]) at [2:70]”

I’ve tried to apply a .toString function :

But when I run the query there are no rows displaying, does anyone have a fix for it ?

Thanks !

[ben]

@Nicolashp

Looks like datetimepicker1.formattedString.toString returns a function, which is causing issues in the timestamp function. datetimepicker1.formattedString should already be a string, so calling toString() here shouldn’t be necessary!