How to write this condition correctly to where clause?

I need to make the following condition compatible with retool query:

WHERE
 ({{ !notes.value }} OR {{ notes.value == 'with_notes' ? orders.shopify_note != '': orders.shopify_note == ''}}) 

If the dropdown has a value selected and its with_notes result query should look like this:

     WHERE
        orders.shopify_note == '' 

If the dropdown selected value is without_notes the query should look like this:

 WHERE
    orders.shopify_note != ''

@ckissi I’m not sure what you mean / how we can help. What are you looking for exactly?

The condition above doesn’t work in the SQL. How to write it to make it compatible with Retool parser? It’s a regular JS syntax but in retool it doesn’t work.

notes is a dropdown element.

image

What error are you getting? Can you send over a screenshot?

I’ve tried different approach but doesn’t work too

Could you try removing the full quotes? So orders.shopify_note <> ''

This actually the initial state from the beginning of this thread.

Just replace <> with !=

1 Like

@nacho thanks but if you check my first code in this thread you’ll notice it uses what you recommend. Unfortunately, does not work.

The error you’re getting here is a SQL error. It’s saying that shopify_note is a boolean, therefore you can’t compare it against a string.

I think you mean this (or the reverse):

notes.value == 'with_notes' ? "orders.shopify_note": "NOT orders.shopify_note" 

In any case, I would try it in plain SQL first and once that works, try to interpolate parts of it in JS.

Unfortunately no, the orders.shopify_note is text and the error is related to SQL binding thats used in Retool.

image

In plain SQL it works OK.

Hi @ckissi,

I think the issue here is we’re getting SQL and JS syntax mixed together. <> is SQL syntax for not equal to, but not valid inside of the {{ }} tags which are all javascript.

What you’re looking for might be:

AND ( {{ !notes.value }} OR )

Passing the results of a {{ }} tag as a string to be read as SQL syntax is only possible if you disable prepared statements in the resource setup, since we want to prevent someone writing DROP table inside of a text input. If you need to pass in a string to be read as SQL, you can disable prepared statements in the resource setup!

@alex-w
So if I understand right in SQL I can use only JS syntax that returns boolean value right?

Hmm, I’m confused now because this part works ok and it uses a string as a result for ANY statement:

(true = {{ fulfillment_status_multi.value.length == 0 }} OR orders.fulfillment_status = ANY ({{ fulfillment_status_multi.value }}))