dynamic WHERE clause criteria is null, causes a problem 1

[glgcag]

I have a query with many tables and criteria and I'm trying to create a dynamic WHERE clause using COALESCE and Case statements. So far it's working well except in this case:

My main table is "Projects" and it is linked to the "Clients" table via a many-to-many table called "ProjectClients". In my criteria I have a parameter called @ClientKey (which I've set to NULL if it's not passed into the query) in which I pass the client key (or keys) that the user has selected in their criteria. The problem I'm running into is that if the user doesn't select a client as criteria, my WHERE clause is not including the projects in which there is no record in the ProjectClients table for that project (basically, no client(s) have been assigned to that project yet), so that project is not included in the result set. But I want to see all projects whether there's a client or not based on the absence of the client criteria. Here's the WHERE clause I have constructed so far:

WHERE tAGProjects.ClosedProject = COALESCE(@OpenClosed, tAGProjects.ClosedProject) 
		 AND tAGProjects.AGProjectKey = COALESCE(@AGProjectKey, tAGProjects.AGProjectKey)
		 AND tAGProjects.ProjectTitle LIKE COALESCE(@ProjectTitle, tAGProjects.ProjectTitle) 
		 AND tClients.ClientNumber IN (CASE WHEN @ClientKey IS NULL THEN tClients.ClientNumber ELSE @ClientKey END)

Any ideas what I'm doing wrong?

 

[rac2]

I dont see any condition in this WHERE clause that refers to the ProjectClients table. Since you mention that table I would guess that you have JOINed it or that it is the basic table in the query. By this I mean there is something like

SELECT * FROM ProjectClients pc 
JOIN tAGProjects ON ...

or

SELECT * FROM tAGProjects 
JOIN ProjectClients pc ON ...

in your query.


I know of a couple of ways to get "missing" rows into the results of a query. The LEFT JOIN and the UNION.
So

SELECT * FROM tAGProjects 
LEFT JOIN ProjectClients pc ON ...

if the join is the issue

or

SELECT * FROM tAGProjects 
WHERE things_not_involving_clients

UNION

SELECT * FROM ProjectClients pc JOIN tAGProjects ON ...
WHERE things_pertaining_to_clients

With a UNION the queries might be quite different except for the columns in the SELECT list which must match in number and datatype.

 

[glgcag]

rac2,

Thank you for your response! I have pasted the complete SELECT statement below to give you an idea of how long it is. You are correct about the table for project clients.

The idea of a different join in this query is quite daunting as you can see it's labyrinthine!

What I did do is create a temp table and I'm working on running the query below with the criteria that works, then running another where clause in a query against the temp table . . . not sure if it's the right way to go though.

		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 tAGProjects.AGProjectKey = COALESCE(@AGProjectKey, tAGProjects.AGProjectKey)
		 AND tAGProjects.ProjectTitle LIKE COALESCE(@ProjectTitle, tAGProjects.ProjectTitle)
		 AND tClients.ClientNumber IN (CASE WHEN @ClientKey IS NULL THEN tClients.ClientNumber ELSE @ClientKey END) 
		 
	END

 

[ESquared]

Use some aliases.

Oh, wait... you're using a GUI query designer. Never mind. Won't help you.

