Hello Team,
Kindly advise on error below:
ORA-01722: invalid number
01722. 00000 - "invalid number"
*Cause: The specified number was invalid.
*Action: Specify a valid number.
script executed:
create table transfinal as select a.SEQ_ID "SEQid1",a.ACCOUNT_ID "ACCID1",a.CARD_ID "CARDID1",a.MASKED_PAN "PAN1",a.CARD_TYPE "CARDTYPE1",a.OBJ_ACC_ID "OBID1",a.OBJ_ACC_TYPE "OBACCTYPE1",
a.SHOP_ID "SHOPID1",a.POS_ID "POSID1",a.CURRENCY "CURRENCY1",
a.BILLING_CURRENCY "BILLCURR1",a.AMOUNT "AMT1",a.BILLING_AMOUNT "BILAMT1",a.CREATE_DATE "CDATE1",a.POST_DATE "POSDATE1",a.STATUS "STATS1",
a.ERROR_CODE "ERRCODE1",a.DC_IND "DCIND1",a.AUTH_CODE "AUTHCODE1",a.REF_NUM,a.DESCRIPTION DESC1,a.TOPUP_TYPE "TOPUPTYPE",
b.SEQ_ID "SEQ1",b.TRADE_NO "TRADE1",b.CREATE_DATE "CREATE1",
b.PARTNER_ID "PARTID",b.NOTIFY_URL "NOTURL",b.RETURN_URL "RETURNURL",
b.SUBJECT "SUBJET",b.BODY "BODY1",b.MER_TRADE_NO ,
b.CURRENCY "CURR",b.TOTAL_PRICE "TOTPRICE",b.TRADE_STATUS "TRADESTATUS",
b.ERROR_CODE "ERRCODE",b.NOTIFY_STATUS "NOTSTATUS",b.ACCOUNT_ID "ACCID",
b.MSISDN "MISIDB2",b.ENCRYPTED_PAN "ENCPAN",b.LANG "lANGUA",
b.POS_ID "POS_ID2",b.SHOP_ID "SHOPID",b.TXN_TYPE "TXNTYPE",
b.REFUND_STATUS "REFSTATUS",b.PAY_TYPE "PAYTYPE",
b.QR_CODE "QRCODE1",b.AUTH_CODE "AUTHCODE2",
b.FD_TXN_TIME "FDTXTIME",c.POS_ID "POSID3",c.POS_SERIAL "POSSERIAL",c.STATUS "STATUS3",c.MER_ID "MERID3",
c.MER_NAME_EN "MER_NAME_EN3",c.MER_NAME_NAT "MER_NAME_NAT3",c.SHOP_ID "SHOPID3",c.SHOP_NAME_EN "SHONAMEEN3",
c.SHOP_NAME_NAT "SHOPNAMENAT3",c.SIC "SIC3",c.CREATE_DATE "CREATEDATE3",
c.LAST_UPD_DATE "LASTUPDATE3",c.ADDR_LINE_1 "ADDRLINE1",c.ADDR_LINE_2 "ADDRLINE2",c.ADDR_LINE_3 "ADDRLINE3",
(select count(*) from psg_p2p_log d where d.ref_number=a.ref_num) "IN_P2P_LOG",
(select count(*) from psg_topup_log topup where topup.ref_number=a.ref_num) "IN_TOPUP_LOG",
(select count(*) from bank_tran_queue banktran where banktran.ref_num=a.ref_num) "IN_DIGITAL_IPS"
from psg_trans_history a left join psg_epay_trans_log b on (ref_num=mer_trade_no)
left join psg_pos_terminal c on b.pos_id=c.pos_id where a.status='S';
psg_pos_terminal:
POS_ID NUMBER
POS_SERIAL VARCHAR2(20 BYTE)
STATUS VARCHAR2(10 BYTE)
MER_ID VARCHAR2(19 BYTE)
MER_NAME_EN VARCHAR2(128 BYTE)
MER_NAME_NAT VARCHAR2(128 BYTE)
SHOP_ID VARCHAR2(19 BYTE)
SHOP_NAME_EN VARCHAR2(128 BYTE)
SHOP_NAME_NAT VARCHAR2(128 BYTE)
SIC VARCHAR2(4 BYTE)
CREATE_DATE DATE
LAST_UPD_DATE DATE
ADDR_LINE_1 VARCHAR2(256 BYTE)
ADDR_LINE_2 VARCHAR2(256 BYTE)
ADDR_LINE_3 VARCHAR2(256 BYTE)
PSG_EPAY_TRANS_LOG:
SEQ_ID NUMBER(11,0)
TRADE_NO VARCHAR2(16 BYTE)
CREATE_DATE DATE
PARTNER_ID VARCHAR2(19 BYTE)
NOTIFY_URL VARCHAR2(256 BYTE)
RETURN_URL VARCHAR2(256 BYTE)
SUBJECT VARCHAR2(256 BYTE)
BODY VARCHAR2(400 BYTE)
MER_TRADE_NO VARCHAR2(64 BYTE)
CURRENCY VARCHAR2(10 BYTE)
TOTAL_PRICE NUMBER(8,2)
TRADE_STATUS VARCHAR2(16 BYTE)
ERROR_CODE VARCHAR2(10 BYTE)
NOTIFY_STATUS VARCHAR2(15 BYTE)
ACCOUNT_ID NUMBER(8,0)
MSISDN VARCHAR2(20 BYTE)
ENCRYPTED_PAN VARCHAR2(48 BYTE)
LANG VARCHAR2(2 BYTE)
POS_ID VARCHAR2(20 BYTE)
SHOP_ID VARCHAR2(20 BYTE)
TXN_TYPE VARCHAR2(2 BYTE)
REFUND_STATUS VARCHAR2(4 BYTE)
PAY_TYPE VARCHAR2(20 BYTE)
QR_CODE VARCHAR2(200 BYTE)
AUTH_CODE VARCHAR2(10 BYTE)
FD_TXN_TIME VARCHAR2(10 BYTE)
PSG_TRANS_HISTORY:
SEQ_ID NUMBER
ACCOUNT_ID NUMBER
CARD_ID NUMBER
MASKED_PAN VARCHAR2(16 BYTE)
CARD_TYPE VARCHAR2(4 BYTE)
OBJ_ACC_ID VARCHAR2(20 BYTE)
OBJ_ACC_TYPE VARCHAR2(3 BYTE)
SHOP_ID VARCHAR2(19 BYTE)
POS_ID NUMBER
CURRENCY VARCHAR2(3 BYTE)
BILLING_CURRENCY VARCHAR2(3 BYTE)
AMOUNT NUMBER(10,2)
BILLING_AMOUNT NUMBER(10,2)
CREATE_DATE DATE
POST_DATE DATE
STATUS VARCHAR2(1 BYTE)
ERROR_CODE VARCHAR2(10 BYTE)
DC_IND VARCHAR2(2 BYTE)
AUTH_CODE VARCHAR2(6 BYTE)
REF_NUM VARCHAR2(128 BYTE)
DESCRIPTION VARCHAR2(128 BYTE)
TOPUP_TYPE VARCHAR2(4 BYTE)
Kindly advise.
Thanks,
Roshan