Oracle Minus query. How can I get a result with NULLS if the top SQL and the bottom SQL don't contain NULLs?

How could this SQL...

CREATE TABLE NewTable AS SELECT A,B,C FROM Table1 minus SELECT A, B, C From Table2

...create a new table with NULL values in column A when neither Table1 or Table2 had NULL values for in column A?

But on the other hand, this SQL...

SELECT * FROM ( SELECT A,B,C FROM Table1 minus SELECT A, B, C From Table2 ) WHERE A IS NULL

return no rows!

It seems inconsistent!

I think it is a bug in Oracle.

Of course the real SQL is much more complex but I believe this accurately illustrates the nature of the problem.

UPDATE

Here's the ACTUAL SQL:

I executed this statement:

CREATE TABLE MyMinus AS select * FROM ---begin main query ( SELECT expenditure_item_date, expenditure_org, expenditure_type, f_amount_billed, f_amount_billed_fc, f_amount_billed_us, f_bl_creation_date, f_catalog_source, f_catalog_type, f_company, f_company_code, f_cost_center_num, f_cuic, f_currency_code, f_destination_type_code, f_distribution_id, f_distribution_num, f_exchange_rate, f_extract_date, f_gl_account, f_isms_jamis_project_num, f_line_id, f_local_use, f_location_num, f_need_by_date, f_org_id, f_po_line_num, f_po_num, f_po_release_num, f_project, f_project_num, f_promised_date, f_quantity_billed, f_quantity_cancelled, f_quantity_delivered, f_quantity_ordered, f_rel_approved_flag, f_rel_cancelled_flag, f_rel_cancel_date, f_rel_closed_code, f_rel_hold_flag, f_rel_revision_num, f_task_num FROM dw_mgr.po_distributions_curr_fct a WHERE EXISTS ( SELECT 1 FROM dw_mgr.po_distributions_curr_fct b, dw_mgr.po_lines_curr_fct, dw_mgr.po_header_curr_fct WHERE a.ROWID = b.ROWID AND b.f_cuic = dw_mgr.po_lines_curr_fct.f_cuic AND b.f_line_id = dw_mgr.po_lines_curr_fct.f_line_id AND dw_mgr.po_lines_curr_fct.f_cuic = dw_mgr.po_header_curr_fct.f_cuic AND dw_mgr.po_lines_curr_fct.f_header_id = dw_mgr.po_header_curr_fct.f_header_id AND dw_mgr.po_header_curr_fct.f_header_creation_date < ADD_MONTHS (TRUNC (SYSDATE, 'YEAR'), -48) AND dw_mgr.po_header_curr_fct.f_po_status IN ('CLOSED', 'FINALLY CLOSED')) MINUS SELECT expenditure_item_date, expenditure_org, expenditure_type, f_amount_billed, f_amount_billed_fc, f_amount_billed_us, f_bl_creation_date, f_catalog_source, f_catalog_type, f_company, f_company_code, f_cost_center_num, f_cuic, f_currency_code, f_destination_type_code, f_distribution_id, f_distribution_num, f_exchange_rate, f_extract_date, f_gl_account, f_isms_jamis_project_num, f_line_id, f_local_use, f_location_num, f_need_by_date, f_org_id, f_po_line_num, f_po_num, f_po_release_num, f_project, f_project_num, f_promised_date, f_quantity_billed, f_quantity_cancelled, f_quantity_delivered, f_quantity_ordered, f_rel_approved_flag, f_rel_cancelled_flag, f_rel_cancel_date, f_rel_closed_code, f_rel_hold_flag, f_rel_revision_num, f_task_num FROM arch_fct.po_distributions_curr_fct a WHERE EXISTS ( SELECT 1 FROM arch_fct.po_distributions_curr_fct b, arch_fct.po_lines_curr_fct, arch_fct.po_header_curr_fct WHERE a.ROWID = b.ROWID AND b.f_cuic = arch_fct.po_lines_curr_fct.f_cuic AND b.f_line_id = arch_fct.po_lines_curr_fct.f_line_id AND arch_fct.po_lines_curr_fct.f_cuic = arch_fct.po_header_curr_fct.f_cuic AND arch_fct.po_lines_curr_fct.f_header_id = arch_fct.po_header_curr_fct.f_header_id AND arch_fct.po_header_curr_fct.f_header_creation_date < ADD_MONTHS (TRUNC (SYSDATE, 'YEAR'), -48) AND arch_fct.po_header_curr_fct.f_po_status IN ('CLOSED', 'FINALLY CLOSED')) )

