Ansi SQL to JET SQL

[Jim]

This is a stab in the dark here, but we have recently
started using ODBC in Access to link a number of tables
together which are held elsewhere in our company.
We use an oracle query builder to extract data on some of
these tables, and I am now trying to rebuild these queries
onto Access without any success whatsoever. Does anyone
know of a program which converts SQL into JET SQL? For
your information here is the code I am trying to convert:

SELECT DISTINCT OPS$COLLS.ACCOUNTS.AC_NUM,
OPS$COLLS.ACCOUNTS.FULL_FUND_AC_NUM,
OPS$COLLS.ACCOUNTS.AC_QALFR, OPS$COLLS.CASES.STRATEGY,
OPS$COLLS.CASES.NEXTACTIVITY,
OPS$COLLS.CUSTOMERS.HOME_TELNUM,
OPS$COLLS.CUSTOMERS.WORK_TELNUM,
OPS$COLLS.CUSTOMERS.OTH_TELNUM,
OPS$COLLS.ACCOUNT_CUSTOMER_MAPPINGS.ARS_AMT,
OPS$COLLS.CASES.ID, OPS$COLLS.ACCOUNTS.NML_INSTM
FROM OPS$COLLS.ACCOUNT_CUSTOMER_MAPPINGS,
OPS$COLLS.ACCOUNTS, OPS$COLLS.CASES, OPS$COLLS.CUSTOMERS,
OPS$COLLS.PROM_REPAYT_DETAILS,
OPS$COLLS.THIRD_PARTY_ROLE_ASSIGNMENTS,
OPS$COLLS.CUSTOMER_REPRESENTATIONS,
OPS$COLLS.MANUAL_ADMIN_TASKS
WHERE (OPS$COLLS.ACCOUNTS.PROD_TYPE_CD='L'
AND
OPS$COLLS.ACCOUNT_CUSTOMER_MAPPINGS.MAIN_AC_HOLDR_IND='Y'
AND OPS$COLLS.ACCOUNT_CUSTOMER_MAPPINGS.ARS_STA_IDFIR='OO'
AND (OPS$COLLS.CUSTOMERS.FXD_PYMT_STA NOT IN
('IO', 'OO', 'EX')
OR OPS$COLLS.CUSTOMERS.FXD_PYMT_STA IS NULL)
AND OPS$COLLS.ACCOUNTS.STP_CD IS NULL
AND NOT EXISTS(SELECT 1
FROM OPS$COLLS.PROM_REPAYT_DETAILS
WHERE
(OPS$COLLS.ACCOUNTS.AC_NUM=OPS$COLLS.PROM_REPAYT_DETAILS.AC
_NUM
AND OPS$COLLS.PROM_REPAYT_DETAILS.PROM_RPYMT_STA_CD IN
('P', 'IO', 'OO', 'EX')))
AND OPS$COLLS.CUSTOMERS.DCSD_IND NOT IN ('Y')
AND OPS$COLLS.CUSTOMERS.BKRPCY_IND NOT IN ('Y')
AND OPS$COLLS.CUSTOMERS.DISPT_IND NOT IN ('Y')
AND OPS$COLLS.CUSTOMERS.STF_1_IND NOT IN ('Y')
AND (OPS$COLLS.CUSTOMERS.DCSD_IND_ORID IS NULL
OR OPS$COLLS.CUSTOMERS.DCSD_IND_ORID NOT IN ('Y'))
AND (OPS$COLLS.ACCOUNT_CUSTOMER_MAPPINGS.PYMT_ORGNTR NOT
IN ('D', 'I')
OR OPS$COLLS.ACCOUNT_CUSTOMER_MAPPINGS.PYMT_ORGNTR IS
NULL)
AND OPS$COLLS.CUSTOMERS.MISC_FLG_1 IS NULL
AND (OPS$COLLS.THIRD_PARTY_ROLE_ASSIGNMENTS.ROLE_CD NOT
IN ('FEE_MAA', 'NOFEE_MAA')
OR OPS$COLLS.THIRD_PARTY_ROLE_ASSIGNMENTS.ROLE_CD IS NULL)
AND (OPS$COLLS.MANUAL_ADMIN_TASKS.MAN_ADMIN_Q_IDFIR IS
NULL
OR OPS$COLLS.MANUAL_ADMIN_TASKS.MAN_ADMIN_Q_IDFIR NOT IN
(33, 89, 91, 94, 97, 99, 239, 252))
AND OPS$COLLS.CASES.STRATEGY NOT IN
('CMH001', 'CON001', 'CMR001', 'CHS001', 'ADJ001', 'ROW001'
, 'RFS001', 'RSS001',
'RFA001', 'FRS001', 'RFR001', 'NMS001', 'TMP001', 'LOS001')
AND ((OPS$COLLS.CUSTOMERS.CAIS_IND='Y'
AND OPS$COLLS.CASES.STRATEGY NOT IN ('NAC001'))
OR OPS$COLLS.CUSTOMERS.CAIS_IND='N')
AND OPS$COLLS.ACCOUNTS.NML_INSTM>0
AND OPS$COLLS.ACCOUNTS.TFR_RSN_CD IS NULL)
AND
((OPS$COLLS.ACCOUNTS.AC_QALFR=OPS$COLLS.ACCOUNT_CUSTOMER_MA
PPINGS.AC_QALFR
AND
OPS$COLLS.ACCOUNTS.AC_NUM=OPS$COLLS.ACCOUNT_CUSTOMER_MAPPIN
GS.AC_NUM)
AND
(OPS$COLLS.ACCOUNT_CUSTOMER_MAPPINGS.PERS_ID=OPS$COLLS.CUST
OMERS.PERS_ID)
AND
(OPS$COLLS.ACCOUNT_CUSTOMER_MAPPINGS.PERS_ID=OPS$COLLS.CASE
S.CUSTID)
AND
(OPS$COLLS.ACCOUNTS.AC_NUM=OPS$COLLS.PROM_REPAYT_DETAILS.AC
_NUM(+))
AND
(OPS$COLLS.CUSTOMER_REPRESENTATIONS.THRD_PTY_ROLE_ASGMT_ID=
OPS$COLLS.THIRD_PARTY_ROLE_ASSIGNMENTS.THRD_PTY_ROLE_ASGMT_
ID(+))
AND
(OPS$COLLS.ACCOUNT_CUSTOMER_MAPPINGS.PERS_ID=OPS$COLLS.CUST
OMER_REPRESENTATIONS.PERS_ID(+))
AND
(OPS$COLLS.CASES.ID=OPS$COLLS.MANUAL_ADMIN_TASKS.CASEID
(+)))
GROUP BY OPS$COLLS.CASES.STRATEGY,
OPS$COLLS.CASES.NEXTACTIVITY,
OPS$COLLS.ACCOUNTS.AC_NUM,
OPS$COLLS.ACCOUNTS.FULL_FUND_AC_NUM,
OPS$COLLS.ACCOUNTS.AC_QALFR,
OPS$COLLS.CUSTOMERS.HOME_TELNUM,
OPS$COLLS.CUSTOMERS.WORK_TELNUM,
OPS$COLLS.CUSTOMERS.OTH_TELNUM,
OPS$COLLS.ACCOUNT_CUSTOMER_MAPPINGS.ARS_AMT,
OPS$COLLS.CASES.ID, OPS$COLLS.ACCOUNTS.NML_INSTM

 

[Brian Camire]

Have you considered using a pass-through query instead?

With a pass-through query, the host database (Oracle in your case) executes
the SQL instead of Access, so you could use your existing queries as they
are. Pass-through queries are not-updatable in Access, though, in case
that's a consideration. If it is, you might consider creating a view in
Oracle, linking to it in Access, and defining a pseudo-index in Access that
identifies a unique key in the underlying table.