Hallo !
Habe folgendes Problem aus einer alten MS-Access abfrage in Oracle8 zu konvertieren.
Diese Abfrage besteht fast nur aus JOINS, hier ist ein Teil des SQL – Statements.
Habe mich selber schon dran probiert bekomme aber eine zur große Ergebnismenge.,
als dass was die Originalabfrage eigentlich liefern sollte.
Hier MS-Access abfrage und danach mein Versuch die joins in Oracle zu konvertieren:
SELECT
ORION_BARCODE.BARCODE AS BoardID,
Last("") AS Field2,
Last("") AS Field3,
Last("") AS Field4,
ORION_FAULT.USER_FAULT_NAME AS DefectLabel,
ORION_COMP.COMP_NAME AS RefDes,
Last("") AS Field7,
Last("") AS Field8,
ORION_PIN_FAULT.PIN_NO AS Pin,
Last("") AS Field9,
Last("") AS Field10,
Last("") AS Field11,
Last("") AS Field12,
Last("") AS Field13,
Last("") AS Field14,
Last("") AS Field15,
Last("") AS Field16,
Last("") AS Field17,
Last("") AS Field18,
ORION_INSPECT_RESULT.REWORK_END AS [Date],
Last("") AS Field20,
Last("") AS Field21,
Last("") AS Field22,
Last("") AS Field23,
Last("") AS Field24,
Last("") AS Field25,
Last("") AS Field26,
Last("") AS Field27
FROM ORION_FAULT
INNER JOIN (((ORION_COMP_FAULT INNER JOIN ORION_COMP ON ORION_COMP_FAULT.COMP_ID = ORION_COMP.COMP_ID)
INNER JOIN (ORION_BARCODE INNER JOIN ORION_INSPECT_RESULT ON ORION_BARCODE.PCB_ID = ORION_INSPECT_RESULT.PCB_ID)
ON (ORION_COMP_FAULT.INSPECT_RESULT_ID = ORION_INSPECT_RESULT.INSPECT_RESULT_ID)
AND (ORION_COMP.PROG_ID = ORION_INSPECT_RESULT.PROG_ID))
LEFT JOIN ORION_PIN_FAULT ON ORION_COMP_FAULT.COMP_ID = ORION_PIN_FAULT.COMP_ID)
ON ORION_FAULT.FAULT_ID = ORION_COMP_FAULT.MACHINE_FAULT_ID
GROUP BY
ORION_BARCODE.BARCODE,
ORION_FAULT.USER_FAULT_NAME,
ORION_COMP.COMP_NAME,
ORION_PIN_FAULT.PIN_NO,
ORION_INSPECT_RESULT.REWORK_END,
ORION_INSPECT_RESULT.REWORK_RESULT,
ORION_COMP_FAULT.REWORK_FAULT_ID
HAVING (((ORION_BARCODE.BARCODE)=[BoardID])
AND ((ORION_INSPECT_RESULT.REWORK_RESULT)=„F“)
AND ((ORION_COMP_FAULT.REWORK_FAULT_ID)""))
ORDER BY ORION_INSPECT_RESULT.REWORK_END;
Und der von mir versuchte Oracle8 part, Scherpunkt liegt hier in den join abfragen da diese in einander Verschachtelt sind.
SELECT
BARCODE.BARCODE as BoardID ,
‚‘ AS Field2,
‚‘ AS Field3,
‚‘ AS Field4,
FAULT.USER_FAULT_NAME AS DefectLabel,
COMP.COMP_NAME AS RefDes,
‚‘ AS Field7,
‚‘ AS Field8,
PIN_FAULT.PIN_NO AS Pin,
‚‘ AS Field9,
‚‘ AS Field10,
‚‘ AS Field11,
‚‘ AS Field12,
‚‘ AS Field13,
‚‘ AS Field14,
‚‘ AS Field15,
‚‘ AS Field16,
‚‘ AS Field17,
‚‘ AS Field18,
INSPECT_RESULT.REWORK_END AS Datum,
‚‘ AS Field20,
‚‘ AS Field21,
‚‘ AS Field22,
‚‘ AS Field23,
‚‘ AS Field24,
‚‘ AS Field25,
‚‘ AS Field26,
‚‘ AS Field27
FROM
FAULT ,
COMP_FAULT,
COMP,
BARCODE ,
INSPECT_RESULT,
PIN_FAULT
where
FAULT.FAULT_ID = COMP_FAULT.MACHINE_FAULT_ID
and COMP_FAULT.INSPECT_RESULT_ID = INSPECT_RESULT.INSPECT_RESULT_ID
and COMP_FAULT.COMP_ID = COMP.COMP_ID
and COMP_FAULT.COMP_ID = PIN_FAULT.COMP_ID
and BARCODE.PCB_ID = INSPECT_RESULT.PCB_ID
and COMP.PROG_ID = INSPECT_RESULT.PROG_ID
GROUP BY
BARCODE.BARCODE,
FAULT.USER_FAULT_NAME,
COMP.COMP_NAME,
PIN_FAULT.PIN_NO,
INSPECT_RESULT.REWORK_END,
INSPECT_RESULT.REWORK_RESULT,
COMP_FAULT.REWORK_FAULT_ID
HAVING
barcode.barcode=‚0202810001‘ AND
INSPECT_RESULT.REWORK_RESULT=‚F‘ AND
COMP_FAULT.REWORK_FAULT_ID ‚‘
ORDER BY INSPECT_RESULT.REWORK_END
gruss Sascha