SQL Newbie - Case question
-
I'm just moving over from access to sql and need some help with the iif statement from access that does not work in sql, but I understand that I need to use CASE.
I've created a query with a CASE.
SELECT *,
CASE
WHEN LEN(Known_as) = 0 THEN forename + ' ' + surname
ELSE Known_as + ' ' + surname
END AS Name
from Names
but I was wondering can you use more than one CASE in a query. What I would also like to do is also have a column that contains known_as if >0 else forename.
hope someone can help me.
-
You can use multiple CASE expressions in a query. Basically as much as you need. You can also net CASEs with other CASEs. I don't have a better example right now at hand, but nested CASEs might look something like this:
declare @theyear int
set @theyear = 2005
select
case
when @theyear % 4 <> 0
then 'Kein Schaltjahr'
else
case
when @theyear % 100 = 0
then
case
when @theyear % 400 = 0
then 'Schaltjahr'
else 'Kein Schaltjahr' end
else 'Schaltjahr' end
end as Schaltjahr_Ja_Nein
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
You can add as many CASEs as you want (as previous poster said)...the syntax will be something like this:
SELECT Known_as =
CASE
WHEN LEN(Known_as) = 0 THEN forename + ' ' + surname
WHEN LEN(Known_as) > 0 THEN forename
ELSE Known_as + ' ' + surname
END AS Name
from Names
**ASCII stupid question, get a stupid ANSI !!!** -
Michael,
take care with NULL values... LEN(Known_as) is NULL (i.e., not 0), if Known_as is NULL. To make sure the CASE works as you want it, first transform NULL values to empty string.
SELECT *,
CASE
WHEN LEN(ISNULL(Known_as,'')) = 0 THEN forename + ' ' + surname
ELSE Known_as + ' ' + surname
END AS FullName
from Names
One query can have several columns computed via CASE, and the CASE statement can have several conditions. These conditions are then evaluated in the order they are written, until the program encounters a condition that is TRUE (which terminates the CASE and subsequent conditions are not evaluated).
-
Here's a search sp that I made using a few cases
CREATE PROCEDURE [dbo].[SPNSearchClients] @FacturerA as varchar(10) = null--# Facturer à
, @Location as Tinyint = 0--Type recherche : au début du champ(1), n'importe où (3), match exact (0)
, @NoClient as varchar(10) = null--# client
, @Nom as varchar(45) = null--nom client
, @Ville as varchar(30) = null--ville client
, @NomFA as varchar(45) = null--nom facturer à
, @VilleFA as varchar(30) = null--ville facturer à
, @OrderBy as Varchar(15) = null--colonne choisie pour le tri
, @ALLCriterias as bit = 0--trouver tous les critères où un seul
WITH RECOMPILE
AS
SET NOCOUNT ON
DECLARE @StartWC as varchar(1)
DECLARE @EndWC as varchar(1)
DECLARE @bAnyParamIsSet as bit
SELECT
@FacturerA = NULLIF(@FacturerA, '')
, @NoClient = NULLIF(@NoClient, '')
, @Nom = NULLIF(@Nom, '')
, @Ville = NULLIF(@Ville, '')
, @NomFA = NULLIF(@NomFA, '')
, @VillefA = NULLIF(@VilleFA, '')
, @OrderBy = NULLIF(@OrderBy, '')
, @StartWC = CASE WHEN @Location & 2 = 2 THEN '%' ELSE '' END
, @EndWC = CASE WHEN @Location & 1 = 1 THEN '%' ELSE '' END
, @bAnyParamIsSet = CAST(LEN(ISNULL(@FacturerA, '') + ISNULL(@NoClient, '') + ISNULL(@Nom, '') + ISNULL(@Ville, '') + ISNULL(@NomFA, '') + ISNULL(@VilleFA, '')) AS BIT)
SELECT
C.[no client]AS [Numéro Client]
, C.Nom
, C.[Facturer a]AS [Factuer à]
, FA.NomAS [Nom Facturer à]
, C.Ville
, C.Téléphone
, C.Adresse
, ISNULL(C.[Code postal], C.[Zip code(usa)]) As [Code Postal]
, C.Fax
FROM dbo.Client C LEFT OUTER JOIN
dbo.Client FA ON C.[Facturer A] = FA.[no client]
WHERE
@bAnyParamIsSet = 1 AND--allows sql server not to do any work if no param is set
0 <
CASE WHEN @ALLCriterias = 0 THEN --return a line if any parameter is true
CASE
WHEN NOT NULLIF(@Ville, '') IS NULL AND C.Ville= @Ville THEN 1
WHEN NOT NULLIF(@VilleFA, '') IS NULL AND FA.Ville= @VilleFA THEN 1
WHEN NOT NULLIF(@NoClient, '') IS NULL AND C.[no client] LIKE @StartWC +@NoClient + @EndWCTHEN 1
WHEN NOT NULLIF(@FacturerA, '') IS NULL AND C.[Facturer a] LIKE @StartWC +@FacturerA + @EndWCTHEN 1
WHEN NOT NULLIF(@Nom, '') IS NULL AND C.Nom LIKE@StartWC +@Nom + @EndWCTHEN 1
WHEN NOT NULLIF(@NomFA, '') IS NULL AND FA.Nom LIKE@StartWC +@NomFA + @EndWCTHEN 1
ELSE 0
END
ELSE --@ALLCriterias = 1--return a line if all parameters are true
CASE WHEN
(@VilleIS NULL OR C.Ville = @Ville )
AND(@VilleFAIS NULL OR FA.Ville = @VilleFA )
AND (@NoClient IS NULL OR C.[no client] LIKE @StartWC +@NoClient + @EndWC)
AND (@FacturerA IS NULL OR C.[Facturer a] LIKE @StartWC +@FacturerA + @EndWC)
AND (@Nom IS NULL OR C.Nom LIKE@StartWC +@Nom + @EndWC)
AND (@NomFAIS NULL OR FA.Nom LIKE@StartWC +@NomFA+ @EndWC)
THEN 1
ELSE 0
END
END
ORDER BY
CASE WHEN @OrderBy = 'no client'THEN C.[no client] ELSE NULLEND
, CASE WHEN @OrderBy = 'nom'THEN C.nom ELSE NULLEND
, CASE WHEN @OrderBy = 'NomFA'THEN FA.nom ELSE NULLEND
, CASE WHEN @OrderBy = 'Facturer a' THEN C.[Facturer a] ELSE NULLEND
, CASE WHEN @OrderBy = 'Adresse' THEN C.Adresse ELSE NULLEND
, CASE WHEN @OrderBy = 'Ville' THEN C.Ville ELSE NULLEND
, CASE WHEN @OrderBy = 'Code Postal' THEN ISNULL(C.[Code postal], C.[Zip code(usa)]) ELSE NULLEND
, CASE WHEN @OrderBy = 'Téléphone' THEN C.Téléphone ELSE NULLEND
, CASE WHEN @OrderBy = 'Fax' THEN C.Fax ELSE NULLEND
, CASE WHEN @OrderBy IS NULL OR @OrderBy NOT IN ('no client', 'nom', 'NomFA', 'Facturer a',
'Adresse', 'Ville', 'Code Postal', 'Téléphone', 'Fax') THEN C.nom ELSE NULLEND
SET NOCOUNT OFF
GO
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply