Convert IIF Access SQL to Oracle SQL...
748206Jan 21 2010 — edited Jan 21 2010I am in the process of converting a MS Access application to VB.Net with Oracle database behind. However I am having a problem converting an MS Access Query that contains IIF statements into an Oracle SQL command. Below is the MS Access SQL code:
SELECT PDTABLE_132.UNIQUE_CLASH_ID, CISClashStatusTbl.Approval_Type AS Clash_Status, CISClashTypeTbl.Clash_Type, *IIf([TEMP_ACTION_DISCIPLINE] Is Null,"UNALOCATED",[TEMP_ACTION_DISCIPLINE]) AS ACTION_DISCIPLINE*, *IIf([CISDisciplineTbl].[Discipline] Is Null,[PD_2910A_PDTABLE_111].[DISCIPLINE_NAME],[CISDisciplineTbl].[Discipline]) AS TEMP_ACTION_DISCIPLINE*
FROM ((((((((((((PDTABLE_132 INNER JOIN PD_2910A_PDTABLE_113 ON PDTABLE_132.MODEL_INDEX_NO_A = PD_2910A_PDTABLE_113.MODEL_INDEX_NO) INNER JOIN PD_2910A_PDTABLE_113 AS PD_2910A_PDTABLE_113_1 ON PDTABLE_132.MODEL_INDEX_NO_B = PD_2910A_PDTABLE_113_1.MODEL_INDEX_NO) INNER JOIN PDTABLE_119 ON PD_2910A_PDTABLE_113.MODEL_INDEX_NO = PDTABLE_119.MODEL_INDEX_NO) INNER JOIN PDTABLE_119 AS PDTABLE_119_1 ON PD_2910A_PDTABLE_113_1.MODEL_INDEX_NO = PDTABLE_119_1.MODEL_INDEX_NO) LEFT JOIN CISClashStatusTbl ON PDTABLE_132.IF_APPROVAL_STATUS = CISClashStatusTbl.Approval_Code) LEFT JOIN CISClashTypeTbl ON PDTABLE_132.RECENT_CLASH_TYPE = CISClashTypeTbl.Clash_Code) INNER JOIN PD_2910A_PDTABLE_112 ON PD_2910A_PDTABLE_113.AREA_INDEX_NO = PD_2910A_PDTABLE_112.AREA_INDEX_NO) INNER JOIN PD_2910A_PDTABLE_112 AS PD_2910A_PDTABLE_112_1 ON PD_2910A_PDTABLE_113_1.AREA_INDEX_NO = PD_2910A_PDTABLE_112_1.AREA_INDEX_NO) LEFT JOIN CISOwnerTbl ON PDTABLE_132.UNIQUE_CLASH_ID = CISOwnerTbl.UNIQUE_CLASH_ID) LEFT JOIN CISDisciplineTbl ON CISOwnerTbl.Discipline_Code = CISDisciplineTbl.Discipline_Code) LEFT JOIN PD_2910A_PDTABLE_111 ON PDTABLE_132.ACTION_DISCIPLINE = PD_2910A_PDTABLE_111.DISCIPLINE_INDX_NO) LEFT JOIN PD_2910A_PDTABLE_111 AS PD_2910A_PDTABLE_111_1 ON PDTABLE_132.DISCIPLINE_INDEX_A = PD_2910A_PDTABLE_111_1.DISCIPLINE_INDX_NO) LEFT JOIN PD_2910A_PDTABLE_111 AS PD_2910A_PDTABLE_111_2 ON PDTABLE_132.DISCIPLINE_INDEX_B = PD_2910A_PDTABLE_111_2.DISCIPLINE_INDX_NO
ORDER BY PDTABLE_132.UNIQUE_CLASH_ID;
Any help would really be appreshiated.
Thanks in advance
Simon
Edited by: user8275408 on 21-Jan-2010 07:25