help with query

this is my query
Select
fname
,Case
When Count(animal) >1 Then Convert(nvarchar(64), Count(animal))
Else animal
End
from tanimals
Group By fname, animal
create table tanimals
(
fname nvarchar(64),
animal nvarchar(64),
)
insert into tanimals (fname, animal) values ('James', 'cat')
insert into tanimals (fname, animal) values ('James', 'dog')
insert into tanimals (fname, animal) values ('Cal', 'dog')
insert into tanimals (fname, animal) values ('Cal', 'fish')
insert into tanimals (fname, animal) values ('Bret', 'fish')
insert into tanimals (fname, animal) values ('Kate', 'fish')
insert into tanimals (fname, animal) values ('Cal', 'turtle')
I'm trying to produce
fname animals
James 2
Cal 3
Kate fish
Bret fish
if the person have more that one animals to give me the number of
animals, but if they own just one to return the animal that they own.
ThanksSelect
fname
,Case
When Count(animal) >1 Then Convert(nvarchar(64), Count(animal))
Else MAX(animal) -- Use aggregate to satisfy Group By
End as Animals
from tanimals
Group By fname -- Not including animal
order by Animals
RLF
<vncntj@.hotmail.com> wrote in message
news:1174415339.746907.287300@.o5g2000hsb.googlegroups.com...
> this is my query
> Select
> fname
> ,Case
> When Count(animal) >1 Then Convert(nvarchar(64), Count(animal))
> Else animal
> End
> from tanimals
> Group By fname, animal
> create table tanimals
> (
> fname nvarchar(64),
> animal nvarchar(64),
> )
> insert into tanimals (fname, animal) values ('James', 'cat')
> insert into tanimals (fname, animal) values ('James', 'dog')
> insert into tanimals (fname, animal) values ('Cal', 'dog')
> insert into tanimals (fname, animal) values ('Cal', 'fish')
> insert into tanimals (fname, animal) values ('Bret', 'fish')
> insert into tanimals (fname, animal) values ('Kate', 'fish')
> insert into tanimals (fname, animal) values ('Cal', 'turtle')
>
> I'm trying to produce
> fname animals
> James 2
> Cal 3
> Kate fish
> Bret fish
> if the person have more that one animals to give me the number of
> animals, but if they own just one to return the animal that they own.
> Thanks
>|||Below are two options. Second requires SQL Server 2005:
SELECT fname, CAST(COUNT(*) AS varchar(30)) AS animals
FROM tanimals
GROUP BY fname
HAVING COUNT(*) > 1
UNION ALL
SELECT fname, animal
FROM tanimals
WHERE fname NOT IN
(
SELECT fname
FROM tanimals
GROUP BY fname
HAVING COUNT(*) > 1
);
WITH ganimals AS
(SELECT fname, CAST(COUNT(*) AS varchar(30)) AS animals
FROM tanimals
GROUP BY fname
HAVING COUNT(*) > 1)
SELECT fname, animal
FROM tanimals
WHERE fname NOT IN (SELECT fname FROM ganimals)
UNION
SELECT fname, animals FROM ganimals
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<vncntj@.hotmail.com> wrote in message news:1174415339.746907.287300@.o5g2000hsb.googlegroups.com...
> this is my query
> Select
> fname
> ,Case
> When Count(animal) >1 Then Convert(nvarchar(64), Count(animal))
> Else animal
> End
> from tanimals
> Group By fname, animal
> create table tanimals
> (
> fname nvarchar(64),
> animal nvarchar(64),
> )
> insert into tanimals (fname, animal) values ('James', 'cat')
> insert into tanimals (fname, animal) values ('James', 'dog')
> insert into tanimals (fname, animal) values ('Cal', 'dog')
> insert into tanimals (fname, animal) values ('Cal', 'fish')
> insert into tanimals (fname, animal) values ('Bret', 'fish')
> insert into tanimals (fname, animal) values ('Kate', 'fish')
> insert into tanimals (fname, animal) values ('Cal', 'turtle')
>
> I'm trying to produce
> fname animals
> James 2
> Cal 3
> Kate fish
> Bret fish
> if the person have more that one animals to give me the number of
> animals, but if they own just one to return the animal that they own.
> Thanks
>|||vncntj,
I believe this will work for you:
select
fname
, animal AnimalOrCount
from tanimals
where fname in
(
select fname
from tanimals
group by fname
having count(*) = 1
)
union all
select fname
, cast(count(*) as nvarchar(64)) AnimalOrCount
from tanimals
group by fname
having count(*) > 1
-- Bill
<vncntj@.hotmail.com> wrote in message
news:1174415339.746907.287300@.o5g2000hsb.googlegroups.com...
> this is my query
> Select
> fname
> ,Case
> When Count(animal) >1 Then Convert(nvarchar(64), Count(animal))
> Else animal
> End
> from tanimals
> Group By fname, animal
> create table tanimals
> (
> fname nvarchar(64),
> animal nvarchar(64),
> )
> insert into tanimals (fname, animal) values ('James', 'cat')
> insert into tanimals (fname, animal) values ('James', 'dog')
> insert into tanimals (fname, animal) values ('Cal', 'dog')
> insert into tanimals (fname, animal) values ('Cal', 'fish')
> insert into tanimals (fname, animal) values ('Bret', 'fish')
> insert into tanimals (fname, animal) values ('Kate', 'fish')
> insert into tanimals (fname, animal) values ('Cal', 'turtle')
>
> I'm trying to produce
> fname animals
> James 2
> Cal 3
> Kate fish
> Bret fish
> if the person have more that one animals to give me the number of
> animals, but if they own just one to return the animal that they own.
> Thanks
>|||> Else MAX(animal) -- Use aggregate to satisfy Group By
LOL... That is much more clever than my approach. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:eivaP9xaHHA.4888@.TK2MSFTNGP06.phx.gbl...
> Select
> fname
> ,Case
> When Count(animal) >1 Then Convert(nvarchar(64), Count(animal))
> Else MAX(animal) -- Use aggregate to satisfy Group By
> End as Animals
> from tanimals
> Group By fname -- Not including animal
> order by Animals
> RLF
> <vncntj@.hotmail.com> wrote in message news:1174415339.746907.287300@.o5g2000hsb.googlegroups.com...
>> this is my query
>> Select
>> fname
>> ,Case
>> When Count(animal) >1 Then Convert(nvarchar(64), Count(animal))
>> Else animal
>> End
>> from tanimals
>> Group By fname, animal
>> create table tanimals
>> (
>> fname nvarchar(64),
>> animal nvarchar(64),
>> )
>> insert into tanimals (fname, animal) values ('James', 'cat')
>> insert into tanimals (fname, animal) values ('James', 'dog')
>> insert into tanimals (fname, animal) values ('Cal', 'dog')
>> insert into tanimals (fname, animal) values ('Cal', 'fish')
>> insert into tanimals (fname, animal) values ('Bret', 'fish')
>> insert into tanimals (fname, animal) values ('Kate', 'fish')
>> insert into tanimals (fname, animal) values ('Cal', 'turtle')
>>
>> I'm trying to produce
>> fname animals
>> James 2
>> Cal 3
>> Kate fish
>> Bret fish
>> if the person have more that one animals to give me the number of
>> animals, but if they own just one to return the animal that they own.
>> Thanks
>|||It is odd, but when it works, it works a charm. :-)
RLF
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OEGliNyaHHA.4008@.TK2MSFTNGP05.phx.gbl...
>> Else MAX(animal) -- Use aggregate to satisfy Group By
> LOL... That is much more clever than my approach. :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:eivaP9xaHHA.4888@.TK2MSFTNGP06.phx.gbl...
>> Select
>> fname
>> ,Case
>> When Count(animal) >1 Then Convert(nvarchar(64), Count(animal))
>> Else MAX(animal) -- Use aggregate to satisfy Group By
>> End as Animals
>> from tanimals
>> Group By fname -- Not including animal
>> order by Animals
>> RLF
>> <vncntj@.hotmail.com> wrote in message
>> news:1174415339.746907.287300@.o5g2000hsb.googlegroups.com...
>> this is my query
>> Select
>> fname
>> ,Case
>> When Count(animal) >1 Then Convert(nvarchar(64), Count(animal))
>> Else animal
>> End
>> from tanimals
>> Group By fname, animal
>> create table tanimals
>> (
>> fname nvarchar(64),
>> animal nvarchar(64),
>> )
>> insert into tanimals (fname, animal) values ('James', 'cat')
>> insert into tanimals (fname, animal) values ('James', 'dog')
>> insert into tanimals (fname, animal) values ('Cal', 'dog')
>> insert into tanimals (fname, animal) values ('Cal', 'fish')
>> insert into tanimals (fname, animal) values ('Bret', 'fish')
>> insert into tanimals (fname, animal) values ('Kate', 'fish')
>> insert into tanimals (fname, animal) values ('Cal', 'turtle')
>>
>> I'm trying to produce
>> fname animals
>> James 2
>> Cal 3
>> Kate fish
>> Bret fish
>> if the person have more that one animals to give me the number of
>> animals, but if they own just one to return the animal that they own.
>> Thanks
>>
>|||Thanks to everyone. It all works.
You guys rock!!
On Mar 20, 4:38 pm, "Russell Fields" <russellfie...@.nomail.com> wrote:
> It is odd, but when it works, it works a charm. :-)
> RLF
> "Tibor Karaszi" <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote in
> messagenews:OEGliNyaHHA.4008@.TK2MSFTNGP05.phx.gbl...
>
> >> Else MAX(animal) -- Use aggregate to satisfy Group By
> > LOL... That is much more clever than my approach. :-)
> > --
> > Tibor Karaszi, SQL Server MVP
> >http://www.karaszi.com/sqlserver/default.asp
> >http://sqlblog.com/blogs/tibor_karaszi
> > "Russell Fields" <russellfie...@.nomail.com> wrote in message
> >news:eivaP9xaHHA.4888@.TK2MSFTNGP06.phx.gbl...
> >> Select
> >> fname
> >> ,Case
> >> When Count(animal) >1 Then Convert(nvarchar(64), Count(animal))
> >> Else MAX(animal) -- Use aggregate to satisfy Group By
> >> End as Animals
> >> from tanimals
> >> Group By fname -- Not including animal
> >> order by Animals
> >> RLF
> >> <vnc...@.hotmail.com> wrote in message
> >>news:1174415339.746907.287300@.o5g2000hsb.googlegroups.com...
> >> this is my query
> >> Select
> >> fname
> >> ,Case
> >> When Count(animal) >1 Then Convert(nvarchar(64), Count(animal))
> >> Else animal
> >> End
> >> from tanimals
> >> Group By fname, animal
> >> create table tanimals
> >> (
> >> fname nvarchar(64),
> >> animal nvarchar(64),
> >> )
> >> insert into tanimals (fname, animal) values ('James', 'cat')
> >> insert into tanimals (fname, animal) values ('James', 'dog')
> >> insert into tanimals (fname, animal) values ('Cal', 'dog')
> >> insert into tanimals (fname, animal) values ('Cal', 'fish')
> >> insert into tanimals (fname, animal) values ('Bret', 'fish')
> >> insert into tanimals (fname, animal) values ('Kate', 'fish')
> >> insert into tanimals (fname, animal) values ('Cal', 'turtle')
> >> I'm trying to produce
> >> fname animals
> >> James 2
> >> Cal 3
> >> Kate fish
> >> Bret fish
> >> if the person have more that one animals to give me the number of
> >> animals, but if they own just one to return the animal that they own.
> >> Thanks- Hide quoted text -
> - Show quoted text -