And then this. Note that rows with NULL values of F_DISTRIBUTION_ID were inserted into the created table.

SELECT COUNT(*) from MyMinus WHERE F_DISTRIBUTION_ID IS NULL

--17 rows

Yet when I execute this:

select * FROM ---begin main query ( SELECT expenditure_item_date, expenditure_org, expenditure_type, f_amount_billed, f_amount_billed_fc, f_amount_billed_us, f_bl_creation_date, f_catalog_source, f_catalog_type, f_company, f_company_code, f_cost_center_num, f_cuic, f_currency_code, f_destination_type_code, f_distribution_id, f_distribution_num, f_exchange_rate, f_extract_date, f_gl_account, f_isms_jamis_project_num, f_line_id, f_local_use, f_location_num, f_need_by_date, f_org_id, f_po_line_num, f_po_num, f_po_release_num, f_project, f_project_num, f_promised_date, f_quantity_billed, f_quantity_cancelled, f_quantity_delivered, f_quantity_ordered, f_rel_approved_flag, f_rel_cancelled_flag, f_rel_cancel_date, f_rel_closed_code, f_rel_hold_flag, f_rel_revision_num, f_task_num FROM dw_mgr.po_distributions_curr_fct a WHERE EXISTS ( SELECT 1 FROM dw_mgr.po_distributions_curr_fct b, dw_mgr.po_lines_curr_fct, dw_mgr.po_header_curr_fct WHERE a.ROWID = b.ROWID AND b.f_cuic = dw_mgr.po_lines_curr_fct.f_cuic AND b.f_line_id = dw_mgr.po_lines_curr_fct.f_line_id AND dw_mgr.po_lines_curr_fct.f_cuic = dw_mgr.po_header_curr_fct.f_cuic AND dw_mgr.po_lines_curr_fct.f_header_id = dw_mgr.po_header_curr_fct.f_header_id AND dw_mgr.po_header_curr_fct.f_header_creation_date < ADD_MONTHS (TRUNC (SYSDATE, 'YEAR'), -48) AND dw_mgr.po_header_curr_fct.f_po_status IN ('CLOSED', 'FINALLY CLOSED')) MINUS SELECT expenditure_item_date, expenditure_org, expenditure_type, f_amount_billed, f_amount_billed_fc, f_amount_billed_us, f_bl_creation_date, f_catalog_source, f_catalog_type, f_company, f_company_code, f_cost_center_num, f_cuic, f_currency_code, f_destination_type_code, f_distribution_id, f_distribution_num, f_exchange_rate, f_extract_date, f_gl_account, f_isms_jamis_project_num, f_line_id, f_local_use, f_location_num, f_need_by_date, f_org_id, f_po_line_num, f_po_num, f_po_release_num, f_project, f_project_num, f_promised_date, f_quantity_billed, f_quantity_cancelled, f_quantity_delivered, f_quantity_ordered, f_rel_approved_flag, f_rel_cancelled_flag, f_rel_cancel_date, f_rel_closed_code, f_rel_hold_flag, f_rel_revision_num, f_task_num FROM arch_fct.po_distributions_curr_fct a WHERE EXISTS ( SELECT 1 FROM arch_fct.po_distributions_curr_fct b, arch_fct.po_lines_curr_fct, arch_fct.po_header_curr_fct WHERE a.ROWID = b.ROWID AND b.f_cuic = arch_fct.po_lines_curr_fct.f_cuic AND b.f_line_id = arch_fct.po_lines_curr_fct.f_line_id AND arch_fct.po_lines_curr_fct.f_cuic = arch_fct.po_header_curr_fct.f_cuic AND arch_fct.po_lines_curr_fct.f_header_id = arch_fct.po_header_curr_fct.f_header_id AND arch_fct.po_header_curr_fct.f_header_creation_date < ADD_MONTHS (TRUNC (SYSDATE, 'YEAR'), -48) AND arch_fct.po_header_curr_fct.f_po_status IN ('CLOSED', 'FINALLY CLOSED')) ) WHERE f_distribution_id is null

