I'm converting a bunch of queries from 9i to 8i, but not very familiar with the 9i join syntax. I don't understand all these parentheses!! Can someone explain the logical order, and if you're bored, convert this statement to 8i syntax for me? Here it is:
SELECT RtuToComPath.RTU_NB, RtuToComPath.NAME, DeviceTypeLookup.DESCR AS DEVICE, ServiceTypeLookup.DESCR AS SERVICE, ConvergeCompath.NAME AS MODEL, ConvergeCompath.TYPE, RtuToComPath.STATUS, RtuToComPath.MODEL_COM_PATH_ID, C2000Rtu.TYPE AS C2000_Type, C2000Rtu.SERVICE AS C2000_Service, RtuToComPath.COM_PATH_ID, C2000RtuGr.GR_NAME, C2000RtuAcqList.LIST_NAME
FROM ServiceTypeLookup INNER JOIN (DeviceTypeLookup INNER JOIN ((C2000RtuGr RIGHT JOIN C2000RtuGrAss ON C2000RtuGr.GR_NB = C2000RtuGrAss.GR_NB) RIGHT JOIN ((C2000RtuAcqList RIGHT JOIN C2000RtuAcqListAss ON C2000RtuAcqList.LIST_NB = C2000RtuAcqListAss.LIST_NB) RIGHT JOIN ((RtuToComPath INNER JOIN C2000Rtu ON RtuToComPath.RTU_NB = C2000Rtu.RTU_NB) LEFT JOIN ConvergeCompath ON RtuToComPath.MODEL_COM_PATH_ID = ConvergeCompath.COM_PATH_ID) ON C2000RtuAcqListAss.RTU_NB = C2000Rtu.RTU_NB) ON C2000RtuGrAss.RTU_NB = C2000Rtu.RTU_NB) ON DeviceTypeLookup.TYPE = C2000Rtu.TYPE) ON ServiceTypeLookup.TYPE = C2000Rtu.SERVICE
But the code looks like it was generated by a program, judging by the number of unnecessary parentheses and the lack of any readable formatting. If so, maybe that program has an option to generate 8i syntax instead of using ANSI joins?
You're right... This was a code from Microsoft Access. Which, as far as I know, can't create a "normal" query without JOIN syntax to be 8i compatible. So it's up to me to make it look nice for 8i. Any Access pros out there? It seems when you use "right join" in access, it's equivolent to the (+) being on the LEFT??? Which is opposite of how Oracle treats RIGHT or LEFT JOIN. With Oracle, LEFT JOIN would be the same as the (+) being on the left.