[RESOLVED] Translating Oracle SQL to MS Access SQL

[caffeine]

I never learned SQL too well. A friend (who only uses Oracle) wrote some SQL for me, and I'm trying to translate it for MS Access. The SQL is:

SELECT a.id,
case when b.ufa is null then c.ufa
else b.ufa end as ufa
FROM metrotmp a,
(select mt.ID,mc.GEOID,mu.UFA
from metrotmp mt, metrocbsa mc, metroufa mu
where mt.ZIP = mc.ZIP
and mc.GEOID=mu.GEOID) b,
(select mt1.id, mu1.UFA
from metrotmp mt1, metroufa mu1
where mt1.STATE = mu1.GEO ) c
where a.id = b.id (+)
and a.id = c.id

I think there are three problems with this:

1. Access has no "case when"
2. Access has no "is null"
3. Access has no "(+)".

For #1, I think nested iif's will work.
For #2, I think nz() will work.

What exactly is (+)? How would I translate (+) into MS Access SQL?

Thanks!

[si_the_geek]

1. You are on the right track, IIf is the equivalent of Case - but in this situation (because the check is for Null) I think NZ would do the job on its own.

2. Yes it does.

3. I think that is a Left Outer Join (and I think on the condition it follows). To use it you will need to use 'modern' SQL syntax rather than specifying join details in the Where clause, eg:

SELECT ...

FROM (metrotmp a
LEFT OUTER JOIN (select mt.ID,mc.GEOID,mu.UFA
                 from metrotmp mt, metrocbsa mc, metroufa mu
                 where mt.ZIP = mc.ZIP
                 and mc.GEOID=mu.GEOID) b
  ON a.id = b.id)
INNER JOIN (select mt1.id, mu1.UFA
            from metrotmp mt1, metroufa mu1
            where mt1.STATE = mu1.GEO ) c
  ON a.id = c.id

I could be wrong as I haven't used Oracle in years, but this should at least be on the right track.

[caffeine]

Unfortunately, nz() isn't part of the Jet engine (I'm using Excel/VBA to access Access using the Jet engine). I think IIF() is required here. :-(

A *big* thanks for the outer join thing. I never would've gotten that in a million years....