Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Convert IIF Access SQL to Oracle SQL...

748206Jan 21 2010 — edited Jan 21 2010
I 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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 18 2010
Added on Jan 21 2010
2 comments
6,651 views