Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

UNION returning extra empty rows 1

Status
Not open for further replies.

weberm

Programmer
Dec 23, 2002
240
US
I have an identical set of tables in two instances (we copied the one to the other as part of a database split project over last Summer/Fall). I modified a report which reads tables for unreconciled records so that it would check both tables by using UNION to the select statement but noticed that it returns an empty row. If I run the queries seperately, I am not pulling a row of NULLs from either table. Does anyone have any ideas about what would cause that to happen?

My query and results
Code:
      SELECT DISTINCT pmt_id,
                      pmt_process_source_an,
                      pmt_tran_auth_dt,
		      PMT_TRAN_NO,
                      PMT_CARD_HOLDER_NAME_AN,
                      pmt_total_tkt_am
                 FROM aalf.applications,
                      aalf.applicants,
                      aalf.payments,
                      aalf.fees
                WHERE APP_APL_ID = APL_ID (+)
                  AND pmt_id = fee_pmt_id
                  AND app_id = fee_app_id(+)
                  AND app_status_cd IN('A', 'E', 'D', 'L', 'N')
                  AND app_accepted_dt < SYSDATE - 2
                  AND pmt_type_cd != 'C'
                  AND pmt_doi_rct_no IS NULL
      UNION
      SELECT DISTINCT pmt_id,
                      pmt_process_source_an,  
                      pmt_tran_auth_dt,
		      PMT_TRAN_NO,
                      PMT_CARD_HOLDER_NAME_AN,
                      pmt_total_tkt_am
                 FROM aalf_applications_fccs,
                      aalf_applicants_fccs,
                      aalf_payments_fccs,
                      aalf_fees_fccs
                WHERE APP_APL_ID = APL_ID (+)
                  AND pmt_id = fee_pmt_id
                  AND app_id = fee_app_id(+)
                  AND app_status_cd IN('A', 'E', 'D', 'L', 'N')
                  AND app_accepted_dt < SYSDATE - 2
                  AND pmt_type_cd != 'C'
                  AND pmt_doi_rct_no IS NULL;
Here is the what I get from the query using UNION. The 'DFS-FACS' row came from the new table instance:
[tt]
PMT_ID PMT_PROCESS_SOURCE_AN PMT_TRAN_AUTH_DT PMT_TRAN_NO PMT_CARD_HOLDER_NAME_AN PMT_TOTAL_TKT_AM
715025 DOI-AALF 119
1275787 DOI-AALF 8/10/2007 8:12:25 PM 20560060 STUART BROOKS 58.95
1276180 DOI-AALF 10/5/2007 12:24:44 PM 177590 ASADF 57.95
1313300 DOI-AALF 11/7/2007 1:36:59 PM 178533 TEST 57.95
1313301 DOI-AALF 11/7/2007 1:58:34 PM 178534 TEST 57.95
1313302 DOI-AALF 11/7/2007 2:17:36 PM 178535 TEST 115.95
1313303 DOI-AALF 11/7/2007 2:32:13 PM 178536 TEST 115.95
1313304 DOI-AALF 11/7/2007 2:43:52 PM 178537 TEST 57.95
1313306 DOI-AALF 11/8/2007 8:49:41 AM 178542 TET 57.95
1313307 DOI-AALF 11/8/2007 11:15:12 AM 178544 TEST 57.95
1313308 DOI-AALF 11/8/2007 7:41:43 PM 178570 TEST 3096.23
1313309 DOI-AALF 11/9/2007 2:23:48 PM 178579 ASDF 115.95
1313310 DOI-AALF 11/9/2007 4:24:53 PM 178585 ASDF 57.95
1313319 DOI-AALF 11/19/2007 3:40:13 PM 178765 TEST 57.95
1313320 DOI-AALF 11/29/2007 9:18:02 AM 178880 ASDFASD 58.95
1313321 DOI-AALF 11/29/2007 9:30:49 AM 178881 ASDF 58.95
1313322 DOI-AALF 11/29/2007 11:32:02 AM 178888 ACCOUNT HOLDER NAME 58.95
1313349 DOI-AALF 12/14/2007 3:23:07 PM 179176 RAYLENE BLOCKER 115.95
1313350 DOI-AALF 12/20/2007 8:22:14 AM 179219 TEST 115.95
1313351 DOI-AALF 12/20/2007 8:30:24 AM 179220 TEST 57.95
1313352 DOI-AALF 12/20/2007 8:33:16 AM 179221 TEST 115.95
1313353 DOI-AALF 12/20/2007 8:36:37 AM 179222 TEST 57.95
1313359 DOI-AALF 1/2/2008 2:17:16 PM 179269 MICHELE 58.95
1313528 DOI-AALF 1/8/2008 8:20:09 AM 179290 TEST 115.95
1313529 DOI-AALF 1/8/2008 8:31:14 AM 179291 TEST 57.95
1313530 DOI-AALF 1/8/2008 8:36:18 AM 179292 TEST 58.95
1313617 DOI-AALF 1/14/2008 8:30:31 AM 179301 ASDFASDF 115.95
1313618 DOI-AALF 1/14/2008 10:55:32 AM 179302 TEST 57.95
1313619 DOI-AALF 1/14/2008 11:01:12 AM 179303 TEST 58.95
1313620 DOI-AALF 1/18/2008 1:35:18 PM 179318 ASDF 57.95
1314407 DOI-AALF 2/6/2008 8:15:29 AM 179466 ASDFASDF 57.95
1315406 DOI-AALF 2/22/2008 8:56:43 AM 179602 TEST 115.95
1315407 DOI-AALF 2/22/2008 10:00:48 AM 179603 TEST 57.95
1315408 DOI-AALF 2/22/2008 10:08:06 AM 179604 TEST 115.95
1315409 DOI-AALF 2/22/2008 11:39:45 AM 179605 TEST 115.95
1315410 DOI-AALF 2/22/2008 1:35:43 PM 179606 TEST 57.95
1315411 DOI-AALF 2/25/2008 2:25:12 PM 179611 JOHN SMITH 57.95
1315412 DOI-AALF 2/26/2008 9:30:56 AM 179614 JANE SMITH 57.95
1315413 DOI-AALF 2/26/2008 9:47:53 AM 179615 JANE SMITH 57.95
1275811 DFS-FACS 10/5/2007 11:42:04 AM 177588 NAGLE 27.95
<-- This is a row of NULLs[/tt]
 
Hello,

I can't imagine that a UNION should create an all null record...
Never saw something like that...
So may I ask, what exactly makes you believe so?
Maybe an extra linefeed, created by your query tool?
By the way, is it from sqlplus, or what else?

For verifying, could you please run this query:
select pmt_process_source_an, count(*) from
(
your whole query here
)
group by pmt_process_source_an

Let's see whether you'll get two or three rows...
 
I ran the query via TOAD and there's an empty row at the end but if I run each part seperately, neither has it. I ran it through SQL*PLUS and got the same results (41 rows, the last one empty).

Let me see what your idea yields. It could very well be bad data somewhere.
 
I'm begining to suspect there's some bad data out there, but I wonder why it only shows up when using UNION...
[tt]
PMT_PROC COUNT(*)
-------- ----------
DFS-FACS 1
DOI-AALF 39
1
[/tt]
 
[ponder]
This is not what I had expected....

For another test, could you please run the same select for the two parts of your query, without the UNION?
 
Even stranger. I ran it against the second part of the query in SQL*PLUS and it returned
[tt]
PMT_PROC COUNT(*)
-------- ----------
DFS-FACS 1
1
[/tt]
Apparently TOAD is suppressing that blank line (I did a row count on the data grid and sure enough, it says there are two records).
 
Sorry to arrive late to this thread...

The (+) operator creates a null row to "match" any otherwise-unmatched row in the non-(+) table. Then, if there are multiple all-NULL rows, they distill down to a single all-NULL row as a result of the DISTINCT behaviour of the UNION set operator.

Let us know if this resolves your anomaly.

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
 
Removing the Outer Joins (+) from the query seems to have done the trick. Thank you!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top