Conversion from oracle SQL to access syntax query

Question

I'm trying to convert sql oracle select to access select query. Could you explain what does this "(+)" mean? It's like join? How can I use "not in" in this case?

Select
lc.LOC, lc.HOME_ZONE, hz.AREA_TYPE, ld.QTY, sk.SKU

From
LOC lc, HOMEZONE hz, CONTAINER cn, LOAD ld, SKU sk

Where
lc.HOME_ZONE = hz.HOME_ZONE
and lc.LOC = cn.LOC (+)
and cn.CONT_NO = ld.CONT_NO (+)
and ld.PROD_NO = sk.PROD_NO (+)
and lc.LOC_COLOR_ZONE in ('80', '90', '95', '100')
and hz.AREA_TYPE in ('211', '231')
and hz.SECTION_NO not in ('990000');

Aleksej · Accepted Answer · 2016-07-15 10:51:51Z

3

The (+) is an Oracle shortcut for the outer join; for example the following two queries are equivalent:

SELECT *
  FROM tableA, tableB
 WHERE tableA.idA = tableB.idB(+)           

SELECT *
  FROM tableA
       LEFT OUTER JOIN tableB
         ON (tableA.idA = tableB.idB)

Your query could be rewritten in ANSI SQL like this:

SELECT lc.LOC,
       lc.HOME_ZONE,
       hz.AREA_TYPE,
       ld.QTY,
       sk.SKU
  FROM LOC lc
       INNER JOIN HOMEZONE hz USING (HOME_ZONE)
       LEFT OUTER JOIN CONTAINER cn USING (LOC)
       LEFT OUTER JOIN LOAD ld USING (CONT_NO)
       LEFT OUTER JOIN SKU sk USING (PROD_NO)
 WHERE     lc.LOC_COLOR_ZONE IN ('80', '90', '95', '100')
       AND hz.AREA_TYPE IN ('211', '231')
       AND hz.SECTION_NO NOT IN ('990000');

answered Jul 15, 2016 at 10:51

Aleksej

23.1k6 gold badges38 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Conversion from oracle SQL to access syntax query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related