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