I get 0 rows.

Why does insert the records into a temp table appear to introduce rows with NULL DIST IDs?

This minus query SQL, which was generated dynamically by a custom data archival program, attempts to verify that the data which SHOULD be archived in the DW_MGR schema was in fact copied to the ARCH_FCT (archive) schema. It is returning differences which included 17 records where the F_DISTRIBUTION_ID in the MyMinus temp table do not match those in the source DW_MG.PO_DISTRIBUTIONS_CURR_FCT table because they are are NULL. Hence, the archive process is design when differences are found. The question is why are there differences, i.e., how did NULL values get into the MyMinus table when they are not in the SOURCE PO_DISTRIBUTIONS_CURR_FCT table?

EDIT:

Can someone with Oracle META access please post info on thd following Oracle bugs. I was referred to them but I contract located someone in my co who can tell me what our support ID # is. I will find out eventually, but it would be nice to know sooner. If you would rather not post it, consider the following bug references as potentially related info on my question:

Bug 8209309: MINUS IS SHOWING DIFFERENCES WITH CTAS + INSERT Bug 7834950: WRONG RESULTS WITH MINUS OPERATOR

-------------Problems Reply------------

Firstly, I'd get rid of the ROWID to ROWID join. Then I'd get make the table aiases unique (not reusing 'a' and 'b' in the query above the MINUS and the query below the MINUS).

Finally, I'd look at those 17 rows and try to find the matching records in "dw_mgr.po_distributions_curr_fct" and see, using DUMP(F_DISTRIBUTION_ID) where there is anything odd about the column values.

Quit breaking your chops. It's an Oracle bug. I'll prove it to ya:

First of all, it has to be the first SQL that is returning NULLS for DISTRIBUTION ID, so isolate that SQL and let's call it "SQL1."

OK, Let's simplify SQL1 for discussion sake and say that it is of this format:

CREATE TABLE TempTable AS
SELECT
F_DISTRIBUTION_ID,
FIELD2,
FIELD3,...FIELD99

FROM WHATEVER
WHERE WHATEVER

Then, you are finding that when you execute this, you are finding rows that have a NULL DIST ID:

SELECT COUNT(*) FROM TempTable WHERE F_DISTRIBUTION_ID IS NULL
--Some positive number of rows returned.

If Oracle wasn't a piece of crap, you could change the number of selected fields so that only F_DISTRIBUTION_ID was selected and you would get the same result when you counted the number of rows with a NULL value of F_DISTRIBUTION_ID, right? Right! But that ain't the case, 'cause Oracle is an unreliable dinosaur.

Try this:

CREATE TABLE TempTable AS
SELECT
F_DISTRIBUTION_ID
FROM WHATEVER
WHERE WHATEVER

SELECT COUNT(*) FROM TempTable WHERE F_DISTRIBUTION_ID IS NULL

I betcha dollars to donuts that you get 0 rows returned.

Now, go call up Microsoft and tell them you want to upgrade to SQL Server 2008 R2.

It generally shouldn't.

The only time it might is if you've some advanced security features (fine grained access control) whereby the optimizer can see that A cannot be null in table1/table2 so returns zero rows, but the FGAC kicks in to stop you seeing the actual values in the column by returning null.



