• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 358
  • Last Modified:

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

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
dustock
Asked:
dustock
  • 3
  • 2
  • 2
  • +1
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
dustockAuthor Commented:
I will take a look and see if I can solve my problem with that.  Thanks!
0
 
johnsoneSenior Oracle DBACommented:
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
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
SurranoSystem EngineerCommented:
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
 
dustockAuthor Commented:
@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
 
SurranoSystem EngineerCommented:
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
 
dustockAuthor Commented:
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
 
johnsoneSenior Oracle DBACommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now