Access Conversion to SQL

What is the correct SQL syntax for the SQL statement below

SELECT dbo_jobroute.jobid, dbo_jobroute.routeindex, Max(IIf(([routetypeid] In (1,2) And [pcpe]=""),1,0)) AS [Received Without], Max(IIf(([routetypeid] In (1,2) And [pcpe]<>""),1,0)) AS [Received With], Max(IIf(([routetypeid]=3 And [pcpe]=""),1,0)) AS [Sent Without], Max(IIf(([routetypeid]=3 And [pcpe]<>""),1,0)) AS [Sent With]
FROM dbo_jobroute INNER JOIN dbo_routing ON dbo_jobroute.routingid = dbo_routing.routingid
GROUP BY dbo_jobroute.jobid, dbo_jobroute.routeindex;

Thanks

Poly
polynominalAsked:
Who is Participating?
 
rafranciscoCommented:
SELECT dbo_jobroute.jobid, dbo_jobroute.routeindex,
       MAX(CASE WHEN [routetypeid] In (1,2) And [pcpe]=''  THEN 1 ELSE 0 END) AS [Received Without],
       MAX(CASE WHEN [routetypeid] In (1,2) And [pcpe]<>'' THEN 1 ELSE 0 END) AS [Received With],
       MAX(CASE WHEN [routetypeid]=3 And [pcpe]='' THEN 1 ELSE 0 END) AS [Sent Without],
       MAX(CASE WHEN [routetypeid]=3 And [pcpe]<>'' THEN 1 ELSE 0 END) AS [Sent With]
FROM dbo_jobroute INNER JOIN dbo_routing ON dbo_jobroute.routingid = dbo_routing.routingid
GROUP BY dbo_jobroute.jobid, dbo_jobroute.routeindex;
0
 
Steve BinkCommented:
That looks fine.  Is it not working?
0
 
Steve BinkCommented:
der...MSSQL, not Access.  I get it now...LOL
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.

All Courses

From novice to tech pro — start learning today.