EDIT. "With [Virtual Private Database] column-masking behavior, all rows display, even those that reference sensitive columns. However, the sensitive columns display as NULL values. "

http://download.oracle.com/docs/cd/E11882_01/network.112/e10574/vpd.htm#i1014682

The only way I can think that F_DISTRIBUTION_ID could be NULL when inserted into MyMinus would be if it's returning NULL somehow, someway in the first query.

To reproduce this (on both 9i and 10g):

SQL> INSERT INTO table1 VALUES (NULL, 2, 3);

1 row created.

SQL> INSERT INTO table2 VALUES (1, 2, 3);

1 row created.

SQL> SELECT *
2 FROM (
3 SELECT a, b, c FROM table1
4 MINUS
5 SELECT a, b, c FROM table2);

A B C
---------- ---------- ----------
2 3

However, with regards to the query returning no rows when run by itself...that's something else. A bug wouldn't surprise me...but have you tried taking out those EXISTS? Of course, there's many different approaches, but perhaps all those sub-queries are causing something funny to happen in memory.

For example:

SELECT expenditure_item_date, expenditure_org, expenditure_type,
f_amount_billed, f_amount_billed_fc, f_amount_billed_us,
f_bl_creation_date, f_catalog_source, f_catalog_type, f_company,
f_company_code, f_cost_center_num, f_cuic, f_currency_code,
f_destination_type_code, f_distribution_id, f_distribution_num,
f_exchange_rate, f_extract_date, f_gl_account,
f_isms_jamis_project_num, f_line_id, f_local_use, f_location_num,
f_need_by_date, f_org_id, f_po_line_num, f_po_num, f_po_release_num,
f_project, f_project_num, f_promised_date, f_quantity_billed,
f_quantity_cancelled, f_quantity_delivered, f_quantity_ordered,
f_rel_approved_flag, f_rel_cancelled_flag, f_rel_cancel_date,
f_rel_closed_code, f_rel_hold_flag, f_rel_revision_num, f_task_num
FROM dw_mgr.po_distributions_curr_fct a
dw_mgr.po_lines_curr_fct,
dw_mgr.po_header_curr_fct
WHERE a.f_cuic = dw_mgr.po_lines_curr_fct.f_cuic
AND a.f_line_id = dw_mgr.po_lines_curr_fct.f_line_id
AND dw_mgr.po_lines_curr_fct.f_cuic = dw_mgr.po_header_curr_fct.f_cuic
AND dw_mgr.po_lines_curr_fct.f_header_id = dw_mgr.po_header_curr_fct.f_header_id
AND dw_mgr.po_header_curr_fct.f_header_creation_date < ADD_MONTHS (TRUNC (SYSDATE, 'YEAR'), -48)
AND dw_mgr.po_header_curr_fct.f_po_status IN ('CLOSED', 'FINALLY CLOSED')
MINUS
SELECT expenditure_item_date, expenditure_org, expenditure_type,
f_amount_billed, f_amount_billed_fc, f_amount_billed_us,
f_bl_creation_date, f_catalog_source, f_catalog_type, f_company,
f_company_code, f_cost_center_num, f_cuic, f_currency_code,
f_destination_type_code, f_distribution_id, f_distribution_num,
f_exchange_rate, f_extract_date, f_gl_account,
f_isms_jamis_project_num, f_line_id, f_local_use, f_location_num,
f_need_by_date, f_org_id, f_po_line_num, f_po_num, f_po_release_num,
f_project, f_project_num, f_promised_date, f_quantity_billed,
f_quantity_cancelled, f_quantity_delivered, f_quantity_ordered,
f_rel_approved_flag, f_rel_cancelled_flag, f_rel_cancel_date,
f_rel_closed_code, f_rel_hold_flag, f_rel_revision_num, f_task_num
FROM arch_fct.po_distributions_curr_fct a,
arch_fct.po_lines_curr_fct,
arch_fct.po_header_curr_fct
WHERE a.f_cuic = arch_fct.po_lines_curr_fct.f_cuic
AND a.f_line_id = arch_fct.po_lines_curr_fct.f_line_id
AND arch_fct.po_lines_curr_fct.f_cuic = arch_fct.po_header_curr_fct.f_cuic
AND arch_fct.po_lines_curr_fct.f_header_id = arch_fct.po_header_curr_fct.f_header_id
AND arch_fct.po_header_curr_fct.f_header_creation_date < ADD_MONTHS (TRUNC (SYSDATE, 'YEAR'), -48)
AND arch_fct.po_header_curr_fct.f_po_status IN ('CLOSED', 'FINALLY CLOSED')

