Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Need help with a query

Posted on 2014-02-13
8
Medium Priority
?
352 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 8

Accepted Solution

by:
Surrano earned 2000 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

721 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