Solved

Need help with a query

Posted on 2014-02-13
8
345 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
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
Comment Utility
I will take a look and see if I can solve my problem with that.  Thanks!
0
 
LVL 34

Expert Comment

by:johnsone
Comment Utility
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
 
LVL 8

Accepted Solution

by:
Surrano earned 500 total points
Comment Utility
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 9

Author Comment

by:dustock
Comment Utility
@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
Comment Utility
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
Comment Utility
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 34

Expert Comment

by:johnsone
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

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…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
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 shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now