Why is this query having syntax issue?

Queries and Resources
, , ,
1

  • Goal: I really don't know how this query has syntax issue

  • Steps: I tried this, everything works:

  • SELECT
    *
    WHERE
    (
    ({{ vessel_checkBox.value }} AND t_load_info.status = 63) OR
    ({{ hold_checkBox.value }} AND t_load_info.status = 62) OR
    ({{ exam_checkBox.value }} AND t_load_info.status = 60) OR
    ({{ examAvailable_checkBox.value }} AND t_load_info.status = 75) OR
    ({{ scheduled_checkBox.value }} AND t_load_info.status = 71) OR
    ({{ l_oldYard_checkBox.value }} AND t_load_info.status = 50) OR
    ({{ l_newYard_checkBox.value }} AND t_load_info.status = 51) OR
    ({{ l_INDYard_checkBox.value }} AND t_load_info.status = 52) OR
    ({{ l_lbYard_checkBox.value }} AND t_load_info.status = 53) OR
    ({{ l_pomonaYard_checkBox.value }} AND t_load_info.status = 83) OR
    ({{ l_wh_checkBox.value }} AND t_load_info.status = 59) OR
    ({{ m_shceduled_checkBox.value }} AND t_load_info.status = 82) OR
    ({{ l_process_checkBox.value }} AND t_load_info.status = 72) OR
    ({{ delivery_process_checkBox.value }} AND t_load_info.status = 74) OR
    ({{ unloading_checkBox.value }} AND t_load_info.status = 79) OR
    ({{ e_wh_checkBox.value }} AND t_load_info.status = 37) OR
    ({{ e_rtn_proces_checkBox.value }} AND t_load_info.status = 78) OR
    ({{ e_oldYard_checkBox.value }} AND t_load_info.status = 55) OR
    ({{ e_newYard_checkBox.value }} AND t_load_info.status = 54) OR
    ({{ e_indYard_checkBox.value }} AND t_load_info.status = 56) OR
    ({{ e_lbYard_checkBox.value }} AND t_load_info.status = 57) OR
    ({{ e_pomonaYard_checkBox.value }} AND t_load_info.status = 84) OR
    ({{ e_rtn_checkBox.value }} AND t_load_info.status = 38) OR
    ({{ Invoice_checkBox.value }} AND t_load_info.status = 80) OR
    ({{ chassis_paid_checkBox.value }} AND t_load_info.status = 77) OR
    ({{ finished_checkBox.value }} AND t_load_info.status = 35) OR
    (NOT {{ vessel_checkBox.value }} AND NOT {{ hold_checkBox.value }} AND NOT {{ exam_checkBox.value }} AND NOT {{ examAvailable_checkBox.value }} AND NOT {{ scheduled_checkBox.value }} AND NOT {{ l_oldYard_checkBox.value }} AND NOT {{ l_newYard_checkBox.value }} AND NOT {{ l_INDYard_checkBox.value }} AND NOT {{ l_lbYard_checkBox.value }} AND NOT {{ l_pomonaYard_checkBox.value }} AND NOT {{ l_wh_checkBox.value }} AND NOT {{ m_shceduled_checkBox.value }} AND NOT {{ l_process_checkBox.value }} AND NOT {{ delivery_process_checkBox.value }} AND NOT {{ unloading_checkBox.value }} AND NOT {{ e_wh_checkBox.value }} AND NOT {{ e_rtn_proces_checkBox.value }} AND NOT {{ e_oldYard_checkBox.value }} AND NOT {{ e_newYard_checkBox.value }} AND NOT {{ e_indYard_checkBox.value }} AND NOT {{ e_lbYard_checkBox.value }} AND NOT {{ e_pomonaYard_checkBox.value }} AND NOT {{ Invoice_checkBox.value }} AND NOT {{ chassis_paid_checkBox.value }} AND NOT {{ finished_checkBox.value }})
    )

AND ((true) OR ({{ customer_filter.value.length }} = 1 AND t_load_info.warehouse_id = 56) OR
({{ customer_filter.value.length}} > 1 AND t_load_info.warehouse_id IN (56,63)))

  • Details: when I use this query, it says i have syntax issue:
  • SELECT
    *
    WHERE
    (
    ({{ vessel_checkBox.value }} AND t_load_info.status = 63) OR
    ({{ hold_checkBox.value }} AND t_load_info.status = 62) OR
    ({{ exam_checkBox.value }} AND t_load_info.status = 60) OR
    ({{ examAvailable_checkBox.value }} AND t_load_info.status = 75) OR
    ({{ scheduled_checkBox.value }} AND t_load_info.status = 71) OR
    ({{ l_oldYard_checkBox.value }} AND t_load_info.status = 50) OR
    ({{ l_newYard_checkBox.value }} AND t_load_info.status = 51) OR
    ({{ l_INDYard_checkBox.value }} AND t_load_info.status = 52) OR
    ({{ l_lbYard_checkBox.value }} AND t_load_info.status = 53) OR
    ({{ l_pomonaYard_checkBox.value }} AND t_load_info.status = 83) OR
    ({{ l_wh_checkBox.value }} AND t_load_info.status = 59) OR
    ({{ m_shceduled_checkBox.value }} AND t_load_info.status = 82) OR
    ({{ l_process_checkBox.value }} AND t_load_info.status = 72) OR
    ({{ delivery_process_checkBox.value }} AND t_load_info.status = 74) OR
    ({{ unloading_checkBox.value }} AND t_load_info.status = 79) OR
    ({{ e_wh_checkBox.value }} AND t_load_info.status = 37) OR
    ({{ e_rtn_proces_checkBox.value }} AND t_load_info.status = 78) OR
    ({{ e_oldYard_checkBox.value }} AND t_load_info.status = 55) OR
    ({{ e_newYard_checkBox.value }} AND t_load_info.status = 54) OR
    ({{ e_indYard_checkBox.value }} AND t_load_info.status = 56) OR
    ({{ e_lbYard_checkBox.value }} AND t_load_info.status = 57) OR
    ({{ e_pomonaYard_checkBox.value }} AND t_load_info.status = 84) OR
    ({{ e_rtn_checkBox.value }} AND t_load_info.status = 38) OR
    ({{ Invoice_checkBox.value }} AND t_load_info.status = 80) OR
    ({{ chassis_paid_checkBox.value }} AND t_load_info.status = 77) OR
    ({{ finished_checkBox.value }} AND t_load_info.status = 35) OR
    (NOT {{ vessel_checkBox.value }} AND NOT {{ hold_checkBox.value }} AND NOT {{ exam_checkBox.value }} AND NOT {{ examAvailable_checkBox.value }} AND NOT {{ scheduled_checkBox.value }} AND NOT {{ l_oldYard_checkBox.value }} AND NOT {{ l_newYard_checkBox.value }} AND NOT {{ l_INDYard_checkBox.value }} AND NOT {{ l_lbYard_checkBox.value }} AND NOT {{ l_pomonaYard_checkBox.value }} AND NOT {{ l_wh_checkBox.value }} AND NOT {{ m_shceduled_checkBox.value }} AND NOT {{ l_process_checkBox.value }} AND NOT {{ delivery_process_checkBox.value }} AND NOT {{ unloading_checkBox.value }} AND NOT {{ e_wh_checkBox.value }} AND NOT {{ e_rtn_proces_checkBox.value }} AND NOT {{ e_oldYard_checkBox.value }} AND NOT {{ e_newYard_checkBox.value }} AND NOT {{ e_indYard_checkBox.value }} AND NOT {{ e_lbYard_checkBox.value }} AND NOT {{ e_pomonaYard_checkBox.value }} AND NOT {{ Invoice_checkBox.value }} AND NOT {{ chassis_paid_checkBox.value }} AND NOT {{ finished_checkBox.value }})
    )

AND ((true) OR ({{ customer_filter.value.length }} = 1 AND t_load_info.warehouse_id = 56) OR
({{ customer_filter.value.length}} > 1 AND t_load_info.warehouse_id IN ({{ customer_filter.value.map(value => value.toString())}})))

2

Hi @JasonYu

Happy to help! A couple of follow up questions:

What resource type is this (Postgres, Retool Database, Mysql, etc)?
For the last line (IN), we have a cheatsheet that shows the commonly used syntax options SQL Cheatsheet - best practices for different flavors of SQL syntax

For example, when running the second query in Retool Database, I have to change IN to = ANY and then it works :slightly_smiling_face:

As noted in the cheatsheet, this syntax quirk has to do with the prepared statements handling of dynamic values

3

It's a mySQL DB.
AND (
(t_load_info.warehouse_id IN ( {{ left_customer_filter.values }}) AND {{ checkbox1.value }}) OR (t_load_info.warehouse_id IN ( {{ left_customer_filter.value }})
)
this one above is not working either. (btw I was trying to find a workaround, I let checkbox1 to be checked if left_customer_filter.value.length > 1)
The behavior i want is: i have a checkbox group, it lists many customers, and I want to select one or more customer and the query result should reflect t_load_info.warehouse_id IN ( {{ left_customer_filter.value }})

If I don't select any option, the query should just display using the existing logic.

4

How about something like this?

5

6

It worked! Thank you

7

Glad to hear!