|
|||||||||
|
|||||||||
|
|||||||||
 |
|||||||||
| |
||
|
|
|
|
|||||||||
|
|||||||||
|
|||||||||
|
|||||||||
| |
||
|
|
|
| ||||||||||||||||||||||||||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
ASP Free Forums Sponsor:
|
|
|
|
#1
October 16th, 2009, 09:28 AM
|
|||
|
|||
|
SQL Server 2005 - Using IF and ELSE in SQL statement
Hi,
I'm trying to use an IF condition within a SQL statement, but it's not working. It's trying to merge two columns ('KnownAsName' and 'surname') into one column called Name, but if there is no entry in the KnownAsName column, then it merges 'forename' and 'surname' as Name. I tried this: Code:
IF [Staff.dbo.STAFF.KnownAsName] IS NOT NULL
BEGIN
SELECT DISTINCT
dbo.STAFF.FORENAME + ' ' + dbo.STAFF.SURNAME AS Name, Employment_1.EmpEnd, Employment_1.EmpStart, dbo.STAFF.Sex, dbo.STAFF.Manager,
dbo.STAFF.EmployeeNo, dbo.STAFF.Profile, dbo.STAFF.Qual1, dbo.STAFF.Qual2, dbo.STAFF.Qual3, dbo.STAFF.Qual4, dbo.STAFF.Qual5,
dbo.STAFF.Qual6, dbo.STAFF.Qual7, dbo.STAFF.Qual8, dbo.STAFF.Qual9, dbo.STAFF.Qual10, dbo.STAFF.SupressScores,
dbo.STAFF.LastScoreSheetPrinted, dbo.STAFF.UID, Employment_1.ID, Employment_1.Centre, Employment_1.Position, dbo.STAFF.Photograph,
(SELECT MIN(EmpStart) AS Expr1
FROM dbo.Employment
WHERE (dbo.STAFF.AppNo = AppNo)
GROUP BY AppNo) AS EarliestStartDate
FROM dbo.Employment AS Employment_1 LEFT OUTER JOIN
dbo.STAFF ON Employment_1.AppNo = dbo.STAFF.AppNo
WHERE (Employment_1.EmpEnd IS NULL) AND (Employment_1.EmpStart < GETDATE()) OR
(Employment_1.EmpEnd > GETDATE())
END
ELSE
BEGIN
SELECT DISTINCT
dbo.STAFF.FORENAME + ' ' + dbo.STAFF.SURNAME AS Name, Employment_1.EmpEnd, Employment_1.EmpStart, dbo.STAFF.Sex, dbo.STAFF.Manager,
dbo.STAFF.EmployeeNo, dbo.STAFF.Profile, dbo.STAFF.Qual1, dbo.STAFF.Qual2, dbo.STAFF.Qual3, dbo.STAFF.Qual4, dbo.STAFF.Qual5,
dbo.STAFF.Qual6, dbo.STAFF.Qual7, dbo.STAFF.Qual8, dbo.STAFF.Qual9, dbo.STAFF.Qual10, dbo.STAFF.SupressScores,
dbo.STAFF.LastScoreSheetPrinted, dbo.STAFF.UID, Employment_1.ID, Employment_1.Centre, Employment_1.Position, dbo.STAFF.Photograph,
(SELECT MIN(EmpStart) AS Expr1
FROM dbo.Employment
WHERE (dbo.STAFF.AppNo = AppNo)
GROUP BY AppNo) AS EarliestStartDate
FROM dbo.Employment AS Employment_1 LEFT OUTER JOIN
dbo.STAFF ON Employment_1.AppNo = dbo.STAFF.AppNo
WHERE (Employment_1.EmpEnd IS NULL) AND (Employment_1.EmpStart < GETDATE()) OR
(Employment_1.EmpEnd > GETDATE())
END
but it came back saying multi-part identifier staff.dbo.staff.KnownAsName could not be bound (line 1) Please can anyone help?
|
|
#2
October 16th, 2009, 02:13 PM
|
||||
|
||||
|
you don't need the if/then statement, you could use a CASE statement in a single query. But I noticed that both your queries are exactly alike. Am I missing something?
Try something like this: Code:
SELECT DISTINCT
CASE
WHEN s.KnownAsName IS NOT NULL THEN s.KnownAsName + ' ' + s.SURNAME
ELSE s.FORENAME + ' ' + s.SURNAME
END AS Name,
e.EmpEnd, e.EmpStart,
s.Sex, s.Manager, s.EmployeeNo, s.Profile, s.Qual1, s.Qual2, s.Qual3, s.Qual4, s.Qual5,
s.Qual6, s.Qual7, s.Qual8, s.Qual9, s.Qual10, s.SupressScores, s.LastScoreSheetPrinted, s.UID,
e.ID, e.Centre, e.Position,
s.Photograph,
(SELECT MIN(EmpStart) AS Expr1
FROM dbo.Employment e1
WHERE s.AppNo = e1.AppNo)
GROUP BY e1.AppNo) AS EarliestStartDate
FROM dbo.Employment AS e
LEFT OUTER JOIN dbo.STAFF s ON e.AppNo = s.AppNo
WHERE (e.EmpEnd IS NULL) AND (e.EmpStart < GETDATE())
OR (e.EmpEnd > GETDATE())
__________________
Come JOIN the party!!! Quote of the Month: Pretension: The downside of being better than everyone else is that people tend to assume you're pretentious. Questions to Ponder: You can be overwhelmed and underwhelmed, but why can't you be simply whelmed? iif([sarcasm]=true,iif([you have to ask]=true,"didn't work","ha ha ha"),"not sarcasm") copyright© 2008 sbenj69
|
|
#3
October 25th, 2009, 07:03 PM
|
|||
|
|||
|
A simple solution is to use ISNULL:
SELECT ISNULL(KnownAsName,Forename) + ' ' + surname If there is a possibility that forename can be null as well, you might want to use COELESCE instead: SELECT COELESCE(KnownAsName, ForeName, 'default') + ' ' + surname. That guarantees that something is printed for the first name.
|
|
#4
November 2nd, 2009, 04:46 AM
|
|||
|
|||
|
Quote:
Thanks, coalesce worked a treat, nice one.
|
|
| Viewing: ASP Free Forums > Database > Microsoft SQL Server > SQL Server 2005 - Using IF and ELSE in SQL statement |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
