Need help with a query
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
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.
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
SELECT pt.tool_station_num,
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'))
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.
ASKER
I will take a look and see if I can solve my problem with that. Thanks!
Why do you think you should only get that one row? These are your limiting conditions:
WHERE PT.PROG_NUM = '4-24277-9733'
AND PT.PROG_REV = 'A'
AND PT.MACH_TYPE = 'AMADA2'
All of the rows that are in the output match those conditions. If you are expecting a different result then you are missing something in your where clause.
WHERE PT.PROG_NUM = '4-24277-9733'
AND PT.PROG_REV = 'A'
AND PT.MACH_TYPE = 'AMADA2'
All of the rows that are in the output match those conditions. If you are expecting a different result then you are missing something in your where clause.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@Surrano thank you very much, this is what I needed. I'll be the first to admit I'm not very strong with the joins so your help is much appreciated!
Also for my case else, are you saying instead of null I should just have single quotes '' ? The way I have it does give me the desired result. But I can change it if its a better way to do the case else.
Also for my case else, are you saying instead of null I should just have single quotes '' ? The way I have it does give me the desired result. But I can change it if its a better way to do the case else.
Well apparently follows the "mainstream" SQL thinking that "NULL" refers to "NOTHING" i.e. in concatenation it's empty string.
PostgreSQL, on the other hand, has a very strong statement about "NULL" referring to "UNKNOWN" i.e. in concatenation the result will be "UNKNOWN" i.e. NULL. Some more funny NULL operations:
NULL OR false is NULL but:
NULL OR true is true
NULL AND true is NULL but:
NULL AND false is false
And my favourite:
NULL = NULL (as a logical expression) is... (guess what) NULL
Get the idea? :)
Also, position of NULL in a sort order is something obscure, since
NULL < 1 is NULL
... I *believe* it's up to the RDBMS how to resolve this but most of the time I see NULLs coming at the END regardless whether you sort ascending (implying that NULL is the greatest value) or descending (implying that NULL is the smallest value). Funny.
(again, this is off-topic since it's related to PostgreSQL not Oracle)
PostgreSQL, on the other hand, has a very strong statement about "NULL" referring to "UNKNOWN" i.e. in concatenation the result will be "UNKNOWN" i.e. NULL. Some more funny NULL operations:
NULL OR false is NULL but:
NULL OR true is true
NULL AND true is NULL but:
NULL AND false is false
And my favourite:
NULL = NULL (as a logical expression) is... (guess what) NULL
Get the idea? :)
Also, position of NULL in a sort order is something obscure, since
NULL < 1 is NULL
... I *believe* it's up to the RDBMS how to resolve this but most of the time I see NULLs coming at the END regardless whether you sort ascending (implying that NULL is the greatest value) or descending (implying that NULL is the smallest value). Funny.
(again, this is off-topic since it's related to PostgreSQL not Oracle)
ASKER
OK, thanks for the follow up! Its nice to get learn a some new things so I can build on my SQL skill set!
Just to add, Oracle does not distinguish between the empty string (i.e. '') and NULL. As far as Oracle is concerned an empty string is null.
http://mobile.experts-exchange.com/Database/Miscellaneous/A_3203-DISTINCT-vs-GROUP-BY-and-why-does-it-not-work-for-my-query.html