Topic: SQL syntax help

Please help with last WHERE clause for custom search using SQL. When date control is not selected (checked), that field should be ignored but if selected the date field must be included.

SELECT
Dispatches.releaseno
,strftime('%d/%m/%Y', Dispatches.loaddate)
,Dispatches.truckreg
,Clients.company
,Products.prodname
,Contracts.contractno
,Dispatches.id

FROM
Dispatches

inner join Contracts
on Dispatches.id_Contracts = Contracts.id

inner join Clients
on Contracts.id_Clients = Clients.id

inner join Products
on Contracts.id_Products = Products.id

WHERE
(case when '{edReleaseNo}'='' then 1=1
else Dispatches.releaseno = "{edReleaseNo}" end)
and
(case when '{edTruckReg}'='' then 1=1
else Dispatches.truckreg = "{edTruckReg}" end)
and
(case when '{edContractNo}'='' then 1=1
else Contracts.contractno = "{edContractNo}" end)
and
(case when {cboProduct}=-1 then 1=1
else Contracts.id_Products = {cboProduct} end)
and
(case when {cboClient}=-1 then 1=1
else Contracts.id_Clients = {cboClient} end)
and
(case when {dtLoadDate} // IS NOT SELECTED // then 1=1
else Dispatches.loaddate = {dtLoadDate} end)                                                                                   
;

Thanks

Re: SQL syntax help

Hello,

(case when {dtLoadDate} // IS NOT SELECTED // then 1=1
else Dispatches.loaddate = {dtLoadDate} end)

Please, replace:

(case when ifnull({dtLoadDate},1)=1 then 1=1 else Dispatches.loaddate = {dtLoadDate} end)
Dmitry.

Re: SQL syntax help

Perfect. Thank you very much