i can't run this

[ancheswaroop]

select
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,
TEDCLZ.CA_AUTH_DLY_FILE.LG3_TIME,
TEDCLZ.CA_AUTH_DLY_FILE.LG3_TRANSACTION_AMOUNT,
TEDCLZ.CA_AUTH_DLY_FILE.LG3_APPROVAL_CODE,
TEDCLZ.CA_AUTH_DLY_FILE.LG3_MERCHANT_ID,
TEDCLZ.CA_AUTH_DLY_FILE.HASH_SRC_CRCD_I
from
TEDCLZ.CA_AUTH_DLY_FILE
left join
TEDCLZ.NEW_DAILY_AUTH_REC_IN
on
CHDATE = 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
and TEDCLZ.CA_AUTH_DLY_FILE.LG3_APPROVAL_CODE = TEDCLZ.NEW_DAILY_AUTH_REC_IN.IN_APPROVAL_CODE
and TEDCLZ.CA_AUTH_DLY_FILE.LG3_MERCHANT_ID = TEDCLZ.NEW_DAILY_AUTH_REC_IN.IN_MERCHANT_ID
and TEDCLZ.CA_AUTH_DLY_FILE.HASH_SRC_CRCD_I = TEDCLZ.NEW_DAILY_AUTH_REC_IN.HASH_SRC_CRCD_I
and TEDCLZ.CA_AUTH_DLY_FILE.BATCH_LOAD_NUMBER = TEDCLZ.NEW_DAILY_AUTH_REC_IN.BATCH_LOAD_NUMBER
where TEDCLZ.CA_AUTH_DLY_FILE.BATCH_LOAD_NUMBER =1100
and TEDCLZ.NEW_DAILY_AUTH_REC_IN.BATCH_LOAD_NUMBER =1100

and this is the error message

DB2 SQL error: SQLCODE: -206, SQLSTATE: 42703, SQLERRMC: CHDATE
Message: "CHDATE" is not valid in the context where it is used. 

 

[ddiamond]

What is CHDATE? A field name or a function?

 

[ddiamond]

If CHDATE is a field, what is its data type? What is the data type of TEDCLZ.NEW_DAILY_AUTH_REC_IN.IN_TRANSACTION_TIME?

 

[MarcLodge]

Dan,
It looks to me as if CHDATE is a temporary column named defined from the 1st column built in the select.

Can you do a left join on a temporary column? I think this may be the problem.

Marc

 

[ddiamond]

Marc,

That sounds right. You cannot use any field aliases in the from clause or the were clause. There scope is limited to the select clause. The following might work:

select
  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,
  TEDCLZ.CA_AUTH_DLY_FILE.LG3_TIME,
  TEDCLZ.CA_AUTH_DLY_FILE.LG3_TRANSACTION_AMOUNT,
  TEDCLZ.CA_AUTH_DLY_FILE.LG3_APPROVAL_CODE,
  TEDCLZ.CA_AUTH_DLY_FILE.LG3_MERCHANT_ID,
  TEDCLZ.CA_AUTH_DLY_FILE.HASH_SRC_CRCD_I
from TEDCLZ.CA_AUTH_DLY_FILE
  left join TEDCLZ.NEW_DAILY_AUTH_REC_IN on
    (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)) = 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
    and TEDCLZ.CA_AUTH_DLY_FILE.LG3_APPROVAL_CODE = TEDCLZ.NEW_DAILY_AUTH_REC_IN.IN_APPROVAL_CODE
    and TEDCLZ.CA_AUTH_DLY_FILE.LG3_MERCHANT_ID = TEDCLZ.NEW_DAILY_AUTH_REC_IN.IN_MERCHANT_ID
    and TEDCLZ.CA_AUTH_DLY_FILE.HASH_SRC_CRCD_I = TEDCLZ.NEW_DAILY_AUTH_REC_IN.HASH_SRC_CRCD_I
    and TEDCLZ.CA_AUTH_DLY_FILE.BATCH_LOAD_NUMBER = TEDCLZ.NEW_DAILY_AUTH_REC_IN.BATCH_LOAD_NUMBER
where TEDCLZ.CA_AUTH_DLY_FILE.BATCH_LOAD_NUMBER =1100
  and TEDCLZ.NEW_DAILY_AUTH_REC_IN.BATCH_LOAD_NUMBER =1100