December 19, 2013 at 6:25 pm
Hello,
i am having trouble in getting my desired result of my query. here is my sample source table
source table:
dispatch date -CustomerName -dispatch by -invoiceNo
2013-12-18 Philip Morris Philippines Manufacturing Inc test 100-00157671
2013-12-18 Philip Morris Philippines Manufacturing Inc test 100-00157672
2013-12-19 Philip Morris Philippines Manufacturing Inc test 100-00157673
2013-12-19 Philip Morris Philippines Manufacturing Inc test 100-00157674
2013-12-19 Philip Morris Philippines Manufacturing Inc test 100-00157675
2013-12-19 Insurance Company Of North America test 100-00157676
2013-12-19 Maersk Global Services Centres (Phils) Ltd. - test 100-00157677
2013-12-20 Maersk Global Services Centres (Phils) Ltd. - test 100-00157678
2013-12-20 Maersk Global Services Centres (Phils) Ltd. - test 100-00157679
2013-12-20 Maersk Global Services Centres (Phils) Ltd. -test 100-001576710
2013-12-20 Maersk Global Services Centres (Phils) Ltd. -test 100-001576711
-declare @CustomerName nvarchar(150),@InvoiceNo nvarchar(150), @InvoiceFrom datetime,@InvoiceTo datetime
Query:
select * from #TempLoadDispatch
where GroupName like case isnull((@CustomerName),'') when '' then @CustomerName + '%' else '%' + @CustomerName + '%' end
or Invoiceno like case isnull((@InvoiceNo),'') when '' then @InvoiceNo + '%' else '%' + @InvoiceNo + '%' end
or InvoiceDate >= @InvoiceFrom and InvoiceDate <= @InvoiceTo
order by Invoiceno desc
Should be output:
-i want to pull all the CustomerName "Philip Morris Philippines Manufacturing Inc" with
dispatch date of "2013-12-19 "
and this should be dynamic the query should be flexible if the on @Customername is supplied
and the @InvoiceNo is null or no value set and @InvoiceFrom and @InvoiceTo is null also.
please help.
thanks and more power.
December 20, 2013 at 8:06 am
It would be much better if you posted your table as a create table followed by insert statements.
I took the liberty of formatting your query so we can read it.
SELECT *
FROM #TempLoadDispatch
WHERE GroupName LIKE CASE isnull((@CustomerName), '')
WHEN ''
THEN @CustomerName + '%'
ELSE '%' + @CustomerName + '%'
END
OR Invoiceno LIKE CASE isnull((@InvoiceNo), '')
WHEN ''
THEN @InvoiceNo + '%'
ELSE '%' + @InvoiceNo + '%'
END
OR InvoiceDate >= @InvoiceFrom
AND InvoiceDate <= @InvoiceTo
ORDER BY Invoiceno DESC
There are a couple of pretty big logic errors in here.
In your case expression you test @CustomerName for null. However in the When predicate you exclude the ISNULL so if @CustomerName is actually NULL you would end up checking for NULL which will never return true.
Something like this:
CASE isnull((@CustomerName), '')
WHEN ''
THEN isnull(@CustomerName, '') + '%' --Without the ISNULL this would be NULL because adding anything to NULL results in NULL
ELSE '%' + @CustomerName + '%'
END
You have the same issue on the second case expression.
Last but not least, I think you may be missing some parenthesis in you where clause. I suspect you probably want any of the first conditions AND the last condition. They way you have it with parenthesis is it will return for the first condition or the second condition or InvoiceDate >= @InvoiceFrom AND InvoiceDate <= @InvoiceTo
If I were going to work on this I would rethink the where conditions completely. What are the actual business rules you are trying to accomplish here? I think the where clause can be greatly simplified but I am not 100% sure what you want as output.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply