Showing posts with label criteria. Show all posts
Showing posts with label criteria. Show all posts

Tuesday, March 20, 2012

Conditional Where Clause

Hi - I am writing a C# program using SQL Server. The form I have is
collecting search criteria for a database. The main 3 fields are Category,
Type, Author.
Any combination of the 3 fields can be used. That is, All 3 fields can be
used to search on, or just 2 or just 1. If the user selects 1 or 2 fields,
I
can't use the 3rd field in the where clause of the query.
How can I create a generic query and pass a string for the "where" clause
instead of creating 7 specific queries for each possible combination of
search criteria.
Thanks,
SarahCREATE PROCEDURE getbook
@.category VARCHAR(10) = NULL,
@.bookType VARCHAR(10) = NULL,
@.author VARCHAR(10) = NULL
AS
SELECT category,
booktype,
author
FROM book
WHERE (category = @.category OR @.category IS NULL )
AND (booktype = @.bookType OR @.bookType IS NULL )
AND (author = @.author OR @.author IS NULL )|||Sarah,
From what you say is the following correct:
You want a Stored Procedure that takes 3 parameters and returns a recordset
based on the passed parameters.
The SELECT statement itself will be static, and both the first two params
will be used if present, and the third [arameter will only be used if neithe
r
of the first two params are present.
Does that sum it up?
Tony
"Sarah Sarah" wrote:

> Hi - I am writing a C# program using SQL Server. The form I have is
> collecting search criteria for a database. The main 3 fields are Category
,
> Type, Author.
> Any combination of the 3 fields can be used. That is, All 3 fields can b
e
> used to search on, or just 2 or just 1. If the user selects 1 or 2 fields
, I
> can't use the 3rd field in the where clause of the query.
> How can I create a generic query and pass a string for the "where" clause
> instead of creating 7 specific queries for each possible combination of
> search criteria.
> Thanks,
> Sarah|||Sarah,
Bearing in mind the mutual exclusivity between params 1,2 and param 3, the
following code will work:::
CREATE STORED PROCEDURE [dbo].[usp_GetSearchResults]
@.Category varchar(100)='',
@.Type varchar(100)='',
@.Author varchar(100)
AS
DECLARE @.sSQL varchar(2000)
SET @.sSQL = ''
IF @.Category ='' AND @.Type =''
BEGIN
SET @.sSQL = @.sSQL + ' SELECT Category, Type, Author '
SET @.sSQL = @.sSQL + ' FROM tblMYTABLE '
SET @.sSQL = @.sSQL + ' WHERE (@.Category='' OR Category=' + CHAR(39) +
@.Category + CHAR(39) + ') '
SET @.sSQL = @.sSQL + ' AND (@.Author ='' OR Author=' + CHAR(39) + @.Author +
CHAR(39) + ') '
END
ELSE
BEGIN
SET @.sSQL = @.sSQL + ' SELECT Category, Type, Author '
SET @.sSQL = @.sSQL + ' FROM tblMYTABLE '
SET @.sSQL = @.sSQL + ' WHERE (@.Category='' OR Category=' + CHAR(39) +
@.Category + CHAR(39) + ') '
SET @.sSQL = @.sSQL + ' AND (@.Type ='' OR Type=' + CHAR(39) + @.Type +
CHAR(39) + ') '
END
EXEC (@.sSQL)
You do not necessarily need the character string to create the select
statement, it does help with debugging though.
Hope it helps,
Tony
"Sarah Sarah" wrote:

> Hi - I am writing a C# program using SQL Server. The form I have is
> collecting search criteria for a database. The main 3 fields are Category
,
> Type, Author.
> Any combination of the 3 fields can be used. That is, All 3 fields can b
e
> used to search on, or just 2 or just 1. If the user selects 1 or 2 fields
, I
> can't use the 3rd field in the where clause of the query.
> How can I create a generic query and pass a string for the "where" clause
> instead of creating 7 specific queries for each possible combination of
> search criteria.
> Thanks,
> Sarah|||KenJ - thanks - this logic will work, but I am getting a syntax error:
"Duplicated parameter names are not allowed"
when I try to do this in Query builder. Any idea what would cause this erro
r.
Thanks,
Sarah
"KenJ" wrote:

> CREATE PROCEDURE getbook
> @.category VARCHAR(10) = NULL,
> @.bookType VARCHAR(10) = NULL,
> @.author VARCHAR(10) = NULL
> AS
> SELECT category,
> booktype,
> author
> FROM book
> WHERE (category = @.category OR @.category IS NULL )
> AND (booktype = @.bookType OR @.bookType IS NULL )
> AND (author = @.author OR @.author IS NULL )
>|||I'm not familiar with query builder. Can you run it in query analyzer?
Here is a sample script that creates a table, loads some dummy data,
runs the procedure with several variations then drops the table and
procedure. I've run it in query analyzer to be sure it works...
USE tempdb
GO
SET nocount ON
GO
CREATE TABLE book (
bookid INT IDENTITY( 1 , 1 ) NOT NULL PRIMARY KEY
, category VARCHAR(10) NULL
, booktype VARCHAR(10) NULL
, author VARCHAR(10) NULL)
GO
INSERT book
VALUES('fiction'
, 'paperback'
, 'twain')
INSERT book
VALUES('fiction'
, 'hardbound'
, 'asimov')
INSERT book
VALUES('fiction'
, 'paperback'
, 'rand')
GO
CREATE PROCEDURE getbook
@.category VARCHAR(10) = NULL
, @.bookType VARCHAR(10) = NULL
, @.author VARCHAR(10) = NULL
AS
SELECT category
, booktype
, author
FROM book
WHERE (category = @.category
OR @.category IS NULL )
AND (booktype = @.bookType
OR @.bookType IS NULL )
AND (author = @.author
OR @.author IS NULL )
GO
-- get all fiction books
EXEC getbook @.category = 'fiction'
-- all fiction books by rand
EXEC getbook @.category = 'fiction' ,
@.author = 'rand'
-- all paperbacks
EXEC getbook @.bookType = 'paperback'
-- returns all books since we don't supply any filter
EXEC getbook
GO
DROP TABLE book
GO
DROP PROCEDURE getbook
GO|||On Wed, 1 Feb 2006 16:50:27 -0800, Sarah Sarah wrote:

>Hi - I am writing a C# program using SQL Server. The form I have is
>collecting search criteria for a database. The main 3 fields are Category,
>Type, Author.
>Any combination of the 3 fields can be used. That is, All 3 fields can be
>used to search on, or just 2 or just 1. If the user selects 1 or 2 fields,
I
>can't use the 3rd field in the where clause of the query.
>How can I create a generic query and pass a string for the "where" clause
>instead of creating 7 specific queries for each possible combination of
>search criteria.
Hi Sarah,
Many ways to skin this cat can be found i Erland Sommarskog's article:
http://www.sommarskog.se/dyn-search.html
Hugo Kornelis, SQL Server MVP|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
It would also help if you would learn that a field and a column nothing
whatsoever alike and that names like "type", "category", etc. are too
vague to be data element names/. Try something like this:
CREATE PROCEDURE GetBook
(@.my_book_category VARCHAR(10) = NULL, -- wild guess
@.my_book_type INTEGER = NULL, -- Dewey Decimal ?
@.my_author_name VARCHAR(25) = NULL)
AS
SELECT book_category, book_type, author_name
FROM Library
WHERE book_category = COALESCE (@.my_book_category, book_category)
AND book_type = COALESCE (@.my_book_type, book_type)
AND author_name = COALESCE (@.my_author_name, author_)name) ;|||> WHERE book_category = COALESCE (@.my_book_category, book_category)
> AND book_type = COALESCE (@.my_book_type, book_type)
> AND author_name = COALESCE (@.my_author_name, author_)name) ;
That would give a tablescan.
Can you imagine how badly that will perform on a table with a few million
rows perhaps 1GB in size.
To do the tablescan everytime a user ran the query SQL Server would have to
read 1GB of data.
Now, multiply that by 10 users, thats 10GB of data SQL Server now needs to
read in order to process all 10 queries.
You are going to need one hell of a big box!
The correct way to do this is to either use IF..ELSE to make the query more
specific depending on which parameters are specified, ie. only put the
parameters specified on the WHERE clause.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1138944895.900338.14850@.g47g2000cwa.googlegroups.com...
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are. Sample data is also a good idea, along with clear
> specifications. It is very hard to debug code when you do not let us
> see it.
> It would also help if you would learn that a field and a column nothing
> whatsoever alike and that names like "type", "category", etc. are too
> vague to be data element names/. Try something like this:
> CREATE PROCEDURE GetBook
> (@.my_book_category VARCHAR(10) = NULL, -- wild guess
> @.my_book_type INTEGER = NULL, -- Dewey Decimal ?
> @.my_author_name VARCHAR(25) = NULL)
> AS
> SELECT book_category, book_type, author_name
> FROM Library
> WHERE book_category = COALESCE (@.my_book_category, book_category)
> AND book_type = COALESCE (@.my_book_type, book_type)
> AND author_name = COALESCE (@.my_author_name, author_)name) ;
>|||Don't forget Ken that the query below will give you a very general plan so
you'll probably end up doing a table scan.
Check the plan before you decided on the solution.
Much better to use IF ELSE or dynamic SQL and taylor your query to the
parameters passed.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"KenJ" <kenjohnson@.hotmail.com> wrote in message
news:1138899994.839100.3010@.o13g2000cwo.googlegroups.com...
> I'm not familiar with query builder. Can you run it in query analyzer?
> Here is a sample script that creates a table, loads some dummy data,
> runs the procedure with several variations then drops the table and
> procedure. I've run it in query analyzer to be sure it works...
> USE tempdb
> GO
> SET nocount ON
> GO
> CREATE TABLE book (
> bookid INT IDENTITY( 1 , 1 ) NOT NULL PRIMARY KEY
> , category VARCHAR(10) NULL
> , booktype VARCHAR(10) NULL
> , author VARCHAR(10) NULL)
> GO
> INSERT book
> VALUES('fiction'
> , 'paperback'
> , 'twain')
> INSERT book
> VALUES('fiction'
> , 'hardbound'
> , 'asimov')
> INSERT book
> VALUES('fiction'
> , 'paperback'
> , 'rand')
> GO
> CREATE PROCEDURE getbook
> @.category VARCHAR(10) = NULL
> , @.bookType VARCHAR(10) = NULL
> , @.author VARCHAR(10) = NULL
> AS
> SELECT category
> , booktype
> , author
> FROM book
> WHERE (category = @.category
> OR @.category IS NULL )
> AND (booktype = @.bookType
> OR @.bookType IS NULL )
> AND (author = @.author
> OR @.author IS NULL )
> GO
> -- get all fiction books
> EXEC getbook @.category = 'fiction'
> -- all fiction books by rand
> EXEC getbook @.category = 'fiction' ,
> @.author = 'rand'
> -- all paperbacks
> EXEC getbook @.bookType = 'paperback'
> -- returns all books since we don't supply any filter
> EXEC getbook
> GO
> DROP TABLE book
> GO
> DROP PROCEDURE getbook
> GO
>

Sunday, March 11, 2012

Conditional Selection

Hi,
Hi,
I'm trying to construct a query (in a stored procedure) which will have a nu
mber of
selection criteria based on input parameters. There are a number of these p
arameters
whose selection conditions they represent which all have to be true for a ro
w to be
returned in the resultset.
The basic query is:
SELECT Store, StoreNumber
FROM Stores
WHERE ...
I'm trying to come up with the WHERE clause.
For example, I want to define a parameter named @.ExcludeSpecialties which if
it has
the value 1, means to return all stores but exclude stores whose StoreNumber
is in
the list (800, 802, 804). If the parameter has the value 0, then it means "
don't
care" and all StoreNumbers should be returned.
One could certainly argue that there probably should have been an column in
the
Stores row to indicate the store is a specialty store, rather than using a h
ard-wired
list of numbers. But the current data schema cannot be easily changed. Bes
ides, the
list never changes.
Indeed, there is a Franchise bit column in the row which is selected by anot
her
parameter called @.ExcludeFranchise whose WHERE predicate could be written as
:
WHERE Franchise = CASE WHEN @.ExcludeFranchise = 1 THEN 0 ELSE Franchise END
and if all the parameters were like this, I wouldn't be posting. Sadly, for
the
Specialties test I'm stuck with a NOT IN list.
This is easy enough to do in an IF/ELSE block, but there are several such si
milar
parameters whose values may be specified in any combination. This, I think,
makes
IF/ELSE impractical as the number of IF/ELSE statements to handle all possib
le
combinations would grow very quickly.
I'm hoping there is a simple solution to this NOT IN list, and it's just tha
t I can't
see it.
Can anyone help?
Thanks,
-- JeffTry this first ( Several popular approaches are details here ):
http://www.sommarskog.se/dyn-search.html
Anith|||try this in your where clause. Let me know if this helps
((@.ExcludeSpecialties = 0) or (storenumber not in (800, 802, 804)))|||You could store the specialties flag in a seperate table, with StoreNumber
as the key, then query against it instead of using the hardcoded list. This
way, when a new specialty store opens, or one of the existing stores
changes, you will just insert a row into the table and not have to touch the
code. It would be better to have it in the original table, but if you can't
change the original, maybe adding a new table is an option...
create table SpecialtyStores
(StoreNumber integer, Specialty bit) -- add PK and FK info here
SELECT Store, StoreNumber
FROM Stores
left outer join SpecialtyStores as spec
on stores.StoreNumber = spec.StoreNumber
WHERE
Specialty = CASE WHEN @.ExcludeFranchise = 1 THEN 0 ELSE 1 END
"Jeff Mason" <je.mason@.comcast.net> wrote in message
news:vvl1525lk0m8baqqpv5vs5q1g36a9c6jpa@.
4ax.com...
> Hi,
> Hi,
> I'm trying to construct a query (in a stored procedure) which will have a
number of
> selection criteria based on input parameters. There are a number of these
parameters
> whose selection conditions they represent which all have to be true for a
row to be
> returned in the resultset.
> The basic query is:
> SELECT Store, StoreNumber
> FROM Stores
> WHERE ...
> I'm trying to come up with the WHERE clause.
> For example, I want to define a parameter named @.ExcludeSpecialties which
if it has
> the value 1, means to return all stores but exclude stores whose
StoreNumber is in
> the list (800, 802, 804). If the parameter has the value 0, then it means
"don't
> care" and all StoreNumbers should be returned.
> One could certainly argue that there probably should have been an column
in the
> Stores row to indicate the store is a specialty store, rather than using a
hard-wired
> list of numbers. But the current data schema cannot be easily changed.
Besides, the
> list never changes.
> Indeed, there is a Franchise bit column in the row which is selected by
another
> parameter called @.ExcludeFranchise whose WHERE predicate could be written
as:
> WHERE Franchise = CASE WHEN @.ExcludeFranchise = 1 THEN 0 ELSE Franchise
END
> and if all the parameters were like this, I wouldn't be posting. Sadly,
for the
> Specialties test I'm stuck with a NOT IN list.
> This is easy enough to do in an IF/ELSE block, but there are several such
similar
> parameters whose values may be specified in any combination. This, I
think, makes
> IF/ELSE impractical as the number of IF/ELSE statements to handle all
possible
> combinations would grow very quickly.
> I'm hoping there is a simple solution to this NOT IN list, and it's just
that I can't
> see it.
> Can anyone help?
> Thanks,
> -- Jeff|||On Thu, 27 Apr 2006 08:13:02 -0700, Omnibuzz <Omnibuzz@.discussions.microsoft
.com>
wrote:

>try this in your where clause. Let me know if this helps
>((@.ExcludeSpecialties = 0) or (storenumber not in (800, 802, 804)))
Duh.
That did it. I knew it was something simple. I was having a Brain Fog, I gu
ess.
Thank you.
-- Jeff

Conditional query criteria in Access 2003 with SQL Server BE

Hello:
I just upsized an Access 2003 application to a SQL Server 2000 back end.
There is a query that worked fine before the upsize, but does not work now.
The purpose of the query is to give me a list of employees that are
scheduled to work today. The employee master row has 7 checkboxes for the
days of the week.
Here is the SQL for the query. Can someone tell me how to make this work
with the SQL Server BE?
SELECT EmployeeQry.EmpID, EmployeeQry.EmpLast, EmployeeQry.EmpFirst,
EmployeeQry.EmpSSN, EmployeeQry.EmpDept, EmployeeQry.Monday,
EmployeeQry.Tuesday, EmployeeQry.Wednesday, EmployeeQry.Thursday,
EmployeeQry.Friday, EmployeeQry.Saturday, EmployeeQry.Sunday
FROM EmployeeQry
WHERE (((EmployeeQry.Monday)=IIf(Format(Date(),"dddd")=' Monday',True))) OR
(((EmployeeQry.Tuesday)=IIf(Format(Date(),"dddd")= 'Tuesday',True))) OR
(((EmployeeQry.Wednesday)=IIf(Format(Date(),"dddd" )='Wednesday',True))) OR
(((EmployeeQry.Thursday)=IIf(Format(Date(),"dddd") ='Thursday',True))) OR
(((EmployeeQry.Friday)=IIf(Format(Date(),"dddd")=' Friday',True))) OR
(((EmployeeQry.Saturday)=IIf(Format(Date(),"dddd") ='Saturday',True))) OR
(((EmployeeQry.Sunday)=IIf(Format(Date(),"dddd")=' Sunday',True)));
Thanks
Steve
John:
Thanks for the reply. Sorry ... the query returns no rows, when in fact
there are many for each day of the week. I tried your suggestions ...
comments under each suggestion.
Steve
"John Spencer" wrote:

> I would try:
> Adding to the IIF statement.
> (((EmployeeQry.Tuesday)=IIf(Format(Date(),"dddd")= 'Tuesday',True,False)))
>
I tried this with no difference

> Or even simpler, drop the IIF statement
> EmployeeQry.Tuesday= (Format(Date(),"dddd")='Tuesday')
>
This returned all rows of the table.

> IF that still fails then try reversing the logic of the test. Just in
> case SQL server is storing the data as 1 (True) and 0 (False).
> EmployeeQry.Tuesday =IIf(Format(Date(),"dddd")<>'Tuesday',False, True)
>
Didn't work either.

> By the way "..., but does not work now." is not very descriptive of the
> problem. Does that mean, you get the wrong results, no results, an
> error, different results than expected at times, a syntax error or ...?
> Please try to be a bit more specific in describing the problem.
> '================================================= ===
> John Spencer
> Access MVP 2002-2005, 2007
> Center for Health Program Development and Management
> University of Maryland Baltimore County
> '================================================= ===
>
> Steve Happ wrote:
>
|||What type is EmployeeQry.Monday on SQL Server?
It could be as John alluded to that
for SQL Server
True = 1
False = 0
for Access
True = -1
False = 0
I might misunderstand, but you try "1"
instead of "True"
WHERE (((EmployeeQry.Monday)=IIf(Format(Date(),"dddd")=' Monday',1,0))) OR
"Steve Happ" wrote:
> I just upsized an Access 2003 application to a SQL Server 2000 back end.
> There is a query that worked fine before the upsize, but does not work
> now.
> The purpose of the query is to give me a list of employees that are
> scheduled to work today. The employee master row has 7 checkboxes for the
> days of the week.
> Here is the SQL for the query. Can someone tell me how to make this work
> with the SQL Server BE?
> SELECT EmployeeQry.EmpID, EmployeeQry.EmpLast, EmployeeQry.EmpFirst,
> EmployeeQry.EmpSSN, EmployeeQry.EmpDept, EmployeeQry.Monday,
> EmployeeQry.Tuesday, EmployeeQry.Wednesday, EmployeeQry.Thursday,
> EmployeeQry.Friday, EmployeeQry.Saturday, EmployeeQry.Sunday
> FROM EmployeeQry
> WHERE (((EmployeeQry.Monday)=IIf(Format(Date(),"dddd")=' Monday',True))) OR
> (((EmployeeQry.Tuesday)=IIf(Format(Date(),"dddd")= 'Tuesday',True))) OR
> (((EmployeeQry.Wednesday)=IIf(Format(Date(),"dddd" )='Wednesday',True))) OR
> (((EmployeeQry.Thursday)=IIf(Format(Date(),"dddd") ='Thursday',True))) OR
> (((EmployeeQry.Friday)=IIf(Format(Date(),"dddd")=' Friday',True))) OR
> (((EmployeeQry.Saturday)=IIf(Format(Date(),"dddd") ='Saturday',True))) OR
> (((EmployeeQry.Sunday)=IIf(Format(Date(),"dddd")=' Sunday',True)));
>
> Thanks
> Steve
|||or
WHERE (((EmployeeQry.Monday)=IIf(Format(Date(),"dddd")=' Monday',1,Null))) OR
the point being that if Monday = "True"
on SQL, then its value is 1
which will never be equal to Access "True"
which is equal to -1
"Gary Walter" wrote:
> What type is EmployeeQry.Monday on SQL Server?
> It could be as John alluded to that
> for SQL Server
> True = 1
> False = 0
> for Access
> True = -1
> False = 0
> I might misunderstand, but you try "1"
> instead of "True"
> WHERE (((EmployeeQry.Monday)=IIf(Format(Date(),"dddd")=' Monday',1,0))) OR
>
> "Steve Happ" wrote:
>

Conditional Query

Hi,

I'm trying to construct a query (in a stored procedure) which will have a number of
selection criteria based on input parameters. There are a number of these parameters
whose selection conditions they represent which all have to be true for a row to be
returned in the resultset.

The basic query is:

SELECT Store, StoreNumber
FROM Stores
WHERE ...

I'm trying to come up with the WHERE clause.

For example, I want to define a parameter named @.ExcludeSpecialties which if it has
the value 1, means to return all stores but exclude stores whose StoreNumber is in
the list (800, 802, 804). If the parameter has the value 0, then it means "don't
care" and all StoreNumbers should be returned.

One could certainly argue that there probably should have been an column in the
Stores row to indicate the store is a specialty store, rather than using a hard-wired
list of numbers. But the current data schema cannot be easily changed. Besides, the
list never changes.

Indeed, there is a Franchise bit column in the row which is selected by another
parameter called @.ExcludeFranchise whose WHERE predicate could be written as:

WHERE Franchise = CASE WHEN @.ExcludeFranchise = 1 THEN 0 ELSE Franchise END

and if all the parameters were like this, I wouldn't be posting. Sadly, for the
Specialties test I'm stuck with a NOT IN list.

This is easy enough to do in an IF/ELSE block, but there are several such similar
parameters whose values may be specified in any combination. This, I think, makes
IF/ELSE impractical as the number of IF/ELSE statements to handle all possible
combinations would grow very quickly.

I'm hoping there is a simple solution to this NOT IN list, and it's just that I can't
see it.

Can anyone help?

Thanks,

-- JeffJeff Mason (je.mason@.comcast.net) writes:
> I'm trying to construct a query (in a stored procedure) which will have
> a number of selection criteria based on input parameters. There are a
> number of these parameters whose selection conditions they represent
> which all have to be true for a row to be returned in the resultset.

I have an article on by web site that discusses a couple of alternatives,
both with static and dynamic SQL:
http://www.sommarskog.se/dyn-search.html

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Thu, 27 Apr 2006 11:10:01 -0400, Jeff Mason wrote:

(snip)
>For example, I want to define a parameter named @.ExcludeSpecialties which if it has
>the value 1, means to return all stores but exclude stores whose StoreNumber is in
>the list (800, 802, 804). If the parameter has the value 0, then it means "don't
>care" and all StoreNumbers should be returned.

Hi Jeff,

WHERE ( @.ExcludeSpecialties = 0 OR StoreNumber NOT IN (800, 802, 804) )

(snip)
>Indeed, there is a Franchise bit column in the row which is selected by another
>parameter called @.ExcludeFranchise whose WHERE predicate could be written as:
>WHERE Franchise = CASE WHEN @.ExcludeFranchise = 1 THEN 0 ELSE Franchise END
>and if all the parameters were like this, I wouldn't be posting. Sadly, for the
>Specialties test I'm stuck with a NOT IN list.

That is indeed a common method to write such queries.

Do read the article Erland posted a link to - it describes a bunch of
methods to achieve what you need, with all their strengths and
weaknesses. Good stuff!

--
Hugo Kornelis, SQL Server MVP

Wednesday, March 7, 2012

Conditional Formatting Question RS 2005

Hi,

Does anyone know how to implement conditional formatting with three criteria (e.g. If a value is <83 then BGcolour Green, between 83 and 90 the BGcolour is Yellow, greater than 90 the BGcolour is green).

I have tried approaching it in two ways. One was with a case statement as follows:

CASE

WHEN TargetPer < 83 THEN 'Red'

WHEN TargetPer >= 83 AND TargetPer < 90 THEN 'Yellow'

WHEN TargetPer >= 90 THEN 'Green'

END AS BGColor

TargetPer being the value i wish to examine. I returned this in the query dataset i am using to populate the report. I then used the following expression to set the backgroundcolor property:

=Fields!BGColor.Value

Unfortunately I get no yellow fields for the appropriate values (even though the BGcolor value says yellow!!!)

The other approach i have used is the following expression to set the background colour

=IIf(Fields!TargetPer.Value>90,"Green",IIf(Fields!BGColor.Value>=83 and Fields!BGColor.Value <=90, "Yellow","Red"))

For this i still have no yellows just reds and greens!

I am using a matrix report that has subgroups on the columns for table!

Any ideas anyone?

Thanks

Marek

You have 2 options to implement CASE logic. Using nested Iif() calls or using the Switch() method. The definition is Switch(<condition>, <value>[,<condition>, <value>],....). So for the first condition that evaluates to true it's corresponding value is returned.

In your case the problem is that you are not applying any aggregation function around your field references. Any groupping naturally means that some aggregation will be applied. If not specified, the default aggregation function is First() rather than Sum(). Also you seem to have Fields!BGColor.Value in your condition which I believe should be Fields!TargetPer.Value.

So the way I see it you have 2 options to re-write your statement:

Using Iif():

=Iif

( Sum(Fields!TargetPer.Value) > 90

, "Green"

, Iif

( Sum(Fields!TargetPer.Value) >= 83

, "Yellow"

, "Red"

)

)

Using Switch():

=Switch

( Sum(Fields!TargetPer.Value) > 90

, "Green"

, Sum(Fields!TargetPer.Value) >= 83

, "Yellow"

, Sum(Fields!TargetPer.Value) < 83 'You could also put True here (CASE ELSE)

, "Red"

)

Saturday, February 25, 2012

Conditional DistinctCount

I want a distinctCount in a group for a criteria. How can i do it
I am doing something like this
=CountDistinct(IIF(Fields!CATE.Value='N'),Fields!ERNR.Value,nothing),
"grpResCat")
Is this right?Try this:
=CountDistinct(IIF(Trim(CStr(Fields!CATE.Value)) = "N", Fields!ERNR.Value,
Nothing), "grpResCat")
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Anand Prabhu" <AnandPrabhu@.discussions.microsoft.com> wrote in message
news:594F0D45-8C6E-4988-A41D-DA03E858EC86@.microsoft.com...
> I want a distinctCount in a group for a criteria. How can i do it
> I am doing something like this
> =CountDistinct(IIF(Fields!CATE.Value='N'),Fields!ERNR.Value,nothing),
> "grpResCat")
> Is this right?
>

Friday, February 10, 2012

concatenating fields

Hi,
Have a simple question.
i have a value field and a criteria field would need to find all records for which the value matches the criteria
Eg:
table
name value criteria target
a 4 <=10
In the 'table' i need to update target based on whether
value meets the criteria

SELECT name FROM table
WHERE value + criteria

does not work

what am i doing wrong?
thanks
manjuI'm not clear on this but if you are saying that

SELECT name FROM table
WHERE value + criteria

does not work, that is because you have left of the condition, or the test. As in "value + criteria" is what?

Is "value + criteria" > 10
Is "value + criteria" = 568
Is "value + criteria" between 26 and 55

You need to add the test condition, the right hand side of the argument.|||im sorry if i wasnt clear.
actually the test condition is in the criteria field
so the table has 3 fields
name:a
value:4
criteria:<=10
thats why i need to concatenate
value and criteria and evaluate that expr
So if 4<=10 i would need to do something
thanks
manju|||You'll need to use dynamic SQL with the EXEC command.

DECLARE @.sqlcmd varchar(100)

SELECT @.sqlcmd = 'SELECT * FROM ' + name + ' WHERE ' + value + criteria

EXEC (@.sqlcmd)

Something like that.|||Thx for the reply but this will not do it
name, value and criteria are fields in sql
i need to evaluate the expr by combining 2 fields in that
table and perform an action based on that value
Eg:
if the table has
a|4|<=5
b|6|<=4
i need to select records for which
the value and criteria match, in this case
only 'a'
thanks
manju|||-- ------------------------
-- Run this
-- ------------------------
create table #tmp (tblname varchar(10), value varchar(10), critiria varchar(10), target varchar(10))
insert into #tmp values ('a','4','<=10',Null)
insert into #tmp values ('b','11','<=10',Null)
select * from #tmp
declare @.tblname varchar(10), @.sqlcmd varchar(100)
select @.tblname = min(tblname) from #tmp
while @.tblname is not null begin
select @.sqlcmd = 'update #tmp set target = ''~'' where value = ''' + value + ''' and critiria = ''' + critiria + ''' and ' + value + critiria From #tmp where tblname = @.tblname
raiserror(@.sqlcmd,0,1) with nowait
exec (@.sqlcmd)
select @.tblname = min(tblname) from #tmp where tblname > @.tblname
end
select * from #tmp

-- ------------------------
-- Should produce this
-- ------------------------
tblname value critiria target
---- ---- ---- ----
a 4 <=10 NULL
b 11 <=10 NULL

(2 row(s) affected)

update #tmp set target = '~' where value = '4' and critiria = '<=10' and 4<=10

(1 row(s) affected)

update #tmp set target = '~' where value = '11' and critiria = '<=10' and 11<=10

(0 row(s) affected)

tblname value critiria target
---- ---- ---- ----
a 4 <=10 ~
b 11 <=10 NULL

(2 row(s) affected)

That's about as good as I can do with the info provided... Hope it helps!