Ms access SQL to Native SQL conversion

[hengsin]

hi, just want to make sure is it my conversion is correct regarding the MS Access SQL and "Native" SQL conversion regarding the join:

Here is the SQl from MS Access

FROM (ASIA.PROD_ORDER_DETAIL_ASIA INNER JOIN ASIA.PART_ASIA ON (ASIA.PROD_ORDER_DETAIL_ASIA.BUSINESS_UNIT_ID =

ASIA_PART_ASIA.BUSINESS_UNIT_ID) AND (ASIA.PROD_ORDER_DETAIL_ASIA.PART_NUM = ASIA.PART_ASIA.PART_NUM)) INNER JOIN

CORP.BUSINESS_UNITS ON ASIA.PROD_ORDER_DETAIL_ASIA.BUSINESS_UNIT_ID = CORP.BUSINESS_UNITS.BUSINESS_UNIT_ID

and i converted into Native SQL as the following:

FROM ASIA.PROD_ORDER_DETAIL ASIA P, CORP.BUSINESS_UNITS C, ASIA.PART_ASIA A
WHERE (P.BUSINESS_UNIT_ID = A.BUSINESS_UNIT_ID AND P.PART_NUM = A.PART_NUM) AND
P.BUSINESS_UNIT_ID = C.BUSINESS_UNIT_ID

Hopefully another pair of eyes will giving me whether something is wrong.....

 

[Norris68]

ANSI/ISO SQL uses INNER/LEFT/RIGHT/OUTER JOINs. Microsoft uses commas and specifies the join fields in the WHERE clause with bizarre *=, =*, *=* syntax. I prefer ANSI/ISO as it seems more explicit to me and doesn't confuse the join fields with the selection criteria, plus works on non-MS databases too.

It looks like your problems stem from table qualification where you specify the database, owner & table but inconsistently (underscore instead of dot); and then in the second example an error in table aliasing.

I would specify it as follows (using aliasing for clarity):
...
FROM ASIA.PROD_ORDER_DETAIL_ASIA PODA
INNER JOIN ASIA.PART_ASIA PA
ON PODA.BUSINESS_UNIT_ID = PA.BUSINESS_UNIT_ID
AND PODA.PART_NUM = PA.PART_NUM
INNER JOIN CORP.BUSINESS_UNITS BU
ON PODA.BUSINESS_UNIT_ID = BU.BUSINESS_UNIT_ID