I'm having a possibly related issue which started out as an issue with MINUS when using a bunch of complex views and exists. I narrowed it down to a probable optimiser issue - you can work around it by stopping the optimiser from messing around with things based on "F_DISTRIBUTION_ID IS NULL" by using something like "upper(F_DISTRIBUTION_ID) IS NULL".

Its next to impossible to create a simplified test case for a bug report in these cases - it likely only occurs in very specific scenarios (its not like MINUS is going to be completely broken after all). With my issue I couldnt reduce the query at all and have it still occur.

FYI my issue was a query which basically joined a bunch of stuff onto a source table, call it Employee. I had a where clause on the primary key of Employee - if I did a where EmployeeId = foo, it would return an extra row with nulls where they shouldn't be (columns from a table which was inner joined on) - if I did a where upper(EmployeeId) = foo then I would get the correct result. Obviously the EmployeeId value was sourced from the same cell in all rows matching the predicate - so it was clearly a bug.

I Just encountered same error. and build an if else condition to get those values :D

table 1

A ! B

null 35


‘if else’ condition :P

select
Case
WHEN a."Add" >= '0' THEN to_number(a."Add" - b."Cease" )

ELSE (b."Cease")*-1
End as "X"
from table 1

Answer

-35

Category:oracle Views:0 Time:2010-06-02
Tags: oracle plsql