Help with optional parameter query with IN statements

I have a query with 17 separate, optional, parameters. I have declared each parameter = NULL so that I can test for NULL in the case that the user didn’t not pass in the parameter.

I am new enough to SQL Server that I am having difficulty building the WHERE clause with all of these optional parameters.

One solution I was advised on by a well paid SQL programmer, was to use a string in the stored proc and dynamically build the WHERE clause and exec it at the end of the sp. But the whole point of a stored proc is that it can be compiled and cached to make it faster, yet the string approach makes it have to compile every time it’s run! Not a good solution, but maybe it’s the best I can do . . .

I have tried many different approaches using different functions, etc. but I’ve hit a brick wall. Any help in sorting it out with YOUR techniques would be greatly appreciated:

1. To add the parameter to the WHERE clause and test for NULL I’ve used the COALESCE function such as “WHERE table.fieldname = COALESCE(@.Param, table.fieldname)”. This works well if there is only one item in the parameter, but in the case that I pass multiple items to the parameter, it completely fails.

2. To handle multiple items, for example, if @.Param = ‘3,7,98’ (essentially, a csv separated list of keys)

Code Snippet

WHERE table.fieldname IN(COALESCE(@.Param, table.fieldname))

doesn’t work because @.Param needs to be parsed from a string into an array of integers in the parameter. So, I am using a UDF I discovered to parse the multi-item parameter. The UDF can be found at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag01/html/TreatYourself.asp and it returns a table variable that can be used in an IN statement. So I’m using

Code Snippet

ISNULL(table.fieldname, 0) IN (SELECT value FROM dbo.fn_Split(@.Param,’,’))

which works brilliantly in my WHERE statement AS LONG AS @.Param ISN’T NULL. So how do I test for NULL first and still use this approach to multi-item parameters?

I’ve tried

Code Snippet

WHERE @.Param IS NULL OR ISNULL(table.fieldname, 0) IN (SELECT value FROM dbo.fn_Split(@.Param,’,’))

and though it works, the OR causes it to slow way down as it compares every record for the OR. (It slows down by approximately 800%.) The other thing I tried was

Code Snippet

ISNULL (table.fieldname, 0) IN (CASE WHEN @.Param IS NULL THEN ISNULL(table.fieldname, 0) ELSE (SELECT value FROM dbo.fn_Split(@.Param,’,’)))

This fails with “Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression” due to the multiple values in the parameter. (I can’t understand why the line without the CASE statement works, but the CASE line doesn’t!)

Am I even on the right track, cuz this is driving me mad and I just need a way to deal with optional multi-item parameters in an IN statement? HELP!

First of all, I think we need more background information...

-What is the end goal of the query?

-How are users interacting with the application? (reporting services, asp.net, etc.)

-What is the database structure like? How many tables are being joined, Approx size of tables

