How to matching this two Tables

[ancheswaroop]

Note:
format of data in LG3_DATE= 20060120(yyyymmdd)
but in IN_TRANSACTION_DATE= 012006(mmddyy)

Format of LG3_TIME=114921506063
Format of IN_TRANSACTION_AMOUNT=114921

Format of LG3_TRANSACTION_AMOUNT= '+00000000052.19'
Format of IN_TRANSACTION_AMOUNT= '000000052.19'

I have to match two tables(TEDCLZ.CA_AUTH_DLY_FILE and TEDCLZ.NEW_DAILY_AUTH_REC_IN)

select
TEDCLZ.CA_AUTH_DLY_FILE.LG3_DATE,
TEDCLZ.CA_AUTH_DLY_FILE.LG3_TIME,
TEDCLZ.CA_AUTH_DLY_FILE.LG3_TRANSACTION_AMOUNT
from
TEDCLZ.CA_AUTH_DLY_FILE
left join
TEDCLZ.NEW_DAILY_AUTH_REC_IN
on
TEDCLZ.CA_AUTH_DLY_FILE.LG3_DATE = TEDCLZ.NEW_DAILY_AUTH_REC_IN.IN_TRANSACTION_DATE
and TEDCLZ.CA_AUTH_DLY_FILE.LG3_TIME = TEDCLZ.NEW_DAILY_AUTH_REC_IN.IN_TRANSACTION_TIME
and TEDCLZ.CA_AUTH_DLY_FILE.LG3_TRANSACTION_AMOUNT = TEDCLZ.NEW_DAILY_AUTH_REC_IN.IN_TRANSACTION_AMOUNT
where TEDCLZ.CA_AUTH_DLY_FILE.BATCH_LOAD_NUMBER =1100
and TEDCLZ.NEW_DAILY_AUTH_REC_IN.BATCH_LOAD_NUMBER =1100

this quary is not running because


how to change the format to match this tables

 

[ancheswaroop]

To change date format i use this in select dtatement

substr(TEDCLZ.CA_AUTH_DLY_FILE.LG3_DATE,5,2) || substr(TEDCLZ.CA_AUTH_DLY_FILE.LG3_DATE,7,2) || substr(TEDCLZ.CA_AUTH_DLY_FILE.LG3_DATE,3,2))
as CHDATE,

but how to use this CHDATE

 

[blom0344]

My suggestion would be to build a view on one of the tables where you create new objects that match the format of the other table. Then join view to 2nd table.
This keeps things more manageble...

Ties Blom