Related post

  • Oracle hierarchical query on non-hierarchical data 2011-10-04

    I hava data in an Oracle table that is organized as a graph that can contain cycles (see example). CREATE TABLE T (parent INTEGER, child INTEGER) AS select 1 parent, 2 child from dual union all select 1 parent, 8 child from dual union all select 2 pa

  • Oracle Hierarchical Query Performance 2008-10-28

    We're looking at using Oracle Hierarchical queries to model potentially very large tree structures (potentially infinitely wide, and depth of 30+). My understanding is that hierarchal queries provide a method to write recursively joining SQL but they

  • Oracle Hierarchical query: how to include top-level parent 2009-05-21

    I have a hierarchical query to track a reporting structure. This almost works, except that it's not reporting the very top level node, probably because the top-level people "report" to themselves. The query is: select level, empid, parentid from user

  • Oracle parallel query - How to find out actual number of spawned processes? 2009-09-14

    I am using Oracle parallel query feature on a 10G 3-node RAC where each node is a 16-CPU machine. The question is, how can I see the actual number of Oracle processes spawned to execute the query on all 3 nodes? --------------Solutions------------- I

  • Oracle Hierarchical Query 2009-11-16

    Using Oracle 10g. I have two tables: User Parent ------------- 1 (null) 2 1 3 1 4 3 Permission User_ID ------------------- A 1 B 3 The values in the permissions table get inherited down to the children. I would like to write a single query that could

  • Oracle SELECT query: collapsing null values when pairing up dates 2010-05-20

    I have the following Oracle query: SELECT id, DECODE(state, 'Open', state_in, NULL) AS open_in, DECODE(state, 'Not Open', state_in, NULL) AS open_out, FROM ( SELECT id, CASE WHEN state = 'Open' THEN 'Open' ELSE 'Not Open' END AS state, TRUNC(state_ti

  • Spring+JPA+Hibernate+Oracle insert query showing but data not inserting into DB 2010-08-28

    Help to solve this issue: Spring+JPA+Hibernate+Oracle insert query showing but data not inserting into DB : One to One relationship, here query for One table is showing second table insert query not showing, also data not inserting into DB.

  • how to time an oracle select query 2010-11-17

    what is the best way to find out how much time an oracle select statement takes. I have the following query for which I want to find out the time, however, since this query brings four thousand records and it takes time to display those 4 thousand re

  • Oracle Select query help please 2011-07-21

    SELECT id FROM ( SELECT id FROM table WHERE PROCS_DT is null ORDER BY prty desc, cret_dt ) where rownum >0 and rownum <=100 The above query is giving me back 100 records as expected SELECT id FROM ( SELECT id FROM table WHERE PROCS_DT is null O

  • Oracle query: how can I elimitate multiple results from this query? 2011-08-25

    I have the following tables: CLIENT - ID - NAME - USER ORDER - ID - DATE - CLIENT_ID - USER ORDER_LINE - ORDER_ID - LINE_ID - USER The user field tracks the person who last modified the record. Additionally, each line item may be modified by a differ

  • Getting duplicates in my oracle select query 2012-04-26

    Following is my Oracle select query for the result, but I'm getting duplicates which I don`t need. I think it's hard to find for others,please give it a try. SELECT I.EID EID, I.WT Title, I.RID RID, I.FORMNAME STAGENAME, I.FORMS STATUS, I.INPT Projec

  • Oracle SELECT query: collapsing NULL values when pairing up dates for different fields 2010-06-23

    This question is very much like my previous question, but a bit more complicated. Rob van Wijk's answer worked perfectly for my other question, and I've been using that as a starting point. My problem now is that I am pivoting dates for different fie

  • SQL query problem when upgrading from SQL Server 2000 to SQL Server 2008 R2 2010-10-27

    I am currently upgrading a database server from SQL Server 2000 to SQL Server 2008 R2. One of my queries used to take under a second to run and now takes in excess of 3 minutes (running on faster a faster machine). I think I have located where it is

  • MySQL query in PHP gives obvious wrong result 2009-03-14

    I'm using PHP and PHPMyAdmin to create a small profile site. I'm giving members an ID number, based on which is the biggest number currently in the database, +1 I did 25 tests before I got the PHP script where I wanted it to be. I then deleted those

  • What does TOP 1 mean in an sql query? 2009-05-19

    What does TOP 1 mean in an sql query? SELECT TOP 1 RequestId FROM PublisherRequests --------------Solutions------------- The query in the example will return the first RequestID from the table PublisherRequests. The order of the results without an Or

  • mysql query works manually but returns no results when running from code 2009-05-31

    I use a very simple query with "Like" to find product by its name SELECT p_pid, p_name, p_cat FROM products WHERE p_sid=346 AND p_name LIKE 'product name here in utf-8 encoding, can be various languages' LIMIT 1 When I run this query from code with v

  • changing DTS(data transformation service) options in sql server 2000 through SQL Query Analyzer 2009-06-11

    How do i change DTS options in sql server 2000 through SQL Query Analyzer? Such options are source, destination, transformation columns, etc.. --------------Solutions------------- You can't edit a DTS package through Query Analyzer; completely differ

  • SQL Server 2000 vs SQL Server 2008 Query Performance 2009-11-20

    I'm working with a client who had a SQL Server 2008 converted from a SQL Server 2000 DB and one of the queries has quite dramatically increased in time since it was on SQL Server 2000. However, if I change the compatibility level to 2008 in the DB, t

  • LINQ Query Returning Multiple Copies Of First Result 2010-04-08

    I'm trying to figure out why a simple query in LINQ is returning odd results. I have a view defined in the database. It basically brings together several other tables and does some data munging. It really isn't anything special except for the fact th

Copyright (C) dskims.com, All Rights Reserved.

processed in 0.130 (s). 11 q(s)