[COLOR=black #d0d0d0]My alarm clock causes time travel. When I hit snooze, no apparent time passes before the alarm sounds again, but the universe has in fact moved forward through time by ten minutes![/color]

 

[glgcag]

ESquared,

Actually, I did cut and paste an Access query into SSMS, that's why you're seeing all the parentheses. Since I'm converting this HUGE Access database, and I'm new to SQL Server, and I'm on a tight schedule (my company waited until the corruption issues are so bad, they HAD to deal with upsizing it) I'm trying to move this thing along as quickly as I can. So far, cutting and pasting into SSMS and modifying from there has worked well, but we'll see for how long . . .

I'm not sure what you mean when you say use aliases and why it won't work in a gui query designer . . .

 

[ESquared]

The GUI designer will mangle your query, even if it keeps the aliases properly. I can't even see the table names in that blob of a query, or scan the joins to see what they are. I also doubt that every single join should be an outer join. So I said "won't help you" because with the GUI you have much larger problems than just aliases.

You should stop using a GUI as SOON as you can. It's a sign of inexperience or inability. Fortunately, the former is correctable.

[COLOR=black #d0d0d0]My alarm clock causes time travel. When I hit snooze, no apparent time passes before the alarm sounds again, but the universe has in fact moved forward through time by ten minutes![/color]

 

[glgcag]

ESquared,

Thank you for the pointer- I agree, I sort of feel like the bastard child of programmers, for two reasons- first, I started in Access and second, I'm therefore used to GUI.

At the beginning of this project I started building the queries from scratch but it took so long that I eventually had to abandon it. I may take the time on this one though.

Incidentally, what do you build your SQL queries in? Just a command line?

 

[PWise]

try this

WHERE tAGProjects.ClosedProject = COALESCE(@OpenClosed, tAGProjects.ClosedProject) 
         AND tAGProjects.AGProjectKey = COALESCE(@AGProjectKey, tAGProjects.AGProjectKey)
         AND tAGProjects.ProjectTitle LIKE COALESCE(@ProjectTitle, tAGProjects.ProjectTitle) 
         AND isnull(tClients.ClientNumber,0) = (CASE WHEN @ClientKey IS NULL THEN isnull(tClients.ClientNumber,0) ELSE @ClientKey END)

 

[ESquared]

I use Query Analyzer (SQL 2000)
and Management Studio (SQL 2005)

[COLOR=black #d0d0d0]My alarm clock causes time travel. When I hit snooze, no apparent time passes before the alarm sounds again, but the universe has in fact moved forward through time by ten minutes![/color]

 

[glgcag]

pwise,

It worked! Thank you very much! Let me see if I can identify what you are doing here:

If @ClientKey Is Null make it = 0
If tClients.ClientNumber Is Null make it = 0

If 0=0, then include the record. (Hence, Null @ClientKey means the user didn't include this parameter, therefore we should ignore the parameter and 0=0 essentially does that.)

But, if @ClientKey Is Not Null, then only include records where tClients.ClientNumber = @ClientKey.

PERFECT! (Sorry for thinking out loud, but it helps for me to wrangle the logic to learn all of this.)

I replaced the = with "IN", so I can pass a comma separated string into @ClientKey for the user to be able to select multiple clients to report on.

ESquared,

I too use Management Studio, though all the queries I'm converting, I copy from Access, but I do edit them only in text, not the SSMS gui feature. It really helps to understand the queries better to work with them in text. I'm definitely improving doing it this way . . .

Thanks again guys!




What is happening here, to me, seems to be that if I have no @ClientKey parameter, then we want records with tClients.ClientNumber IS NULL to be included. So, you are making Null values 0, and if 0=0, it includes all records

 

[fsteeman]

Hi! I have had a similar problem, although a less complex query.

In case a certain parameter is null, just select all rows, also those where the column the parameter is compared with is null. First I have tried:

SELECT RecipeID, RecipeName, RecipeText, RecipeCategory, RecipeDietCategory
FROM Recipes
WHERE (RecipeName LIKE '%' + @freetext + '%') 
AND (RecipeCategory = ISNULL(@category, RecipeCategory))

But that does not work, logically, because in case the parameter is null AND the column value is null, the rows are not selected, because null is never equal anything including itself.

But I just know there must be a simple solution to this, so I tried the promising suggestion above:

SELECT RecipeID, RecipeName, RecipeText, RecipeCategory, RecipeDietCategory
FROM Recipes
WHERE (RecipeName LIKE '%' + @freetext + '%') 
AND (ISNULL(RecipeCategory, 0) = (CASE WHEN @recipecategory IS NULL THEN ISNULL(RecipeCategory, 0) ELSE @recipecategory END))

I am working with dataset designer in Visual Studio and when I attempt to execute the query I get a "Wrong parameter" message. What could be wrong?

Fedor Steeman
Virksomheds-IT http://www.virksomheds-it.dk/
Denmark