-Are all 17 parameters needed? Perhaps you could develop a set of linked reports that give the user more detailed information as they drill deeper.

As far as performance, I wouldn't worry too much about having the query compiled every time, this really doesn't take that long. Query performance in most cases comes from proper indexing.

|||

Anthony,

Thank you for your response!

Here's the info:

- I migrated an Access Jet database to SQL but still use Access as the client.

- Query is the source for an Access report that is the most comprehensive report in the program. The user has sorting and grouping options in the report that allow them to view most everything they need from one report.

- The database structure is very good, and the tables aren't huge, the average size of a table is 15k records, but this query touches approximately 1/4 of all tables in the db. 20 tables are being joined and the largest table of the 20 has 28k records.

- This query is inserting all the values into a temp table which I then run my report queries on. Here's ALL the code with some of the WHERE clause: (I've put a couple of different multi-item parameter techniques in the WHERE clause- of course, neither of them work.)

Code Snippet

ALTER PROCEDURE [dbo].[spAG_ProjectStatusTESTFunction]

-- Add the parameters for this sp here

@.AGProjectKey varchar(255) = NULL,

@.OpenClosed bit = NULL,

@.ClientKey varchar(200) = NULL,

@.ProdCoKey varchar(255) = NULL,

@.ProjectTitle varchar(100) = NULL,

@.AgentKey varchar(50) = NULL,

@.GenreKey varchar(200) = NULL,

@.ProjectTypeKey varchar(255) = NULL,

@.ProjectType2Key varchar(255) = NULL,

@.DirectorKey varchar(255) = NULL,

@.ProdKey varchar(255) = NULL,

@.ExecProdKey varchar(255) = NULL,

@.ContactKey varchar(255) = NULL,

@.ProjectStatus varchar(50) = NULL,

@.Distributor varchar(50) = NULL,

@.MusSup varchar(255) = NULL,

@.NonClientFinalOut varchar(255) = NULL

AS

BEGIN

DECLARE @.sSQL varchar(8000);

DECLARE @.sWHERE varchar(8000);

DECLARE @.sORDERBY varchar(8000);

CREATE TABLE #rAG_ProjectStatusPre

(

[CoProdCompany] varchar(255) NULL, [ProductionCompanyDesc] varchar(500) NULL,

[AGProjectKey] int NOT NULL, [ProjectTitle] nvarchar(100) NULL, [ProdOfficeAddr1] nvarchar(50) NULL,

[ProdOfficeAddr2] nvarchar(50) NULL, [ProdOfficeAddr3] nvarchar(50) NULL, [ProdOfficeCity] nvarchar(15) NULL,

[ProdOfficeState] nvarchar(2) NULL, [ProdOfficeFax] nvarchar(20) NULL, [ProdOfficePhone] nvarchar(20) NULL,

[ProdOfficeZip] nvarchar(9) NULL, [ClientKey] int NULL, [FirstName] nvarchar(15) NULL, [LastName] nvarchar(20) NULL,

[AgentKey] int NULL, [AgentName] nvarchar(30) NULL, [Producer] nvarchar(30) NULL,

[ExecProducer] nvarchar(30) NULL, [Director] nvarchar(30) NULL, [Contact] nvarchar(30) NULL,

[Cast] nvarchar(30) NULL, [Source] nvarchar(50) NULL, [ProjectTypeDesc] nvarchar(50) NULL,

[ProjectType2Desc] nvarchar(50) NULL, [GenreDesc] nvarchar(50) NULL, [Network] nvarchar(50) NULL,

[ProductionStatusDesc] nvarchar(50) NULL, [ClosedProject] bit NULL, [ClosedDate] datetime NULL,

[MusicSupervisor] nvarchar(255) NULL, [Location] nvarchar(255) NULL, [ProdStartDate] nvarchar(50) NULL,

[WrapShootDate] nvarchar(50) NULL, [SpottingDate] nvarchar(50) NULL, [RecordingDate] nvarchar(50) NULL,

[MixingDate] nvarchar(50) NULL, [DeliveryDate] nvarchar(50) NULL, [ReleaseDate] nvarchar(50) NULL,

[FinalDub] nvarchar(50) NULL, [FilmTVBudget] nvarchar(50) NULL, [MusicBudget] nvarchar(50) NULL,

[Synopsis] nvarchar(255) NULL, [ProjectStatus] nvarchar(255) NULL, [NonClientFinalOut] nvarchar(255) NULL

);

BEGIN

SET @.sORDERBY = '';

SET @.sWHERE = '';

SET @.sSQL = '';

INSERT INTO #rAG_ProjectStatusPre (CoProdCompany, ProductionCompanyDesc, AGProjectKey, ProjectTitle,

ProdOfficeAddr1, ProdOfficeAddr2, ProdOfficeAddr3, ProdOfficeCity, ProdOfficeState, ProdOfficeZip, ProdOfficeFax, ProdOfficePhone,

