This is Oracle 9i, the tables cannot change, if this can't be done in a single query please let me know.
I am trying to query information from a few tables and for some of the columns I am trying to query from 2 tables to get the data. The problem I am running into is that the data is showing up in every row for the column when it should only be in 1 row. Below is the query and a screenshot of the output
case when t.tool_y_dim > 0 THEN TO_CHAR(t.tool_y_dim,'9.999') || ' x' Else null END ||
case when t.tool_x_dim > 0 THEN TO_CHAR(t.tool_x_dim, '9.999') Else null END AS DIM,
pt.tool_orient, t.tool_shape, t.tool_bin_location, pt.PROG_SEQ_NUM, (
SELECT PROG_NOTES_TEXT FROM MF_MACH_PROG_NOTES pn
JOIN MF_MACH_PROG_TOOL pt
ON pn.MACH_TYPE = pt.MACH_TYPE
AND pn.PROG_NUM = pt.PROG_NUM
AND pn.PROG_REV = pt.PROG_REV
AND pn.PROG_SEQ_NUM = pt.PROG_SEQ_NUM
WHERE pn.PROG_NOTES_TYPE = 'TOOL:PGM'
AND pn.PROG_NUM = '4-24277-9733'
AND pn.PROG_REV = 'A'
AND pn.MACH_TYPE = 'AMADA2'
) AS TL_PROG_NOTES
FROM MF_MACH_PROG_TOOL PT
JOIN MF_TOOL_LU T
ON pt.tool_id = t.tool_id
AND pt.MACH_TYPE = t.CONFIG_MACH_TYPE
WHERE PT.PROG_NUM = '4-24277-9733'
AND PT.PROG_REV = 'A'
AND PT.MACH_TYPE = 'AMADA2'
ORDER BY TO_NUMBER(TRANSLATE(SUBSTR(tool_station_num, -2),'0123456789' || SUBSTR(tool_station_num, -2),'0123456789'))
The problem is the not in TL_PROG_NOTES should only display for TOOL_STATION_NUM T141.
Here is a screen shot of the two tables I am trying to query from with the specific part number, rev and machine type. As you can see, the PROG_TOOL table has the sequence number that I am trying to match up on in the PROG_NOTES table.