Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

MINUS is close but only provides DISTINCT results which doesn't work for me

632063Aug 6 2009 — edited Aug 7 2009
I am a new poster here so please forgive an etiquette faux pas I make here.

I have a table in which I store details about reports I run. In that table, I store the report_name (since many reports can be stored at once), the sequence of the data point that is selected, which section it prints to, and the subtotal to which it printed.

TABLE report_table
report_name VARCHAR2(60)
datapoint VARCHAR2(10)
section VARCHAR2(1)
sub_total VARCHAR2(1)

Since all datapoints that count IN to the report (sections 6, 8, 11, 22, and 23) also have to count OUT of the report (sections 27, 38, 42, and 44) and vice versa, I provide the users with a query which returns a list of the datapoints which did not count in and out the same number of times in the same report along with the section and subtotal to which they printed.

A datapoint can count in and out multiple times in the same run so these data points are listed multiple times.

A very basic query which will SOMETIMES work includes using MINUS to select the IN datapoints MINUS the OUT datapoints followed by an OR which selects the OUT datapoints MINUS the IN datapoints.
SELECT datapoint, sub_total, section
FROM report_table
WHERE datapoint IN
( (SELECT datapoint
FROM report_table
WHERE file_name = :p_file_name
AND section IN ('006', '008', '011', '022', '023')
AND report_name = selected_report_name
--(a bind variable is used here)
MINUS
SELECT datapoint
FROM report_table
WHERE section IN ('027', '038', '042', '044')
AND report_name = selected_report_name)
OR datapoint IN (
SELECT datapoint
FROM report_table
WHERE section IN ('027', '038', '042', '044')
AND report_name = selected_report_name
MINUS
SELECT datapoint
FROM report_table
WHERE section IN ('006', '008', '011', '022', '023')
AND report_name = selected_report_name)
)
ORDER BY datapoint;

This is unreliable, however, because MINUS removes duplicates so if a datapoint counts IN 3 times and OUT 2 times, no results are selected.

I can make sure the line is not duplicated by using a GROUP BY to COUNT the number of datapoints returned.

SELECT datapoint, COUNT (*)
FROM report_table
WHERE report_name = selected_report_name
AND section IN ('006', '008', '011', '022', '023')
GROUP BY datapoint
MINUS
SELECT datapoint, COUNT (*)
FROM report_table
WHERE report_name = selected_report_name
AND section IN ('027', '038', '042', '044')
GROUP BY datapoint
UNION
SELECT datapoint, COUNT (*)
FROM report_table
WHERE report_name = selected_report_name
AND section IN ('027', '038', '042', '044')
GROUP BY datapoint
MINUS
SELECT datapoint, COUNT (*)
FROM report_table
WHERE report_name = selected_report_name
AND section IN ('006', '008', '011', '022', '023')
GROUP BY datapoint;

The problem is that I cannot select the information I really need (datapoint, section, and sub_total) from the resulting set because the resulting set is not returning a single value.

There must be some logical way to do this that I am missing. Any thoughts would be appreciated.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Sep 4 2009
Added on Aug 6 2009
3 comments
635 views