coverstion from plsql to sql in MS Access

[dj59]

I am trying to convert an sql statement from an oracle based platform to use it in MS Access (sql server platform). I'm getting a syntax error (of course), but I can't see where the problem is.

The sql is below. Any help would be appreciated.

select a.south_index_id, c.cnty_cd, a.southwest_name_source_cd, sp.south_ssn, sp.estimated_birth_dt,
[a.LAST_NAME] & ', ' & [a.FIRST_NAME) PERSONNAME], 
a.BIRTH_DT, a.GENDER_CD, sp.ms_pmi, cnty_person_id
FROM SW_alias_name AS a, SW_south_person AS sp, SW_county_pid AS c
WHERE EXISTS ((((select * from SW_alias_name a2  where ucase(a2.last_name) = ucase(a.last_name)
   and ucase(a2.first_name) = ucase(a.first_name)
   and a2.birth_dt = a.birth_dt
   and a2.south_index_id <> a.south_index_id
   and a.southwest_name_source_cd = '11'
   and a2.southwest_name_source_cd <> '11'
   and c.cnty_cd = '11'
   and c.south_index_id = a.south_index_id
   and c.south_index_id = sp.south_index_id
   and sp.deceased_dt is null
   and sp.ms_pmi is null))<>False))
ORDER BY personname;

ERROR: "syntax error in query expression 'EXISTS' ......

Also, in the origional sql the 'where exists...' was written as:

WHERE exists (select 'X' from ....)

 

[SQL_Hell]

select a.south_index_id, c.cnty_cd, a.southwest_name_source_cd, sp.south_ssn, sp.estimated_birth_dt,
[a.LAST_NAME] & ', ' & [a.FIRST_NAME) PERSONNAME], 
a.BIRTH_DT, a.GENDER_CD, sp.ms_pmi, cnty_person_id
FROM SW_alias_name AS a, SW_south_person AS sp, SW_county_pid AS c
WHERE EXISTS (select * from SW_alias_name a2  where ucase(a2.last_name) = ucase(a.last_name)
   and ucase(a2.first_name) = ucase(a.first_name)
   and a2.birth_dt = a.birth_dt
   and a2.south_index_id <> a.south_index_id
   and a.southwest_name_source_cd = '11'
   and a2.southwest_name_source_cd <> '11'
   and c.cnty_cd = '11'
   and c.south_index_id = a.south_index_id
   and c.south_index_id = sp.south_index_id
   and sp.deceased_dt is null
   and sp.ms_pmi is null)
ORDER BY personname;

 

[jdraw]

You could try the code below (untested).
It would be helpful if you told us more about your table structures. You don't seem to be making use of key fields, but that could just be a naming thing.
I haven't used Oracle for a few years.

select a.south_index_id, c.cnty_cd, a.southwest_name_source_cd, sp.south_ssn, sp.estimated_birth_dt,
[a.LAST_NAME] & ', ' & [a.FIRST_NAME) PERSONNAME], 
a.BIRTH_DT, a.GENDER_CD, sp.ms_pmi, cnty_person_id
FROM SW_alias_name AS a, SW_south_person AS sp, SW_county_pid AS c
WHERE 
         ucase(a2.last_name) = ucase(a.last_name)
   and ucase(a2.first_name) = ucase(a.first_name)
   and a2.birth_dt = a.birth_dt
   and a2.south_index_id <> a.south_index_id
   and a.southwest_name_source_cd = '11'
   and a2.southwest_name_source_cd <> '11'
   and c.cnty_cd = '11'
   and c.south_index_id = a.south_index_id
   and c.south_index_id = sp.south_index_id
   and sp.deceased_dt is null
   and sp.ms_pmi is null;

 

[SQL_Hell]

Yeah I agree with JDraw, why so many join conditions?

 

[CJ_London]

Access isn't case sensitive so this line

and ucase(a2.first_name) = ucase(a.first_name)

can be written as

and a2.first_name = a.first_name

and I can't make sense of this

[a.LAST_NAME] & ', ' & [a.FIRST_NAME) PERSONNAME],

 

[SQL_Hell]

Please ignore my first post, I read the question as PSQL to T-SQL not PSQL to Access SQL

JDraw's answer looks more right, sorry.

 

[jdraw]

In my view of the original code there is no join in any of the tables, and no where clause per se. The Where clause was simply EXISTS which (to me) is just removing records from what seems to be a Cartesian product on the 3 tables.

 

[dj59]

Thanks!
It is working now except for one odd thing.
I had to do some edits to the following line and the query works fine, except it brings up at pop up box asking for the personname. I leave it blank and it runs okay, but do you have any ideas how to get rid of that box?

 [a]![LAST_NAME] & ', ' & [a]![FIRST_NAME] AS PERSONNAME,

I do believe there may be an easier way to do this using the design query in MS Access and I'll look at that too. I just wanted to see if the conversion could be done in sql mode.

 

[dj59]

ISSUE RESOLVED:
I just deleted the following line from the sql and put ascending order in the query design.

ORDER BY [a.LAST_NAME] & ', ' & [a.FIRST_NAME], personname;

Thank you so much!

 

[CJ_London]

but do you have any ideas how to get rid of that box

it's caused by this line

[a.LAST_NAME] & ', ' & [a.FIRST_NAME) PERSONNAME],