Solved

Need help with a query

Posted on 2014-02-13
8
350 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

729 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