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
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]
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;
[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]