masteringcode
Programmer
I am trying to convert this code with little luck due to my lack of knowledge with Oracle. This is somewhat complex. Any suggestions.
SELECT COMPONENT.CMPNT_NAME AS TAG_NAME, DRAWING.DWG_NAME AS Spec_No, DRAWING_1.DWG_NAME AS PID_NO, COMPONENT.CMPNT_SERV AS SERVICE, COMPONENT_LOCATION.CMPNT_LOC_NAME AS Location, [COMPONENT HANDLE].CMPNT_HANDLE_DESC AS STATUS_DESCRIPTION, IIf([CMPNT_FUNC_TYPE_DESC] Not Like "*T/C2*",[CMPNT_FUNC_TYPE_DESC],"ELEMENT TEMPERATURE T/C") AS INSTR_TYPE, LOOP.LOOP_NUM, LOOP.LOOP_NAME, COMPONENT_SYS_IO_TYPE.CMPNT_SYS_IO_TYPE_NAME AS [I/O_TYPE], COMPONENT.REQ_NO, IIf([SPEC_CMPNT_PO_NO] Like "*---*","----",[SPEC_CMPNT_PO_NO]) AS PO_NO, [UDF COMPONENT].UDF_C20 AS [JB Wiring Dwg], [COMPONENT MFR].CMPNT_MFR_NAME AS MANUFACTURER, [COMPONENT MODEL].CMPNT_MOD_NAME AS MODEL_NO, [UDF COMPONENT].UDF_C01 AS LOCATION_Dwg, [UDF COMPONENT].UDF_C56 AS MOUNTING_DETAIL, [UDF COMPONENT].UDF_C10 AS [Installation Detail], [UDF COMPONENT].UDF_C52 AS [ELECT-STEAM_TRACING], [UDF COMPONENT].UDF_C51 AS [Ext Pwr], [UDF COMPONENT].UDF_C53 AS ELECT_DETAIL, [UDF COMPONENT].UDF_C54 AS [System Number]
FROM DRAWING AS DRAWING_1 INNER JOIN (((((((COMPONENT_LOCATION INNER JOIN ((DRAWING INNER JOIN [SPEC SHEET DATA] ON DRAWING.DWG_ID = [SPEC SHEET DATA].DWG_ID) INNER JOIN ((([SPEC FORM] INNER JOIN (COMPONENT_FUNCTION_TYPE INNER JOIN ([COMPONENT HANDLE] INNER JOIN COMPONENT ON [COMPONENT HANDLE].CMPNT_HANDLE_ID = COMPONENT.CMPNT_HANDLE_ID) ON COMPONENT_FUNCTION_TYPE.CMPNT_FUNC_TYPE_ID = COMPONENT.CMPNT_FUNC_TYPE_ID) ON [SPEC FORM].SPEC_FORM_ID = COMPONENT_FUNCTION_TYPE.SPEC_FORM_ID) INNER JOIN [COMPONENT MODEL] ON COMPONENT.CMPNT_MOD_ID = [COMPONENT MODEL].CMPNT_MOD_ID) INNER JOIN [COMPONENT MFR] ON COMPONENT.CMPNT_MFR_ID = [COMPONENT MFR].CMPNT_MFR_ID) ON [SPEC SHEET DATA].CMPNT_ID = COMPONENT.CMPNT_ID) ON COMPONENT_LOCATION.CMPNT_LOC_ID = COMPONENT.CMPNT_LOC_ID) INNER JOIN [UDF COMPONENT] ON COMPONENT.CMPNT_ID = [UDF COMPONENT].CMPNT_ID) INNER JOIN COMPONENT_SYS_IO_TYPE ON COMPONENT.CMPNT_SYS_IO_TYPE_ID = COMPONENT_SYS_IO_TYPE.CMPNT_SYS_IO_TYPE_ID) LEFT JOIN [DCS TAG] ON COMPONENT.CMPNT_NAME = [DCS TAG].CMPNT_NAME) LEFT JOIN CONTROL_VALVE ON COMPONENT.CMPNT_ID = CONTROL_VALVE.CMPNT_ID) INNER JOIN LOOP ON COMPONENT.LOOP_ID = LOOP.LOOP_ID) LEFT JOIN FLOW ON COMPONENT.CMPNT_ID = FLOW.CMPNT_ID) ON DRAWING_1.DWG_ID = COMPONENT.DWG_ID
WHERE (((COMPONENT.CMPNT_NAME) Is Not Null And (COMPONENT.CMPNT_NAME) Not Like " %") AND (([COMPONENT HANDLE].CMPNT_HANDLE_NAME) Not Like "*DELETE*"))
ORDER BY COMPONENT.CMPNT_NAME
SELECT COMPONENT.CMPNT_NAME AS TAG_NAME, DRAWING.DWG_NAME AS Spec_No, DRAWING_1.DWG_NAME AS PID_NO, COMPONENT.CMPNT_SERV AS SERVICE, COMPONENT_LOCATION.CMPNT_LOC_NAME AS Location, [COMPONENT HANDLE].CMPNT_HANDLE_DESC AS STATUS_DESCRIPTION, IIf([CMPNT_FUNC_TYPE_DESC] Not Like "*T/C2*",[CMPNT_FUNC_TYPE_DESC],"ELEMENT TEMPERATURE T/C") AS INSTR_TYPE, LOOP.LOOP_NUM, LOOP.LOOP_NAME, COMPONENT_SYS_IO_TYPE.CMPNT_SYS_IO_TYPE_NAME AS [I/O_TYPE], COMPONENT.REQ_NO, IIf([SPEC_CMPNT_PO_NO] Like "*---*","----",[SPEC_CMPNT_PO_NO]) AS PO_NO, [UDF COMPONENT].UDF_C20 AS [JB Wiring Dwg], [COMPONENT MFR].CMPNT_MFR_NAME AS MANUFACTURER, [COMPONENT MODEL].CMPNT_MOD_NAME AS MODEL_NO, [UDF COMPONENT].UDF_C01 AS LOCATION_Dwg, [UDF COMPONENT].UDF_C56 AS MOUNTING_DETAIL, [UDF COMPONENT].UDF_C10 AS [Installation Detail], [UDF COMPONENT].UDF_C52 AS [ELECT-STEAM_TRACING], [UDF COMPONENT].UDF_C51 AS [Ext Pwr], [UDF COMPONENT].UDF_C53 AS ELECT_DETAIL, [UDF COMPONENT].UDF_C54 AS [System Number]
FROM DRAWING AS DRAWING_1 INNER JOIN (((((((COMPONENT_LOCATION INNER JOIN ((DRAWING INNER JOIN [SPEC SHEET DATA] ON DRAWING.DWG_ID = [SPEC SHEET DATA].DWG_ID) INNER JOIN ((([SPEC FORM] INNER JOIN (COMPONENT_FUNCTION_TYPE INNER JOIN ([COMPONENT HANDLE] INNER JOIN COMPONENT ON [COMPONENT HANDLE].CMPNT_HANDLE_ID = COMPONENT.CMPNT_HANDLE_ID) ON COMPONENT_FUNCTION_TYPE.CMPNT_FUNC_TYPE_ID = COMPONENT.CMPNT_FUNC_TYPE_ID) ON [SPEC FORM].SPEC_FORM_ID = COMPONENT_FUNCTION_TYPE.SPEC_FORM_ID) INNER JOIN [COMPONENT MODEL] ON COMPONENT.CMPNT_MOD_ID = [COMPONENT MODEL].CMPNT_MOD_ID) INNER JOIN [COMPONENT MFR] ON COMPONENT.CMPNT_MFR_ID = [COMPONENT MFR].CMPNT_MFR_ID) ON [SPEC SHEET DATA].CMPNT_ID = COMPONENT.CMPNT_ID) ON COMPONENT_LOCATION.CMPNT_LOC_ID = COMPONENT.CMPNT_LOC_ID) INNER JOIN [UDF COMPONENT] ON COMPONENT.CMPNT_ID = [UDF COMPONENT].CMPNT_ID) INNER JOIN COMPONENT_SYS_IO_TYPE ON COMPONENT.CMPNT_SYS_IO_TYPE_ID = COMPONENT_SYS_IO_TYPE.CMPNT_SYS_IO_TYPE_ID) LEFT JOIN [DCS TAG] ON COMPONENT.CMPNT_NAME = [DCS TAG].CMPNT_NAME) LEFT JOIN CONTROL_VALVE ON COMPONENT.CMPNT_ID = CONTROL_VALVE.CMPNT_ID) INNER JOIN LOOP ON COMPONENT.LOOP_ID = LOOP.LOOP_ID) LEFT JOIN FLOW ON COMPONENT.CMPNT_ID = FLOW.CMPNT_ID) ON DRAWING_1.DWG_ID = COMPONENT.DWG_ID
WHERE (((COMPONENT.CMPNT_NAME) Is Not Null And (COMPONENT.CMPNT_NAME) Not Like " %") AND (([COMPONENT HANDLE].CMPNT_HANDLE_NAME) Not Like "*DELETE*"))
ORDER BY COMPONENT.CMPNT_NAME
