Oracle to MS-Access SQL:

Question

I'm trying to replicate a SQL code from Oracle to my Access to automate the creation of some reports. So far I have been successful but I am having trouble with one:

This is Oracle SQL:

SELECT DISTINCT o587265.PROJECT_NUMBER AS E587273,
    o587265.PROJECT_NAME AS E587274,
    o587265.PROJECT_STATUS_CODE AS E587275,
    o587265.PROJECT_MANAGER AS E587276
FROM (
    SELECT p.segment1 project_number,
        p.NAME project_name,
        p.project_status_code,
        (
            SELECT a.FULL_NAME
            FROM apps.per_all_people_f a,
                apps.pa_project_players_v pm
            WHERE a.person_id = pm.person_id
                AND pm.project_ID = p.project_ID
                AND pm.ROLE = 'Project Manager'
                AND a.current_employee_flag = 'Y'
                AND a.person_type_id = 1
                AND SYSDATE BETWEEN a.effective_start_date
                    AND a.effective_end_date
                AND SYSDATE BETWEEN pm.start_date_active(+)
                    AND NVL(pm.end_date_active, SYSDATE)
            ) project_manager
    FROM apps.pa_projects_all p
    WHERE p.project_status_code != 'CLOSED'
        AND p.template_flag = 'N'
        AND p.org_ID = 5003
    ) o587265
    ORDER BY o587265.PROJECT_NUMBER ASC,
    o587265.PROJECT_NAME ASC,
    o587265.PROJECT_MANAGER ASC;

And this is my current MS-Access version:

SELECT DISTINCT o587265.PROJECT_NUMBER AS E587273,
    o587265.PROJECT_NAME AS E587274,
    o587265.PROJECT_STATUS_CODE AS E587275,
    o587265.PROJECT_MANAGER AS E587276
FROM (
    SELECT p.segment1 AS project_number,
        p.NAME AS project_name,
        p.project_status_code,
        (
            SELECT a.FULL_NAME
            FROM ((apps_per_all_people_f AS a INNER JOIN apps_pa_project_players_v AS pm ON a.person_id = pm.person_id
                ) INNER JOIN apps_pa_projects_all AS p ON pm.project_ID = p.project_ID
            )
            WHERE pm.ROLE = 'Project Manager'
                AND a.current_employee_flag = 'Y'
                AND a.person_type_id = 1
                AND DATE () BETWEEN a.effective_start_date
                AND a.effective_end_date
                AND DATE () BETWEEN pm.start_date_active
                AND IIf(IsNull(pm.end_date_active), DATE (), pm.end_date_active)
            ) AS PROJECT_MANAGER
    FROM apps_pa_projects_all AS p
    WHERE p.project_status_code <> 'CLOSED'
        AND p.template_flag = 'N'
        AND p.org_ID = 5003
    ) AS o587265
ORDER BY o587265.PROJECT_NUMBER ASC,
    o587265.PROJECT_NAME ASC,
    o587265.PROJECT_MANAGER ASC;

The first one runs directly on Oracle 11g (SQL Developer) and works. The MS-Access one seems to be returning more than one row in the subquery. I exhausted my knowledge but I can't seem to replicate it.

What am I doing wrong?

Also, what is this (+) operator in the Oracle SQL code?

I've known this operator as a OUTER (LEFT/RIGHT) JOIN but I never saw it being used like that and that is definitely not a JOIN.

Can someone help me?

I'm pretty sure the (+) really is a left outer join operator, even in this case. But it doesn't look like it worked, because it wasn't applied consistently. I think you can remove it from the Oracle query, and it won't change anything. — sstan
You should try rewriting the Oracle query to use explicit joins. At the very least, you would be left having an understanding of what the query is actually doing. — Tim Biegeleisen
sstan: (+) I though so too but in this case it isn't. This query was extract from Oracle Discovery interface and it is used as it is to generate a report so it does work. I did remove it and it didn't change anything. — user6412203
tim: already did and already worked in Oracle. I think I'm using something wrong in MS-ACCESS. — user6412203

Parfait Parfait · Accepted Answer · 2016-06-02T03:24:47

Possibly in the translation of Oracle's implicit join for MS Access explicit join, you added an additional INNER JOIN referencing the apps_pa_projects_all table in the derived table's subquery for PROJECT_MANAGER column, replacing the alias p. However, this subquery correlated to the outer FROM clause's derived table, apps_pa_projects_all with original alias p:

Consider removing the second INNER JOIN and returning the correlated WHERE clause condition:

ORIGINAL

...
    (
        SELECT a.FULL_NAME
        FROM ((apps_per_all_people_f AS a 
        INNER JOIN apps_pa_project_players_v AS pm 
                ON a.person_id = pm.person_id) 
        INNER JOIN apps_pa_projects_all AS p 
                ON pm.project_ID = p.project_ID)
        WHERE pm.ROLE = 'Project Manager'
         AND a.current_employee_flag = 'Y'
         ...

CHANGE

...
    (
        SELECT a.FULL_NAME
        FROM apps_per_all_people_f AS a 
        INNER JOIN apps_pa_project_players_v AS pm 
                ON a.person_id = pm.person_id
        WHERE pm.project_ID = p.project_ID
         AND pm.ROLE = 'Project Manager'
         AND a.current_employee_flag = 'Y'
         ...

Oracle to MS-Access SQL:

1 Answers