• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 152
  • Last Modified:

Error around If Clause

I have a query that is throwing a comma error around my if clause. What am I missing??

SELECT p.ID as PatientID,
p.MedicalNumber as MRN,
p.LastName,
p.FirstName,
p.Status,
p.Age,
p.Sex as Gender,
p.AdmitDate,
p.DischargeDate,
p.AdmittingDiagnosis as Diagnosis,
loc.Abbreviation as CurrentLocation,
loc.PatientUnit as CurrentUnit
FROM Patient p
LEFT JOIN Location loc on loc.ID= Iif(p.HomeLocation is null,p.Destination,p.HomeLocation)
ORDER BY p.LastName,p.FirstName
0
NickMalloy
Asked:
NickMalloy
1 Solution
 
SQL_SERVER_DBACommented:
well for starters a typical iif in tsql looks like

case when ... then ... else ... end
0
 
weareitCommented:
LEFT JOIN Location loc on loc.ID= Iif(p.HomeLocation is null,p.Destination,p.HomeLocation)

Is it supposed to be Iif or If, or is this a typo???

-saige-
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
SELECT p.ID as PatientID,
p.MedicalNumber as MRN,
p.LastName,
p.FirstName,
p.Status,
p.Age,
p.Sex as Gender,
p.AdmitDate,
p.DischargeDate,
p.AdmittingDiagnosis as Diagnosis,
loc.Abbreviation as CurrentLocation,
loc.PatientUnit as CurrentUnit
FROM Patient p
LEFT JOIN Location loc on loc.ID= CASE WHEN  p.HomeLocation IS NULL THEN  p.Destination ELSE p.HomeLocation END
ORDER BY p.LastName,p.FirstName
0
 
LowfatspreadCommented:
SELECT p.ID as PatientID,
p.MedicalNumber as MRN,
p.LastName,
p.FirstName,
p.Status,
p.Age,
p.Sex as Gender,
p.AdmitDate,
p.DischargeDate,
p.AdmittingDiagnosis as Diagnosis,
loc.Abbreviation as CurrentLocation,
loc.PatientUnit as CurrentUnit
FROM Patient p
LEFT JOIN Location loc
on loc.ID= coalesce(p.homelocation,p.Destination)
ORDER BY p.LastName,p.FirstName
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now