Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1010
  • Last Modified:

MAX and IIF statements Access vs SQL

I have an Access query with the following clause:

Max(IIf([dbo_AbsDrgDiagnoses]![DiagnosisSeqID]=1,[dbo_AbsDrgDiagnoses]![Diagnosis],0)) AS PrinDiag

I have translated it to SQL as:

MAX(iif(dbo.AbsDrgDiagnoses.DiagnosisSeqID=1,dbo.AbsDrgDiagnoses.Diagnosis,0)) as PrinDiag

I am getting a syntax error that "error in list of function arguaments: "=" not recognized

Do you see what could be wrong?

Thanks

Glen
0
GPSPOW
Asked:
GPSPOW
1 Solution
 
mds-cosCommented:
According to MSDN for SQL 2012, what you have should work for the new IIF function.  But since it is not, give the CASE statement a try instead:

SELECT (CASE WHEN dbo.AbsDrgDiagnoses.DiagnosisSeqID = 1 THEN dbo.AbsDrgDiagnoses.Diagnosis ELSE 0) as PrinDiag
0
 
GPSPOWAuthor Commented:
Thanks I forgot about the case statement
0
 
SharathData EngineerCommented:
just for completeness, correcting the syntactial errors in mds-cos's query.
SELECT MAX(CASE WHEN dbo.AbsDrgDiagnoses.DiagnosisSeqID = 1 THEN dbo.AbsDrgDiagnoses.Diagnosis ELSE 0 END) as PrinDiag

Open in new window

0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

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