March 8, 2012 at 2:53 am
I have a form that returns order details based on the input received in 4 parameters
using the following WHERE clause
WHERE HOrder.Acno = @Acno and
SUBSTRING(HDespatch.ArcOrdno,1,4) = @Unit and
SUBSTRING(HDespatch.ArcOrdno,5,8) >= @FromDate and
SUBSTRING(HDespatch.ArcOrdno,5,8) <= @ToDate
The current WHERE clause expects all these fields to contain data, but I want to expand the clause so that it will cater for the following.
If the Acno is blank then it will return all the orders for the unit within the date range.
If the unit is blank it will return all orders for the Acno for the date range entered.
If the date range is blank it will return all the Acno's orders for that unit
Hope this makes sense and any help would be much appreciated
March 8, 2012 at 3:22 am
Looks like you want a catch-all query.
See this great blog post by Gail Shaw: http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
-- Gianluca Sartori
March 8, 2012 at 3:24 am
You could also read this article by Erland Sommarskog: http://www.sommarskog.se/dyn-search-2005.html
-- Gianluca Sartori
March 8, 2012 at 3:27 am
Thanks, I'll have a look at both
March 9, 2012 at 4:24 am
ron.grace (3/8/2012)
I have a form that returns order details based on the input received in 4 parametersusing the following WHERE clause
WHERE HOrder.Acno = @Acno and
SUBSTRING(HDespatch.ArcOrdno,1,4) = @Unit and
SUBSTRING(HDespatch.ArcOrdno,5,8) >= @FromDate and
SUBSTRING(HDespatch.ArcOrdno,5,8) <= @ToDate
The current WHERE clause expects all these fields to contain data, but I want to expand the clause so that it will cater for the following.
If the Acno is blank then it will return all the orders for the unit within the date range.
If the unit is blank it will return all orders for the Acno for the date range entered.
If the date range is blank it will return all the Acno's orders for that unit
Hope this makes sense and any help would be much appreciated
I'm assuming you have control over the code...instead of trying to make some kind of catch all where clause what about building it dynamically based on the conditions. Something like:
Dim strWhere as string
If txtAcno.text = "" then
strWhere = build your conditions for that
End if
If txtUnit.text = "" then
strWhere = build your conditions for that
End if
etc.
March 9, 2012 at 4:45 am
I used a CASE clause and it seems to be working
WHEREHOrder.Acno = CASE LTRIM(RTRIM(@Acno))
WHEN '' THEN HOrder.Acno
ELSE @Acno
END
AND
SUBSTRING(HDespatch.ArcOrdno,1,4)=CASE LTRIM(RTRIM(@Unit))
WHEN '' THEN SUBSTRING(HDespatch.ArcOrdno,1,4)
ELSE @Unit
END
AND
SUBSTRING(HDespatch.ArcOrdno,5,8)>=CASE LTRIM(RTRIM(@Fdate))
WHEN '' THEN SUBSTRING(HDespatch.ArcOrdno,5,8)
ELSE @Fdate
END
AND
SUBSTRING(HDespatch.ArcOrdno,5,8)<=CASE LTRIM(RTRIM(@Tdate))
WHEN '' THEN SUBSTRING(HDespatch.ArcOrdno,5,8)
ELSE @Tdate
END
March 9, 2012 at 7:22 am
Sure it works, but it won't use indexes.
I would recommend using dynamic SQL in this case.
You can look it up in Erland's article.
-- Gianluca Sartori
March 9, 2012 at 4:27 pm
ron.grace (3/8/2012)
I have a form that returns order details based on the input received in 4 parametersusing the following WHERE clause
WHERE HOrder.Acno = @Acno and
SUBSTRING(HDespatch.ArcOrdno,1,4) = @Unit and
SUBSTRING(HDespatch.ArcOrdno,5,8) >= @FromDate and
SUBSTRING(HDespatch.ArcOrdno,5,8) <= @ToDate
The current WHERE clause expects all these fields to contain data, but I want to expand the clause so that it will cater for the following.
If the Acno is blank then it will return all the orders for the unit within the date range.
If the unit is blank it will return all orders for the Acno for the date range entered.
If the date range is blank it will return all the Acno's orders for that unit
Hope this makes sense and any help would be much appreciated
Well, to start with you are obviously dealing with a denormalized table design. There is no hope of getting an indexed search on Order Date if it's part of the column ArcOrdno. You could an indexed search on Unit by doing this:
WHERE ArcOrdno like @Unit+'%';
You could try somthing like this:
WHERE Acno = ISNULLl( @Acno, Acno )
AND ArcOrdno LIKE ISNULL( @Unit, ArcOrdno )+'%'
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
March 10, 2012 at 1:19 am
Gianluca Sartori (3/9/2012)
Sure it works, but it won't use indexes.I would recommend using dynamic SQL in this case.
You can look it up in Erland's article.
It may use indexes to scan them.
Even if you do dynamic in this case you cannot use indexes properly because of the SUBSTRING function on the columns. The only way to fix it without changing the design too much is to create calculated persisted columns and then indexes on them.
March 12, 2012 at 1:11 am
I'll create new indexes in the source file
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply