Select records from table one which are not match

[ancheswaroop]

Table 1: TRAN_ADJUSTMENT_REC
Table 2: DLY_TRAN_FILE

I am matching this two tables with this Query

select count(1)
from
TEDCLZ.TRAN_ADJUSTMENT_REC
INNER JOIN
TEDCLZ.DLY_TRAN_FILE
on
cast((REPLACE(substr(cast(date(cast(substr(TEDCLZ.DLY_TRAN_FILE.AT31_TRANSACTION_DATE,5,3) as int))as char(10)),6,5)||''||
substr(TEDCLZ.DLY_TRAN_FILE.AT31_TRANSACTION_DATE,1,4),'-','')) as char(10)) = TEDCLZ.TRAN_ADJUSTMENT_REC.TRANSACTION_DATE
AND cast((REPLACE(substr(cast(date(cast(substr(TEDCLZ.DLY_TRAN_FILE.AT31_DATE_POSTED,5,3) as int))as char(10)),6,5)||''||
substr(TEDCLZ.DLY_TRAN_FILE.AT31_DATE_POSTED,1,4),'-','')) as char(10)) = TEDCLZ.TRAN_ADJUSTMENT_REC.TRANSACTION_POST_DATE
AND substr(TEDCLZ.DLY_TRAN_FILE.AT31_AMT_TRANSACTION,2,14) = substr(TEDCLZ.TRAN_ADJUSTMENT_REC.TRANSACTION_AMOUNT,4,14)
AND TEDCLZ.DLY_TRAN_FILE.AT31_REFERENCE_NUMBER = TEDCLZ.TRAN_ADJUSTMENT_REC.TRANSACTION_REF_NUM
AND TEDCLZ.DLY_TRAN_FILE.AT31_TRANSACTION_CODE = TEDCLZ.TRAN_ADJUSTMENT_REC.TRAN_CODE
AND TEDCLZ.DLY_TRAN_FILE.HASH_SRC_CRCD_I = TEDCLZ.TRAN_ADJUSTMENT_REC.HASH_SRC_CRCD_I
WHERE TEDCLZ.TRAN_ADJUSTMENT_REC.TRANSACTION_POST_DATE='01112006'

No. of records in TRAN_ADJUSTMENT_REC( Table 1) is 13179
No. of records match are 13036
143 records are not matching

Now i want that 143 records from table 1 which are not matching to table 2
When i am using this query i am not getting any records ( query is running no error message)

select
TEDCLZ.TRAN_ADJUSTMENT_REC.TRANSACTION_DATE,
TEDCLZ.TRAN_ADJUSTMENT_REC.TRANSACTION_POST_DATE,
TEDCLZ.TRAN_ADJUSTMENT_REC.TRANSACTION_POST_DATE,
TEDCLZ.TRAN_ADJUSTMENT_REC.TRANSACTION_REF_NUM,
TEDCLZ.TRAN_ADJUSTMENT_REC.TRAN_CODE,
TEDCLZ.TRAN_ADJUSTMENT_REC.TCAT_CODE,
TEDCLZ.TRAN_ADJUSTMENT_REC.HASH_SRC_CRCD_I
from
TEDCLZ.TRAN_ADJUSTMENT_REC
left outer join
TEDCLZ.DLY_TRAN_FILE
on
cast((REPLACE(substr(cast(date(cast(substr(TEDCLZ.DLY_TRAN_FILE.AT31_TRANSACTION_DATE,5,3) as int))as char(10)),6,5)||''||
substr(TEDCLZ.DLY_TRAN_FILE.AT31_TRANSACTION_DATE,1,4),'-','')) as char(10)) = TEDCLZ.TRAN_ADJUSTMENT_REC.TRANSACTION_DATE
AND cast((REPLACE(substr(cast(date(cast(substr(TEDCLZ.DLY_TRAN_FILE.AT31_DATE_POSTED,5,3) as int))as char(10)),6,5)||''||
substr(TEDCLZ.DLY_TRAN_FILE.AT31_DATE_POSTED,1,4),'-','')) as char(10)) = TEDCLZ.TRAN_ADJUSTMENT_REC.TRANSACTION_POST_DATE
AND substr(TEDCLZ.DLY_TRAN_FILE.AT31_AMT_TRANSACTION,2,14) = substr(TEDCLZ.TRAN_ADJUSTMENT_REC.TRANSACTION_AMOUNT,4,14)
AND TEDCLZ.DLY_TRAN_FILE.AT31_REFERENCE_NUMBER = TEDCLZ.TRAN_ADJUSTMENT_REC.TRANSACTION_REF_NUM
AND TEDCLZ.DLY_TRAN_FILE.AT31_TRANSACTION_CODE = TEDCLZ.TRAN_ADJUSTMENT_REC.TRAN_CODE
AND TEDCLZ.DLY_TRAN_FILE.HASH_SRC_CRCD_I = TEDCLZ.TRAN_ADJUSTMENT_REC.HASH_SRC_CRCD_I
WHERE TEDCLZ.TRAN_ADJUSTMENT_REC.TRANSACTION_POST_DATE='01112006'
AND TEDCLZ.TRAN_ADJUSTMENT_REC.TRANSACTION_AMOUNT IS NULL

is this query is correct.(left outer join)

 

[blom0344]

Nope, this will not work. If you check on null as you do then you will not select non-existing records in the outer-join set. You better build a construction using subselect and 'not in'...

Ties Blom

 

[MarcLodge]

The very bottom of page 228 in Graeme Birchall's excellent cookbook discusses this type of thing and gives examples which may be helpful.

http://mysite.verizon.net/Graeme_Birchall/cookbook/DB2V82CK.PDF

Marc

 

[gregsimpson]

Not had time to look at the post in detail. Just thought it's worth mentioning there is an except operator to find results in one result set, which aren't present in another.

Additionally, there is am intersection operator, which finds the elements tha belong to two different result sets.

select gid from table1
intersect
select gid from table2

select gid from table1
intersect
select gid from table2

Cheers
Greg

 

[ddiamond]

Greg,

I looked through my DB2 documentation, but could not find any examples of the except operator. Can you give an example or link to an example of this operator?

- Dan

 

[gregsimpson]

Dan,

I have a typo in the above. I should have shown in my second example the word except, rather than intersect.

Some examples from the web

http://publib.boulder.ibm.com/infoc....etools.iseries.langref2.doc/rbafzmstmark.htm

http://www.thescripts.com/forum/thread182670.html

http://wwwdb.inf.tu-dresden.de/files/teaching/ibmcert/700/db2cert4v8-a4.pdf

Google for

db2 except intersect

you'll get lots of examples.

Cheers
Greg

 

[ddiamond]

Thanks Greg.