I am attempting write a query that joins a table to itself multiple times in order to create a sort of hierarchy structure. The database I am querying is used in a manufacturing setting where item numbers change during the different stages of production for any given part. (For example, an item starts as Mxxxxxx, becomes Pxxxxxxx, and then when it is finished, is given the final number of 1xxxxxx).
Thus far, I've been able to achieve something similar to the result set I am looking for. When I run the below query against the database, I get a result set that more or less is what I want. However, when I try and use this same exact query in our BI Tool, I get an ORA-00918: column ambiguously defined. I have scanned this query over 3 times now, gave every column an alias and still cannot find what on earth is causing the issue.
I know this is a huge query, but does anyone know where this troublesome column name might be? Is their some kind of tool I could use in the future to make column ambiguously defined errors a bit easier to identify?
Here is the oracle/sql query I have rewritten/modified from an existing report:
SELECT
STANDARD.MFGNO AS STANDARD_MFGNO,
ARINVT.ITEMNO AS ARINVT_ITEMNO,
ARINVT.DESCRIP AS ARINVT_DESCRIP,
ARCUSTO.COMPANY AS ARCUSTO_COMPANY,
STANDARD.BOM_CNTRL AS STANDARD_BOM_CNTRL,
STANDARD.CYCLETM AS STANDARD_CYCLETM,
STANDARD.OPERATOR AS STANDARD_OPERATOR,
STANDARD.CNTR_TYPE AS STANDARD_CNTR_TYPE,
STANDARD.SCRAP AS STANDARD_SCRAP,
STANDARD.SETUPHRS AS STANDARD_SETUPHOURS,
ARINVT.REV AS ARINVT_REV,
ARINVT_OPMAT.ITEMNO AS ARINVT_OPMAT_ITEMNO,
ARINVT_OPMAT.DESCRIP AS ARINVT_OPMAT_DESCRIP,
STANDARD.MFG_TYPE,
STANDARD.MFGCELL,
STANDARD.EPLANT_ID,
OPMAT.PTSPER_DISP AS PTSPER_DISP,
OPMAT.SEQ AS OPMAT_SEQ,
STANDARD.FIRST_ARTICLE_DATE,
STANDARD.SETUPHRS2,
STANDARD.EFF_FACTOR,
ARINVT_RAWMAT.DESCRIP AS ARINVT_RAWMAT_DESCRIP,
ARINVT_RAWMAT.ITEMNO AS ARINVT_RAWMAT_ITEMNO,
ARINVT_opmat_2.ITEMNO AS ARINVT_OPMAT_ITEMNO_2,
ARINVT_opmat_2.DESCRIP AS ARINVT_OPMAT_DESCRIP_2,
PTOPER_2.OPSEQ AS OPSEQ2,
OPMAT_3.SEQ AS SEQ_3,
ARINVT_opmat_3.ITEMNO AS ARINVT_OPMAT_ITEMNO_3,
ARINVT_opmat_3.DESCRIP AS ARINVT_OPMAT_DESCRIP_3,
PARTNO_2.ARINVT_ID AS PARTNO_2_ARINVT_ID,
ARINVT_opmat_4.CLASS AS OPMAT_4_CLASS,
ARINVT_opmat_4.ITEMNO AS ARINVT_OPMAT_ITEMNO_4,
ARINVT_opmat_4.DESCRIP AS ARINVT_OPMAT_DESCRIP_4,
STANDARD_2.ID AS STANDARD_ID_2,
ARINVT_mat_2.ITEMNO AS ARINVT_MAT_ITEMNO_2,
ARINVT_mat_2.DESCRIP AS ARINVT_MAT_2_DESCRIP,
STANDARD_3.ID AS STANDARD_ID_3,
ARINVT_mat_3.ITEMNO AS ARINVT_MAT_ITEMNO_3,
ARINVT_mat_3.DESCRIP AS ARINVT_MAT_3_DESCRIP,
PARTNO_3.ARINVT_ID AS PARTNO_3_ARINVT_ID,
STANDARD_4.ID AS STANDARD_ID_4,
ARINVT_mat_4.ITEMNO AS ARINVT_MAT_ITEMNO_4,
ARINVT_mat_4.DESCRIP AS ARINVT_MAT_4_DESCRIP,
STANDARD.ARINVT_ID_MAT AS STANDARD_ARINVT_ID_MAT,
ARINVT_opmat_2.CLASS as OPMAT_2_CLASS,
ARINVT_OPMAT.CLASS AS OPMAT_CLASS,
ARINVT_opmat_3.CLASS AS OPMAT_3_CLASS,
OPMAT_3.ARINVT_ID AS OPMAT_3_ARINVT_ID,
OPMAT_2.ARINVT_ID AS OPMAT_2_ARINVT_ID,
OPMAT_4.ARINVT_ID AS OPMAT_4_ARINVT_ID,
OPMAT_2.PTSPER AS OPMAT_2_PTSPER,
OPMAT_3.PTSPER AS OPMAT_3_PTSPER,
OPMAT_4.PTSPER AS OPMAT_4_PTSPER,
SNDOP.OPDESC AS SNDOP_OPDESC,
PTOPER.OPSEQ AS PTOPER_OPSEQ,
OPMAT.ID AS OPMAT_ID,
SNDOP_2.OPDESC AS SNDOP_2_OPDESC,
PTOPER_3.OPSEQ AS PTOPER_3_OPSEQ,
SNDOP_3.OPDESC AS SNDOP_3_OPDESC,
PTOPER_4.OPSEQ AS PTOPER_4_OPSEQ,
SNDOP_4.OPDESC AS SNDOP_4_OPDESC,
OPMAT_2.SEQ AS OPMAT_2_SEQ,
OPMAT_2.ID AS OPMAT_2_ID,
OPMAT_3.ID AS OPMAT_3_ID,
ARINVT_opmat_3.ID AS ARINVT_OPMAT_3_ID,
OPMAT_4.SEQ AS OPMAT_4_SEQ,
OPMAT_4.ID AS OPMAT_4_ID,
STANDARD_5.ID AS STANDARD_5_ID,
PTOPER_5.OPSEQ AS PTOPER_5_OPSEQ,
OPMAT_5.SEQ AS OPMAT_5_SEQ,
OPMAT_5.ID AS OPMAT_5_ID,
ARINVT_opmat_5.CLASS AS OPMAT_5_CLASS,
ARINVT_opmat_5.ITEMNO AS OPMAT_5_ITEMNO,
ARINVT_opmat_5.DESCRIP AS OPMAT_5_DESCRIP,
OPMAT_5.PTSPER AS OPMAT_5_PTSPER,
STANDARD_6.ID AS STANDARD_ID_6,
PTOPER_6.OPSEQ AS PTOPER_6_OPSEQ,
OPMAT_6.SEQ AS OPMAT_6_SEQ,
OPMAT_6.ID AS OPMAT_6_ID,
ARINVT_opmat_6.CLASS AS OPMAT_6_CLASS,
ARINVT_opmat_6.ITEMNO AS OPMAT_6_ITEMNO,
ARINVT_opmat_6.DESCRIP AS OPMAT_6_DESCRIP,
STANDARD_7.ID AS STANDARD_ID_7,
PTOPER_7.OPSEQ AS PTOPER_7_OPSEQ,
OPMAT_7.SEQ AS OPMAT_7_SEQ,
OPMAT_7.ID AS OPMAT_7_ID,
ARINVT_opmat_7.CLASS AS OPMAT_7_CLASS,
ARINVT_opmat_7.ITEMNO AS OPMAT_7_ITEMNO,
ARINVT_opmat_7.DESCRIP AS OPMAT_7_DESCRIP,
OPMAT_7.PTSPER AS OPMAT_7_PTSPER,
ARINVT_opmat_7.STANDARD_ID AS ARINVT_OPMAT_7_STANDARD_ID,
EPLANT.COMPANY AS EPLANT_COMPANY,
ARINVT_RAWMAT.ID AS ARINVT_RAWMAT_ID,
PARTNO_4.ARINVT_ID AS PARTNO_4_ARINVT_ID,
PARTNO_5.ARINVT_ID AS PARTNO_5_ARINVT_ID,
PARTNO_6.ARINVT_ID AS PARTNO_6_ARINVT_ID,
PARTNO_7.ARINVT_ID AS PARTNO_7_ARINVT_ID,
ARINVT_opmat_2.ID AS ARINVT_OPMAT_2_ID,
ARINVT_OPMAT.ID AS ARINVT_OPMAT_ID,
ARINVT_mat_2.ID AS ARINVT_MAT_2_ID,
ARINVT_mat_3.ID AS ARINVT_MAT_3_ID,
ARINVT_mat_4.ID AS ARINVT_MAT_4_ID,
ARINVT_opmat_4.ID AS ARINVT_OPMAT_4_ID,
ARINVT_opmat_5.ID AS ARINVT_OPMAT_5_ID,
ARINVT_opmat_6.ID AS ARINVT_OPMAT_6_ID,
ARINVT_opmat_7.ID AS ARINVT_OPMAT_7_ID,
ARINVT_mat_5.CLASS AS ARINVT_MAT_5_CLASS,
ARINVT_mat_6.CLASS AS ARINVT_MAT_6_CLASS,
ARINVT_mat_6.ID AS ARINVT_MAT_6_ID,
ARINVT_mat_5.ID AS ARINVT_MAT_5_ID,
ARINVT_mat_5.ITEMNO AS ARINVT_MAT_5_ITEMNO,
ARINVT_mat_5.DESCRIP AS ARINVT_MAT_5_DESCRIP,
ARINVT_mat_6.ITEMNO AS ARINVT_MAT_6_ITEMNO,
ARINVT_mat_6.DESCRIP AS ARINVT_MAT_6_DESCRIP,
ARINVT_mat_7.ITEMNO AS ARINVT_MAT_7_ITEMNO,
ARINVT_mat_7.DESCRIP AS ARINVT_MAT_7_DESCRIP,
ARINVT_mat_7.CLASS AS ARINVT_MAT_7_CLASS,
ARINVT_mat_7.ID AS ARINVT_MAT_7_ID,
ARINVT_mat_2.UNIT AS ARINVT_MAT_2_UNIT,
ARINVT_RAWMAT.UNIT AS ARINVT_RAWMAT_UNIT,
ARINVT_RAWMAT.STANDARD_ID AS ARINVT_RAWMAT_STANDARD_ID,
ARINVT_mat_2.STANDARD_ID AS ARINVT_MAT_2_STANDARD_ID,
ARINVT_mat_3.STANDARD_ID AS ARINVT_MAT_3_STANDARD_ID,
ARINVT_mat_5.STANDARD_ID AS ARINVT_MAT_5_STANDARD_ID,
ARINVT_mat_6.STANDARD_ID AS ARINVT_MAT_6_STANDARD_ID,
ARINVT_mat_7.STANDARD_ID AS ARINVT_MAT_7_STANDARD_ID,
ARINVT_mat_4.STANDARD_ID AS ARINVT_MAT_4_STANDARD_ID,
STANDARD.DESCRIP AS STANDARD_DESCRIP,
STANDARD.UOM AS STANDARD_UOM,
OPMAT.UNIT AS OPMAT_UNIT,
OPMAT_3.UNIT AS OPMAT_3_UNIT,
OPMAT_5.UNIT AS OPMAT_5_UNIT,
OPMAT_7.UNIT AS OPMAT_7_UNIT,
SNDOP_5.OPDESC AS SNDOP_5_OPDESC,
SNDOP_6.OPDESC AS SNDOP_6_OPDESC,
SNDOP_7.OPDESC AS SNDOP_7_OPDESC,
OPMAT.PTSPER AS OPMAT_PTSPER,
ARINVT_OPMAT.UNIT AS ARINVT_OPMAT_UNIT,
ARINVT_opmat_2.UNIT AS ARINVT_2_OPMAT_UNIT,
OPMAT_2.UNIT AS OPMAT_2_UNIT,
OPMAT_2.PTSPER_DISP AS OPMAT_2_PTSPER_DISP,
ARINVT_OPMAT.STANDARD_ID AS ARINVT_OPMAT_STANDARD_ID,
ARINVT_opmat_2.STANDARD_ID AS ARINVT_OPMAT_2_STANDARD_ID,
ARINVT_mat_3.UNIT AS ARINVT_MAT_3_UNIT,
ARINVT_opmat_3.STANDARD_ID AS ARINVT_OPMAT_3_STANDARD_ID,
ARINVT_mat_4.UNIT AS ARINVT_MAT_4_UNIT,
ARINVT_opmat_4.STANDARD_ID AS ARINVT_OPMAT_4_STANDARD_ID,
ARINVT_mat_5.UNIT AS ARINVT_MAT_5_UNIT,
ARINVT_opmat_5.STANDARD_ID AS ARINVT_OPMAT_5_STANDARD_ID,
ARINVT_mat_6.UNIT AS ARINVT_MAT_6_UNIT,
ARINVT_opmat_6.STANDARD_ID AS ARINVT_OPMAT_6_STANDARD_ID,
ARINVT_mat_7.UNIT AS ARINVT_MAT_7_UNIT,
ARINVT_opmat_3.UNIT AS ARINVT_OPMAT_3_UNIT,
OPMAT_3.PTSPER_DISP AS OPMAT_3_PTSPER_DISP,
ARINVT_opmat_4.UNIT AS ARINVT_OPMAT_4_UNIT,
OPMAT_4.UNIT AS OPMAT_4_UNIT,
OPMAT_4.PTSPER_DISP AS OPMAT_4_PTSPER_DISP,
ARINVT_opmat_5.UNIT AS ARINVT_OPMAT_5_UNIT,
OPMAT_5.PTSPER_DISP AS OPMAT_5_PTSPER_DISP,
ARINVT_opmat_6.UNIT AS ARINVT_OPMAT_6_UNIT,
OPMAT_6.PTSPER AS OPMAT_5_PTSPER,
OPMAT_6.UNIT AS OPMAT_6_UNIT,
OPMAT_6.PTSPER_DISP AS OPMAT_6_PTSPER_DISP,
ARINVT_opmat_7.UNIT AS ARINVT_OPMAT_7_UNIT,
OPMAT_7.PTSPER_DISP AS OPMAT_7_PTSPER_DISP,
STANDARD.ID AS STANDARD_ID,
ARINVT.ID AS ARINVT_ID,
ARINVT_RAWMAT.NON_MATERIAL AS ARINVT_RAWMAT_NON_MATERIAL,
ARINVT_OPMAT.NON_MATERIAL AS ARINVT_OPMAT_NON_MATERIAL,
ARINVT_opmat_2.NON_MATERIAL AS ARINVT_OPMAT_2_NON_MATERIAL,
ARINVT_opmat_3.NON_MATERIAL AS ARINVT_OPMAT_3_NON_MATERIAL,
ARINVT_opmat_4.NON_MATERIAL AS ARINVT_OPMAT_4_NON_MATERIAL,
ARINVT_opmat_5.NON_MATERIAL AS ARINVT_OPMAT_5_NON_MATERIAL,
ARINVT_opmat_6.NON_MATERIAL AS ARINVT_OPMAT_6_NON_MATERIAL,
ARINVT_opmat_7.NON_MATERIAL AS ARINVT_OPMAT_7_NON_MATERIAL,
ARINVT_mat_2.NON_MATERIAL AS ARINVT_MAT_2_NON_MATERIAL,
ARINVT_mat_3.NON_MATERIAL AS ARINVT_MAT_3_NON_MATERIAL,
ARINVT_mat_4.NON_MATERIAL AS ARINVT_MAT_4_NON_MATERIAL,
ARINVT_mat_5.NON_MATERIAL AS ARINVT_MAT_5_NON_MATERIAL,
ARINVT_mat_6.NON_MATERIAL AS ARINVT_MAT_6_NON_MATERIAL,
ARINVT_mat_7.NON_MATERIAL AS ARINVT_MAT_7_NON_MATERIAL
FROM
(
(
(
(
(
(
(
(
(
(
(
(
(
(
(
(
(
(
(
(
(
(
(
(
(
(
(
(
(
(
(
(
(
(
(
(
(
(
(
(
(
(
(
(
(
(
(
(
(
(
IQMS.STANDARD STANDARD
LEFT OUTER JOIN IQMS.PARTNO PARTNO ON STANDARD.ID = PARTNO.STANDARD_ID
)
LEFT OUTER JOIN IQMS.ARCUSTO ARCUSTO ON STANDARD.ARCUSTO_ID = ARCUSTO.ID
)
LEFT OUTER JOIN IQMS.EPLANT EPLANT ON STANDARD.EPLANT_ID = EPLANT.ID
)
LEFT OUTER JOIN IQMS.ARINVT ARINVT_RAWMAT ON STANDARD.ARINVT_ID_MAT = ARINVT_RAWMAT.ID
)
LEFT OUTER JOIN IQMS.ARINVT ARINVT ON PARTNO.ARINVT_ID = ARINVT.ID
)
LEFT OUTER JOIN IQMS.PTOPER PTOPER ON PARTNO.ID = PTOPER.PARTNO_ID
)
LEFT OUTER JOIN IQMS.SNDOP SNDOP ON PTOPER.SNDOP_ID = SNDOP.ID
)
LEFT OUTER JOIN IQMS.OPMAT OPMAT ON SNDOP.ID = OPMAT.SNDOP_ID
)
LEFT OUTER JOIN IQMS.ARINVT ARINVT_OPMAT ON OPMAT.ARINVT_ID = ARINVT_OPMAT.ID
)
LEFT OUTER JOIN IQMS.PARTNO PARTNO_2 ON (
ARINVT_OPMAT.STANDARD_ID = PARTNO_2.STANDARD_ID
)
AND (ARINVT_OPMAT.ID = PARTNO_2.ARINVT_ID)
)
LEFT OUTER JOIN IQMS.STANDARD STANDARD_2 ON PARTNO_2.STANDARD_ID = STANDARD_2.ID
)
LEFT OUTER JOIN IQMS.PTOPER PTOPER_2 ON PARTNO_2.ID = PTOPER_2.PARTNO_ID
)
LEFT OUTER JOIN IQMS.ARINVT ARINVT_mat_2 ON STANDARD_2.ARINVT_ID_MAT = ARINVT_mat_2.ID
)
LEFT OUTER JOIN IQMS.SNDOP SNDOP_2 ON PTOPER_2.SNDOP_ID = SNDOP_2.ID
)
LEFT OUTER JOIN IQMS.OPMAT OPMAT_2 ON SNDOP_2.ID = OPMAT_2.SNDOP_ID
)
LEFT OUTER JOIN IQMS.ARINVT ARINVT_opmat_2 ON OPMAT_2.ARINVT_ID = ARINVT_opmat_2.ID
)
LEFT OUTER JOIN IQMS.PARTNO PARTNO_3 ON (
ARINVT_opmat_2.STANDARD_ID = PARTNO_3.STANDARD_ID
)
AND (ARINVT_opmat_2.ID = PARTNO_3.ARINVT_ID)
)
LEFT OUTER JOIN IQMS.PTOPER PTOPER_3 ON PARTNO_3.ID = PTOPER_3.PARTNO_ID
)
LEFT OUTER JOIN IQMS.STANDARD STANDARD_3 ON PARTNO_3.STANDARD_ID = STANDARD_3.ID
)
LEFT OUTER JOIN IQMS.ARINVT ARINVT_mat_3 ON STANDARD_3.ARINVT_ID_MAT = ARINVT_mat_3.ID
)
LEFT OUTER JOIN IQMS.SNDOP SNDOP_3 ON PTOPER_3.SNDOP_ID = SNDOP_3.ID
)
LEFT OUTER JOIN IQMS.OPMAT OPMAT_3 ON SNDOP_3.ID = OPMAT_3.SNDOP_ID
)
LEFT OUTER JOIN IQMS.ARINVT ARINVT_opmat_3 ON OPMAT_3.ARINVT_ID = ARINVT_opmat_3.ID
)
LEFT OUTER JOIN IQMS.PARTNO PARTNO_4 ON (
ARINVT_opmat_3.STANDARD_ID = PARTNO_4.STANDARD_ID
)
AND (ARINVT_opmat_3.ID = PARTNO_4.ARINVT_ID)
)
LEFT OUTER JOIN IQMS.STANDARD STANDARD_4 ON PARTNO_4.STANDARD_ID = STANDARD_4.ID
)
LEFT OUTER JOIN IQMS.PTOPER PTOPER_4 ON PARTNO_4.ID = PTOPER_4.PARTNO_ID
)
LEFT OUTER JOIN IQMS.SNDOP SNDOP_4 ON PTOPER_4.SNDOP_ID = SNDOP_4.ID
)
LEFT OUTER JOIN IQMS.OPMAT OPMAT_4 ON SNDOP_4.ID = OPMAT_4.SNDOP_ID
)
LEFT OUTER JOIN IQMS.ARINVT ARINVT_opmat_4 ON OPMAT_4.ARINVT_ID = ARINVT_opmat_4.ID
)
LEFT OUTER JOIN IQMS.PARTNO PARTNO_5 ON ARINVT_opmat_4.STANDARD_ID = PARTNO_5.STANDARD_ID
)
LEFT OUTER JOIN IQMS.PTOPER PTOPER_5 ON PARTNO_5.ID = PTOPER_5.PARTNO_ID
)
LEFT OUTER JOIN IQMS.STANDARD STANDARD_5 ON PARTNO_5.STANDARD_ID = STANDARD_5.ID
)
LEFT OUTER JOIN IQMS.ARINVT ARINVT_mat_5 ON STANDARD_5.ARINVT_ID_MAT = ARINVT_mat_5.ID
)
LEFT OUTER JOIN IQMS.SNDOP SNDOP_5 ON PTOPER_5.SNDOP_ID = SNDOP_5.ID
)
LEFT OUTER JOIN IQMS.OPMAT OPMAT_5 ON SNDOP_5.ID = OPMAT_5.SNDOP_ID
)
LEFT OUTER JOIN IQMS.ARINVT ARINVT_opmat_5 ON OPMAT_5.ARINVT_ID = ARINVT_opmat_5.ID
)
LEFT OUTER JOIN IQMS.PARTNO PARTNO_6 ON ARINVT_opmat_5.STANDARD_ID = PARTNO_6.STANDARD_ID
)
LEFT OUTER JOIN IQMS.PTOPER PTOPER_6 ON PARTNO_6.ID = PTOPER_6.PARTNO_ID
)
LEFT OUTER JOIN IQMS.STANDARD STANDARD_6 ON PARTNO_6.STANDARD_ID = STANDARD_6.ID
)
LEFT OUTER JOIN IQMS.SNDOP SNDOP_6 ON PTOPER_6.SNDOP_ID = SNDOP_6.ID
)
LEFT OUTER JOIN IQMS.OPMAT OPMAT_6 ON SNDOP_6.ID = OPMAT_6.SNDOP_ID
)
LEFT OUTER JOIN IQMS.ARINVT ARINVT_opmat_6 ON OPMAT_6.ARINVT_ID = ARINVT_opmat_6.ID
)
LEFT OUTER JOIN IQMS.PARTNO PARTNO_7 ON ARINVT_opmat_6.STANDARD_ID = PARTNO_7.STANDARD_ID
)
LEFT OUTER JOIN IQMS.STANDARD STANDARD_7 ON PARTNO_7.STANDARD_ID = STANDARD_7.ID
)
LEFT OUTER JOIN IQMS.PTOPER PTOPER_7 ON PARTNO_7.ID = PTOPER_7.PARTNO_ID
)
LEFT OUTER JOIN IQMS.SNDOP SNDOP_7 ON PTOPER_7.SNDOP_ID = SNDOP_7.ID
)
LEFT OUTER JOIN IQMS.OPMAT OPMAT_7 ON SNDOP_7.ID = OPMAT_7.SNDOP_ID
)
LEFT OUTER JOIN IQMS.ARINVT ARINVT_opmat_7 ON OPMAT_7.ARINVT_ID = ARINVT_opmat_7.ID
)
LEFT OUTER JOIN IQMS.ARINVT ARINVT_mat_7 ON STANDARD_7.ARINVT_ID_MAT = ARINVT_mat_7.ID
)
LEFT OUTER JOIN IQMS.ARINVT ARINVT_mat_6 ON STANDARD_6.ARINVT_ID_MAT = ARINVT_mat_6.ID
)
LEFT OUTER JOIN IQMS.ARINVT ARINVT_mat_4 ON STANDARD
_4.ARINVT_ID_MAT = ARINVT_mat_4.ID
Pop into Excel, replace " as " with a unique character, then convert text to columns based on that character, then use a conditional formula to highlight duplicates. This entire process takes less than 30 seconds.
Column OPMAT_5_PTSPER is listed twice.