Solved

Need help with a query

Posted on 2014-02-13
8
349 Views
Last Modified: 2014-02-14
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

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'))

Open in new window

The problem is the not in TL_PROG_NOTES should only display for TOOL_STATION_NUM T141.
Query 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.
Prog Tool TableProgNotes
0
Comment
Question by:dustock
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39857166
I am currently on mobile, so difficult to read all of the above easily . Maybe my article  helps you to get the good sql.
http://mobile.experts-exchange.com/Database/Miscellaneous/A_3203-DISTINCT-vs-GROUP-BY-and-why-does-it-not-work-for-my-query.html
0
 
LVL 9

Author Comment

by:dustock
ID: 39857255
I will take a look and see if I can solve my problem with that.  Thanks!
0
 
LVL 35

Expert Comment

by:johnsone
ID: 39857564
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.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 8

Accepted Solution

by:
Surrano earned 500 total points
ID: 39858479
Your subquery for the note field:

	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'

Open in new window


refers to "pt" within itself and I assume you need to find the match by PT.PROG_SEQ_NUM but not the internal PT but the external PT.
So my first guess would be that PT must be pulled from outside:

...
	SELECT PROG_NOTES_TEXT FROM MF_MACH_PROG_NOTES pn
	WHERE pn.PROG_NOTES_TYPE = 'TOOL:PGM'
          AND 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
...

Open in new window


But you could also lift the subquery outside as a left join: (I did no tests but you can get the concept even if I took it wrong)

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, 
  pn.PROG_NOTES_TEXT 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)
LEFT OUTER JOIN MF_MACH_PROG_NOTES pn 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
  AND pn.PROG_NOTES_TYPE = 'TOOL:PGM')
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'))

Open in new window


Additional note: the case-else-null structure would be more portable if you used case-else-emptystring. Honestly I'm even surprised how it's supposed to work in Oracle at all... but according to your screenshot it does.
0
 
LVL 9

Author Comment

by:dustock
ID: 39858818
@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.
0
 
LVL 8

Expert Comment

by:Surrano
ID: 39858846
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)
0
 
LVL 9

Author Comment

by:dustock
ID: 39858859
OK, thanks for the follow up!  Its nice to get learn a some new things so I can build on my SQL skill set!
0
 
LVL 35

Expert Comment

by:johnsone
ID: 39858902
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.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question