| Access to Oracle sql conversion [message #78293] |
Mon, 11 February 2002 10:47  |
Phil
Messages: 32
Registered: March 2001
|
Member |
|
|
Hi
I have been working on a routine that converts access Queries built in access to Oracle queries.
If anyone is interested please send me an email explaining why you are interested and i will get back to you
Here are some simple examples
access 97 sql
SELECT MULT001.RISK_KEY, MULT001.POLKEY, MULT001.LOCATION
FROM ((((MULT001 LEFT JOIN MULT004 ON (MULT001.POLKEY = MULT004.POLKEY) AND (MULT001.RISK_KEY = MULT004.RISK_KEY)) LEFT JOIN MULT002 ON (MULT001.POLKEY = MULT002.POLKEY) AND (MULT001.RISK_KEY = MULT002.RISK_KEY)) LEFT JOIN MULT003 ON (MULT001.POLKEY = MULT003.POLKEY) AND (MULT001.RISK_KEY = MULT003.RISK_KEY)) LEFT JOIN MULT005 ON (MULT001.POLKEY = MULT005.POLKEY) AND (MULT001.RISK_KEY = MULT005.RISK_KEY)) LEFT JOIN MULT006 ON (MULT001.POLKEY = MULT006.POLKEY) AND (MULT001.RISK_KEY = MULT006.RISK_KEY);
Converted sql
SELECT
t1.RISK_KEY,
t1.POLKEY,
t1.LOCATION
FROM
MULT001 t1,MULT004 t2,MULT002 t3,MULT003 t4,MULT005 t5,MULT006 t6
WHERE t1.RISK_KEY = t2.RISK_KEY(+)
AND t1.POLKEY = t2.POLKEY(+)
AND t1.RISK_KEY = t3.RISK_KEY(+)
AND t1.POLKEY = t3.POLKEY(+)
AND t1.RISK_KEY = t4.RISK_KEY(+)
AND t1.POLKEY = t4.POLKEY(+)
AND t1.RISK_KEY = t5.RISK_KEY(+)
AND t1.POLKEY = t5.POLKEY(+)
AND t1.RISK_KEY = t6.RISK_KEY(+)
AND t1.POLKEY = t6.POLKEY(+)
Example 2
----------
Access sql
SELECT MULT001.RISK_KEY, MULT001_1.RISK, MULT001_1.RISK_TO
FROM MULT001 LEFT JOIN MULT001 AS MULT001_1 ON (MULT001.LOCATION = MULT001_1.LOCATION) AND (MULT001.POLKEY = MULT001_1.POLKEY) AND (MULT001.RISK_KEY = MULT001_1.RISK_KEY)
WHERE (((MULT001_1.RISK_KEY)="1200000001069759001001ATR") AND ((MULT001.RISK)="001") AND ((MULT001.RISK_TO)=#2/10/2002#))
GROUP BY MULT001.RISK_KEY, MULT001_1.RISK, MULT001_1.RISK_TO, MULT001_1.RISK_KEY
HAVING (((MULT001.RISK_KEY)="1200000001069759001001ATR") AND ((MULT001_1.RISK)="001") AND ((MULT001_1.RISK_TO)=#2/10/2002#))
ORDER BY MULT001.RISK_KEY, MULT001_1.RISK_KEY DESC;
Converted sql
SELECT
t1.RISK_KEY,
t2.RISK,
t2.RISK_TO
FROM
MULT001 t1,MULT001 t2
WHERE t1.RISK_KEY = t2.RISK_KEY(+)
AND t1.POLKEY = t2.POLKEY(+)
AND t1.LOCATION = t2.LOCATION(+)
AND(((t2.RISK_KEY)='1200000001069759001001ATR')
AND ((t1.RISK)='001')
AND ((t1.RISK_TO)='10-Feb-2002'))
GROUP BY t1.RISK_KEY , t2.RISK , t2.RISK_TO , t2.RISK_KEY
HAVING (((t1.RISK_KEY)='1200000001069759001001ATR')
AND ((t2.RISK)='001')
AND ((t2.RISK_TO)='10-Feb-2002'))
ORDER BY t1.RISK_KEY , t2.RISK_KEY DESC
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
