An expression of non-boolean type specified in a context where a condition is expected, near 'or

Queries and Resources
,
1

I'm getting this error when adding a search parameter to my table's select query.

select * from products
where {{ !selSearchProductType.value }} or product_type_id = {{selSearchProductType.value}}

I am using Azure SQL Server. When testing Retool this logic worked just fine against Postgres. Googling has been a bust. I am very rusty in SQL Server being a longtime Postgres guy, but SQL Server is spec'd for this project.

Am I doing something stupid or is this another example of Postgres being superior to SQL Sever? Is there a preferred syntax for SQL Sever or should I just manually build the query in JS?

2

This actually does seem to be an issue with MS SQL syntax:

1804×790 61.1 KB
1810×618 57.9 KB
A workaround something like this seems to work:

select * from products
where ( {{ selSearchProductType.value || null }} IS NULL OR product_type_id = {{selSearchProductType.value}} )

2 Likes
3

Thanks! Based on your start I was able to finish my full Azure SQL Server query which I am including here to give a template for others. This includes searching foreign key values, doing a wild card search against multiple fields, clauses based on checkbox state and date search.

select p.*, t.town_name, pt.product_type 
from products p
join towns t on p.town_id=t.town_id
join product_types pt on p.product_type_id=pt.product_type_id
where  ({{selSearchProductType.value || null}} IS NULL OR p.product_type_id={{selSearchProductType.value}})
AND ({{selSearchTown.value || null}} IS NULL OR p.town_id={{selSearchTown.value}})
AND ({{txtSearchProduct.value || null}} IS NULL OR (p.product_name like {{'%' + txtSearchProduct.value + '%'}} or p.product_description like {{'%' + txtSearchProduct.value + '%'}}) )
AND (1= {{chkLowStock.value === true ? 0: 1}} or (reorder_trigger > inventory_count) and reorder_trigger > 0)
AND (1= {{chkSearchUncounted.value === true ? 0: 1}} or (inventory_count = -1))
AND ({{dtSearchOlderThan.formattedString || null}} IS NULL OR p.last_inventory_check <{{dtSearchOlderThan.formattedString}})
3 Likes