ClientKey, [FirstName], [LastName], AgentKey, AgentName, Producer, ExecProducer, Director, Contact, [Cast], Source,

ProjectTypeDesc, ProjectType2Desc, GenreDesc, Network, ProductionStatusDesc, ClosedProject, ClosedDate, MusicSupervisor,

Location, ProdStartDate, WrapShootDate, SpottingDate, RecordingDate, MixingDate, DeliveryDate,

ReleaseDate, FinalDub, FilmTVBudget, MusicBudget, Synopsis, ProjectStatus, NonClientFinalOut)

SELECT tProductionCompanies_1.ProductionCompanyDesc AS CoProdCompany,

tProductionCompanies.ProductionCompanyDesc, tAGProjects.AGProjectKey,

tAGProjects.ProjectTitle, tAGProjects.ProdOfficeAddr1, tAGProjects.ProdOfficeAddr2,

tAGProjects.ProdOfficeAddr3, tAGProjects.ProdOfficeCity, tAGProjects.ProdOfficeState,

tAGProjects.ProdOfficeZip, tAGProjects.ProdOfficeFax, tAGProjects.ProdOfficePhone,

tClients.ClientNumber AS [ClientKey], tClients.FirstName AS [FirstName],

tClients.LastName AS [LastName], tAgents.AgentKey, tAgents.AgentName,

[tMailList].[FirstName] + ' ' + [tMailList].[LastName] AS Producer,

[tMailList_1].[FirstName] + ' ' + [tMailList_1].[LastName] AS ExecProducer,

[tMailList_2].[FirstName] + ' ' + [tMailList_2].[LastName] AS Director,

[tMailList_3].[FirstName] + ' ' + [tMailList_3].[LastName] AS Contact,

[tMailList_4].[FirstName] + ' ' + [tMailList_4].[LastName] AS [Cast],

tProjectSources.Source, tProjectTypes.ProjectTypeDesc, tProjectType2s.ProjectType2Desc,

tGenre.GenreDesc, tAGProjects.Network, tProductionStatus.ProductionStatusDesc, tAGProjects.ClosedProject,

tAGProjects.ClosedDate, tAGProjects.MusicSupervisor, tAGProjects.Location, tAGProjects.ProdStartDate,

tAGProjects.WrapShootDate, tAGProjects.SpottingDate, tAGProjects.RecordingDate,

tAGProjects.MixingDate, tAGProjects.DeliveryDate, tAGProjects.ReleaseDate, tAGProjects.FinalDub,

tAGProjects.FilmTVBudget, tAGProjects.MusicBudget, tAGProjects.Synopsis,

tAGProjects.ProjectStatus, tAGProjects.NonClientFinalOut

FROM (tMailList RIGHT JOIN (tClients RIGHT JOIN ((((((((((((((tAGProjects LEFT JOIN tProductionCompanies ON

tAGProjects.ProductionCompanyKey = tProductionCompanies.ProductionCompanyKey) LEFT JOIN

tProductionCompanies AS tProductionCompanies_1 ON tAGProjects.CoProductionCompanyKey =

tProductionCompanies_1.ProductionCompanyKey) LEFT JOIN tProjectTypes ON tAGProjects.ProjectTypeKey =

tProjectTypes.ProjectTypeKey) LEFT JOIN tProjectType2s ON tAGProjects.Type2Key = tProjectType2s.ProjectType2Key)

LEFT JOIN tGenre ON tAGProjects.GenreKey = tGenre.GenreKey) LEFT JOIN tProductionStatus ON

tAGProjects.ProductionStatusKey = tProductionStatus.ProductionStatusKey) LEFT JOIN

(tProjectAgents LEFT JOIN tAgents ON tProjectAgents.AgentKey = tAgents.AgentKey) ON

tAGProjects.AGProjectKey = tProjectAgents.AGProjectKey) LEFT JOIN (tProjectCast LEFT JOIN

tMailList AS tMailList_4 ON tProjectCast.ContactKey = tMailList_4.MailListKey) ON

tAGProjects.AGProjectKey = tProjectCast.AGProjectKey) LEFT JOIN tProjectClients ON

tAGProjects.AGProjectKey = tProjectClients.AGProjectKey) LEFT JOIN (tProjectContacts LEFT JOIN

tMailList AS tMailList_3 ON tProjectContacts.ContactKey = tMailList_3.MailListKey) ON

tAGProjects.AGProjectKey = tProjectContacts.AGProjectKey) LEFT JOIN (tProjectDirectors LEFT JOIN

tMailList AS tMailList_2 ON tProjectDirectors.ContactKey = tMailList_2.MailListKey) ON

tAGProjects.AGProjectKey = tProjectDirectors.AGProjectKey) LEFT JOIN (tProjectExecProducers LEFT JOIN

tMailList AS tMailList_1 ON tProjectExecProducers.ContactKey = tMailList_1.MailListKey) ON

tAGProjects.AGProjectKey = tProjectExecProducers.AGProjectKey) LEFT JOIN tProjectProducers ON

tAGProjects.AGProjectKey = tProjectProducers.AGProjectKey) LEFT JOIN tProjectSources ON

tAGProjects.AGProjectKey = tProjectSources.AGProjectKey) ON tClients.ClientNumber = tProjectClients.ClientKey) ON tMailList.MailListKey = tProjectProducers.ContactKey)

WHERE tAGProjects.ClosedProject = COALESCE(@.OpenClosed, tAGProjects.ClosedProject)

AND @.AGProjectKey IS NULL OR ISNULL(tAGProjects.AGProjectKey, 0) IN (SELECT Value FROM dbo.fn_Split(@.AGProjectKey,','))

AND tAGProjects.ProjectTitle LIKE COALESCE(@.ProjectTitle, tAGProjects.ProjectTitle)

AND ISNULL(tClients.ClientNumber, 0) IN(CASE WHEN @.ClientKey IS NULL THEN ISNULL(tClients.ClientNumber, 0) ELSE @.ClientKey END)

END

BEGIN

SELECT DISTINCT AGProjectKey, ProjectTitle, FirstName, ClientKey, LastName, ProdOfficeAddr1, ProdOfficeAddr2, ProdOfficeAddr3, ProdOfficeCity, ProdOfficeState, ProdOfficeZip, ProdOfficeFax, ProdOfficePhone,

Network, ClosedProject, ClosedDate, MusicSupervisor, Location, ProdStartDate, WrapShootDate, SpottingDate, RecordingDate, MixingDate, DeliveryDate,

ReleaseDate, FinalDub, FilmTVBudget, MusicBudget, Synopsis, ProjectStatus, NonClientFinalOut, ProjectTypeDesc, ProjectType2Desc,

GenreDesc, ProductionCompanyDesc, CoProdCompany, ProductionStatusDesc

FROM #rAG_ProjectStatusPre

END

DROP TABLE #rAG_ProjectStatusPre

END

|||

Try:

case

when (@.Param is null) or (table.fieldname is null) then 1

else case when exists(select * from dbo.fn_split(@.Param, ',') as t where t.value = table.fieldname) then 1 else 0 end

end = 1

Not sure it will give good performance.

Here is a very good article about this theme.

Dynamic Search Conditions in T-SQL

http://www.sommarskog.se/dyn-search.html

AMB

|||

Okay, thanks for the follow-up information.

The previous poster's code will work, but in my opinion it makes the code rather confusing for future developers. I would code it to dynamically build the where clause.

As the table get larger performance will almost certainly become an issue due to the large number of joins. Make sure that you at least index the larger tables on the join columns and if possible create a 'covered index'. If you are unaware what that is you can learn more on books-online or try googling it.

You could code the where clause something like this...

declare @.where varchar(1000)

set @.where='where '

if @.AGProjectKey is not null

begin

set @.where=@.where + @.AGProject + ' and '

end

if @.ClientKey is not null

begin

set @.where=@.where + @.ClientKey + ' and '

end

Good luck. Let me know if you have any more questions.

Help with Nested Case Statements

Hi,

I am trying to write a query which is something like this :

CASE WHEN CASE WHEN crp.Title_Code = crp1.Title_Code
THEN ar.Description + '|' + ar1.Description
WHEN crp.Title_Code = crp2.Title_Code
THEN ar.Description + '|' + ar2.Description
WHEN crp.Title_Code = crp3.Title_Code
THEN ar.Description + '|' + ar3.Description
ELSE ar.Description END

WHEN CASE WHEN crp1.Title_Code = crp2.Title_Code
THEN ar1.Description + '|' + ar2.Description
WHEN crp1.Title_Code = crp3.Title_Code
THEN ar1.Description + '|' + ar3.Description
ELSE ar1.Description END

WHEN CASE WHEN crp2.Title_Code = crp3.Title_Code
THEN ar2.Description + '|' + ar3.Description
ELSE ar2.Description END

END AS Reason_Code_Description

But this is obviously not correct because the WHEN statements do not have a THEN statement - but I dont have anything to do in THEN statement becuase the nested CASE statements take care of everything. Can anybody please help me modify the query so that it works?

Thanks !!

It is not clear what you are attempting to accomplish. Perhaps if you were to present the entire concept, and the entire query, we might be better able to help you.|||

SELECT CASE crp.Title_Code WHEN crp1.Title_Code
THEN ar.Description + '|' + ar1.Description,
WHEN crp2.Title_Code
THEN ar.Description + '|' + ar2.Description,
WHEN crp3.Title_Code
THEN ar.Description + '|' + ar3.Description
ELSE ar.Description END
UNION
SELECT CASE crp1.Title_Code WHEN crp2.Title_Code
THEN ar1.Description + '|' + ar2.Description ,
WHEN crp1.Title_Code = crp3.Title_Code
THEN ar1.Description + '|' + ar3.Description
ELSE ar1.Description END
UNION
SELECT CASE crp2.Title_Code WHEN crp3.Title_Code
THEN ar2.Description + '|' + ar3.Description
ELSE ar2.Description END

Of course you need to incorporate your JOINS.

Just my twist on it,

Adamus

|||

Agreed. Can you at least make it clear if you are trying to get one value back, or three? Each of the WHEN clauses needs a boolean expression to determin if it is used. So if you want one value, it should be something like

CASE WHEN <boolean condition>
THEN CASE WHEN crp.Title_Code = crp1.Title_Code
THEN ar.Description + '|' + ar1.Description
WHEN crp.Title_Code = crp2.Title_Code
THEN ar.Description + '|' + ar2.Description
WHEN crp.Title_Code = crp3.Title_Code
THEN ar.Description + '|' + ar3.Description
ELSE ar.Description END

WHEN <boolean condition>
THEN CASE WHEN crp1.Title_Code = crp2.Title_Code
THEN ar1.Description + '|' + ar2.Description
WHEN crp1.Title_Code = crp3.Title_Code
THEN ar1.Description + '|' + ar3.Description
ELSE ar1.Description END

WHEN <boolean condition>
THEN CASE WHEN crp2.Title_Code = crp3.Title_Code
THEN ar2.Description + '|' + ar3.Description
ELSE ar2.Description END

END AS Reason_Code_Description

|||

I think I need to give the complete description here..This is the whole query - I modified it to get it to working..But my query returns only one record whereas it should return 4 different records if Reason_Code_ID1,Reason_Code_ID2,Reason_Code_ID3,Reason_Code_ID4 have different values for look up field of Title_Code and should concatenate the description for ones that have same Title_Code value.

Select CASE WHEN ar1.Description is NOT NULL AND ar2.Description is not null AND ar3.Description is not null
THEN CASE WHEN crp.Title_Code = crp1.Title_Code
THEN ar.Description + '|' + ar1.Description
WHEN crp.Title_Code = crp2.Title_Code
THEN ar.Description + '|' + ar2.Description
WHEN crp.Title_Code = crp3.Title_Code
THEN ar.Description + '|' + ar3.Description
ELSE ar.Description END
WHEN ar2.Description is not null AND ar3.Description is not null
THEN CASE WHEN crp1.Title_Code = crp2.Title_Code
THEN ar1.Description + '|' + ar2.Description
WHEN crp1.Title_Code = crp3.Title_Code
THEN ar1.Description + '|' + ar3.Description
ELSE ar1.Description END
WHEN ar3.Description is not null THEN
CASE WHEN crp2.Title_Code = crp3.Title_Code
THEN ar2.Description + '|' + ar3.Description
ELSE ar2.Description END
END AS Reason_Code_Description,
crp.CRP_Score_Reason_Code_ID,
crp.Title_Code
From ADF_CRP_Score s (nolock)
LEFT OUTER JOIN CRP_Score_Reason_Code crp
LEFT OUTER JOIN CCR..ADF_Reference_Mapping arm (nolock)
JOIN CCR..ADF_Reference ar (nolock)
ON arm.Lookup_ID = ar.Lookup_ID AND ar.Language = 'F'
ON arm.Bureau_Code_ID = '1'
AND arm.Segment_Field = 'CRP_Score_Reason_Code_ID'
AND ar.Code = crp.CRP_Score_Reason_Code_ID
AND arm.Segment = 'CRP'
ON (crp.CRP_Score_Reason_Code_ID = s.CRP_Score_Reason_Code_ID1)
LEFT OUTER JOIN CRP_Score_Reason_Code crp1
LEFT OUTER JOIN CCR..ADF_Reference_Mapping arm1 (nolock)
JOIN CCR..ADF_Reference ar1 (nolock)
ON arm1.Lookup_ID = ar1.Lookup_ID AND ar1.Language = 'F'
ON arm1.Bureau_Code_ID = '1'
AND arm1.Segment_Field = 'CRP_Score_Reason_Code_ID'
AND ar1.Code = crp1.CRP_Score_Reason_Code_ID
AND arm1.Segment = 'CRP'
ON (crp1.CRP_Score_Reason_Code_ID = s.CRP_Score_Reason_Code_ID2)
LEFT OUTER JOIN CRP_Score_Reason_Code crp2
LEFT OUTER JOIN CCR..ADF_Reference_Mapping arm2 (nolock)
JOIN CCR..ADF_Reference ar2 (nolock)
ON arm2.Lookup_ID = ar2.Lookup_ID AND ar2.Language = 'F'
ON arm2.Bureau_Code_ID = '1'
AND arm2.Segment_Field = 'CRP_Score_Reason_Code_ID'
AND ar2.Code = crp2.CRP_Score_Reason_Code_ID
AND arm2.Segment = 'CRP'
ON (crp2.CRP_Score_Reason_Code_ID = s.CRP_Score_Reason_Code_ID3)
LEFT OUTER JOIN CRP_Score_Reason_Code crp3
LEFT OUTER JOIN CCR..ADF_Reference_Mapping arm3 (nolock)
JOIN CCR..ADF_Reference ar3 (nolock)
ON arm3.Lookup_ID = ar3.Lookup_ID AND ar3.Language = 'F'
ON arm3.Bureau_Code_ID = '1'
AND arm3.Segment_Field = 'CRP_Score_Reason_Code_ID'
AND ar3.Code = crp3.CRP_Score_Reason_Code_ID
AND arm3.Segment = 'CRP'
ON (crp3.CRP_Score_Reason_Code_ID = s.CRP_Score_Reason_Code_ID4)

Please help.

Thanks!

|||

That is quite a SQL statement :) Can you give us some sample DDL and INSERT statements to make this more clear (and more simple) and sample results (expecting 2 rows, not 4 :)

Thanks

|||

There must be a more difficult way to code this. :)

You might be able to filter this query down in the WHERE clause instead of having a multitude of JOINS and SELECT CASE

What exactly is the goal of this query?

Adamus

|||

SELECT rc.Reason_Code_Description FROM
ADF_CRP_Score s (nolock)
LEFT OUTER JOIN CRP_Score_Reason_Code rc ON
(rc.CRP_Score_Reason_Code_ID = s.CRP_Score_Reason_Code_ID1)
OR (rc.CRP_Score_Reason_Code_ID = s.CRP_Score_Reason_Code_ID2)
OR (rc.CRP_Score_Reason_Code_ID = s.CRP_Score_Reason_Code_ID3)
OR (rc.CRP_Score_Reason_Code_ID = s.CRP_Score_Reason_Code_ID4)

This was the initail query that returnd following results :

CBSSD|Number of purchases in the previous 12 months.||||D|
CBSSD|Total monthly payments.||||E|
CBSSD|Total high credit .||||K|
CBSSD|Number of sales.||||L|

I had to modify the query so that if Title_code (D,E,K,L) in above example are same , like below

CBSSD|Number of purchases in the previous 12 months.||||D|
CBSSD| Age of oldest retail account.||||D|
CBSSD|Total high credit .||||K|
CBSSD|Number of sales.||||L|

then the data segments should be concatenated into one to give this result:

CBSSD|Number of purchases in the previous 12 months.| Age of oldest retail account||||D|
CBSSD|Total high credit .||||K|
CBSSD|Number of sales.||||L|

What my modified query returns is :

CBSSD|Number of purchases in the previous 12 months.| Age of oldest retail account||||D|

And what I would like to return is :

CBSSD|Number of purchases in the previous 12 months.| Age of oldest retail account||||D|
CBSSD|Total high credit .|Total balance||||K|
CBSSD|Number of sales.||||L|

I know its a bit confusing and complex and that is why I am here ..I am totally confused...:)

Please help.

Thanks!

|||

Although the below example isn't a complete solution to your problem [as I don't have either the table definitions or the patience to unpick your CASE statements :) ], it should help to start you off thinking of solutions along similar lines.

The example requires SQL Server 2005.

Chris

DECLARE @.MyTable TABLE ([Desc] VARCHAR(100) NULL, [Code] CHAR(1))
INSERT INTO @.MyTable
SELECT 'Number of purchases in the previous 12 months.', 'D' UNION ALL
SELECT 'Age of oldest retail account', 'D' UNION ALL
SELECT NULL, 'D' UNION ALL
SELECT NULL, 'D' UNION ALL
SELECT 'Total high credit.', 'K' UNION ALL
SELECT NULL, 'K' UNION ALL
SELECT 'Total balance', 'K' UNION ALL
SELECT NULL,'K' UNION ALL
SELECT 'Number of sales.', 'L' UNION ALL
SELECT NULL, 'L' UNION ALL
SELECT NULL, 'L' UNION ALL
SELECT NULL, 'L'

DECLARE @.MyCodeTable TABLE ([Code] CHAR(1))
INSERT INTO @.MyCodeTable
SELECT 'D' UNION
SELECT 'K' UNION
SELECT 'L'

SELECT 'CBSSD|'
+ REPLACE(
REPLACE(
(SELECT REPLACE(ISNULL(mt.[Desc], '') + '|', ' ', '~') AS [data()]
FROM @.MyTable mt
WHERE mt.[Code] = mct.[Code]
ORDER BY 1 DESC
FOR XML PATH('')), ' ', '')
, '~', ' ')
+ mct.[Code] + '|' AS [String]
FROM @.MyCodeTable mct
/*Output
CBSSD|Number of purchases in the previous 12 months.|Age of oldest retail account|||D|
CBSSD|Total high credit.|Total balance|||K|
CBSSD|Number of sales.||||L|
*/