I AM GETTING THE FOLLOWIN ERROR MESSAGE:
"ORA-01445 cannot select from ROWID"
my query :
SELECT
-- L/C Application Information
SUBSTR(C00_CUST_DDA_ACCT,3,3) Branch
,SUBSTR(C00_CUST_DDA_ACCT,6,6) Customer_No
,SUBSTR(C00_CUST_DDA_ACCT,12,2) Currency
,CASE WHEN C00_LC_TYPE='I' THEN '4' WHEN C00_LC_TYPE='E' THEN '5'
ELSE ' ' END ACCNT_Type
,SUBSTR(C00_USER_XREFN,13,3) Serial_NO
-- L/C Status NOT COMPLETE
--,CASE WHEN LNI_TRN_NO_NUM IS NULL THEN '4' WHEN
LNI_TRN_NO_NUM<>'' THEN '5' ELSE '' END LC_Status
,(SELECT MAX(TO_NUMBER(C01_TYPE_SEQ)) FROM LOLOC01 WHERE
LOLOC01.C01_GROUP = LOLOC00.C00_GROUP AND
LOLOC01.C01_NUMBER=LOLOC00.C00_NUMBER) Number_Of_Amd_Times
,CASE WHEN C00_LC_TYPE='I' THEN '1' WHEN C00_LC_TYPE='E' THEN '2'
ELSE ' ' END LC_Type
,C00_ISSUE_DATE + 19000000 Year
,'723' GL_NO
,SUBSTR(C00_USER_XREFN,13,3) Incmoning_Doc_NO
,C00_REVOCABLE_IND Revocation_Code
,C50_CONF_INSTR_OR Confirmation_Code
,CASE WHEN C00_STAND_BY_IND = 'Y' THEN '2' ELSE '1' END
Transfer_Code
,C00_REVOLVE_TYPE Revolving_Type
,C50_AVAIL_BY Method_of_Payment
,C00_TENORCODE_LATE Delivery_Terms_Code
,CASE WHEN C00_ABOUT_MODIFER='Y' THEN 10 ELSE 0 END
About_Percentage
,CASE WHEN C00_ABOUT_MODIFER <>'Y' THEN C00_PCT_MODIFER ELSE 0 END
Percentage_of_Tolerance_AMT
,C00_CASHCL_BASE_BL LC_Sight_AMT
,C00_CASHCL_ORIGAMT LC_Deferred_AMT
,C00_BOOK_EXCH_RATE Central_Bank_Rate
,C00_CONF_OPEN_AMT LC_Sight_Equivalent_AMT
,C00_UNCNF_OPEN_AMT LC_Deferred_Equivalent_AMT
,C00_CONF_ORIG_PCT Sight_Margin_Percentage
,C00_CASH_COLL_PCT Deferred_Margin_Percentage
,C00_CASHCL_DB_DDA Customer_Margin_Account
,C00_CASH_COLL_CURR Cash_Margin_Currency
,C00_CASHCL_ORIGAMT Sight_Margin_AMT
,C00_CASHCL_ORIG_BS Sight_Margin_Eqi_AMT
,C00_CASH_COLL_BAL Deferred_Margin_AMT
,C00_CASHCL_BASE_BL Deferred_Margin_Eqi_AMT
,C00_COMM_DB_DDA Customer_Acc_NO_for_Changes
,C00_COMM_PERD_TYPE Iss_Comm_Type
,C05_COMM_RT_OVRD Iss_Comm_Percentage
,C05_ACCEPT_EXCH_RT Draft_Comm_Percentage
,C01_ACCT_EXCH_RATE Exchange_Comm_Percentage
,C02_INTEREST_RATE Interest_Percentage
,C05_STD_COMM_AMT Comm_AMT
,C05_ACCEPT_BAL Draft_Comm_AMT
,C00_CABL_CHGS_AMT Telex_AMT
,CASE WHEN EXISTS(SELECT DP3_REF_NUMBER FROM LDFPY03 WHERE
DP3_REF_NUMBER=C00_GROUP || C00_NUMBER AND DP3_APPL_TYPE='LN' AND
(DP3_CHRG_MNEM_CD_1='?' OR DP3_CHRG_MNEM_CD_2='?' OR
DP3_CHRG_MNEM_CD_3='?' OR DP3_CHRG_MNEM_CD_4='?')) THEN
TRIM(TO_CHAR((SELECT SUM(CASE WHEN DP3_CHRG_MNEM_CD_1='?'
THEN DP3_CHRG_AMT_1
WHEN DP3_CHRG_MNEM_CD_2='?'
THEN DP3_CHRG_AMT_2
WHEN DP3_CHRG_MNEM_CD_3='?'
THEN DP3_CHRG_AMT_3
WHEN DP3_CHRG_MNEM_CD_4='?'
THEN DP3_CHRG_AMT_4
ELSE 0 END)
FROM LDFPY03 WHERE DP3_REF_NUMBER=C00_GROUP ||
C00_NUMBER AND DP3_APPL_TYPE='LN'),'99999999990.000'))
ELSE ' ' END Stamp_AMT
,C05_COMM_AMT Exc_Comm_AMT
,XL5_AP_ACC_INT_RAT Interest_AMT
,C00_ISSUE_DATE + 19000000 Date_of_Iss
,C00_EXPR_DATE_ORIG + 19000000 Date_of_Expiry
,C00_****P_FROM ****pping_****t
,C00_****P_TO Destination_****t
,C00_SHPNG_DATELATE + 19000000 Latest_Date_of_****pment
,C00_EXPR_AT_CITY Place_of_LC_Expiry
,CASE WHEN C00_PART_****PT_IND='Y' THEN 1 ELSE 2 END
Partial_****pment
,CASE WHEN C00_TRANS****P_IND= 'Y' THEN 1 ELSE 2 END Trans_****pment
,C01_CUST_DDA_ACCT Drawee_NO
--,SUBSTR(XTX_LINE_ARRAY,1170,2) Insurance_Policy_Type
--,SUBSTR(XTX_LINE_ARRAY,1300,15) Insurance_Policy_NO
--,SUBSTR(XTX_LINE_ARRAY,1430,9) Insurance_Policy_ISSDate
--,SUTSTR(XTX_LINE_ARRAY,1235,3) Insurance_Policy_CURR_Code
--,SUBSTR(XTX_LINE_ARRAY,1495,14) Insurance_Policy_AMT
--,SUBSTR(XTX_LINE_ARRAY,1365,33) ****p_or_Airline_Name
,0 Reference_PreAdvice
,0 Reimbursing_Banks_Charges
-- the following 10 columns have to be reviewed!!!
,CASE WHEN(C00_TENORCODE_ORIG ='' AND C00_TENORDAYS_ORIG ='' AND
C00_TENORDATE_ORIG = '') THEN 'SIGHT' ELSE 'USANCE' END
LC_Sight_Balance
,CASE WHEN(C00_TENORCODE_ORIG ='' AND C00_TENORDAYS_ORIG ='' AND
C00_TENORDATE_ORIG = '') THEN 'SIGHT' ELSE 'USANCE' END
LC_Sight_Balance_JD
,CASE WHEN(C00_TENORCODE_ORIG ='' AND C00_TENORDAYS_ORIG ='' AND
C00_TENORDATE_ORIG = '') THEN 'SIGHT' ELSE 'USANCE' END
LC_Deferred_Balance
,CASE WHEN(C00_TENORCODE_ORIG ='' AND C00_TENORDAYS_ORIG ='' AND
C00_TENORDATE_ORIG = '') THEN 'SIGHT' ELSE 'USANCE' END
LC_Deferred_Balance_JD
,CASE WHEN(C00_TENORCODE_ORIG ='' AND C00_TENORDAYS_ORIG ='' AND
C00_TENORDATE_ORIG = '') THEN 'SIGHT' ELSE 'USANCE' END
LC_Sight_Margin_Balance
,CASE WHEN(C00_TENORCODE_ORIG ='' AND C00_TENORDAYS_ORIG ='' AND
C00_TENORDATE_ORIG = '') THEN 'SIGHT' ELSE 'USANCE' END
LC_Deferred_Margin_Balance
,CASE WHEN(C00_TENORCODE_ORIG ='' AND C00_TENORDAYS_ORIG ='' AND
C00_TENORDATE_ORIG = '') THEN 'SIGHT' ELSE 'USANCE' END
LC_Deferred_Margin_Balance_BS
,CASE WHEN(C00_TENORCODE_ORIG ='' AND C00_TENORDAYS_ORIG ='' AND
C00_TENORDATE_ORIG = '') THEN 'SIGHT' ELSE 'USANCE' END
Accep_Guar_Draft_Balance
,CASE WHEN(C00_TENORCODE_ORIG ='' AND C00_TENORDAYS_ORIG ='' AND
C00_TENORDATE_ORIG = '') THEN 'SIGHT' ELSE 'USANCE' END
Accep_Guar_Uncoll_Draft_BL
,CASE WHEN(C00_TENORCODE_ORIG ='' AND C00_TENORDAYS_ORIG ='' AND
C00_TENORDATE_ORIG = '') THEN 'SIGHT' ELSE 'USANCE' END
Doc_Over_Draft_Balance
,C00_SHRT_MERCHDESC Brief_Goods_Desc
,LGA_ENTRY_DATE + 19000000 App_Entry_Date
,LGA_ENTRY_TIME App_Entry_time
,LGA_ENTRY_DATE + 19000000 App_Modi_Date
,LGA_ENTRY_TIME App_Modi_time
,C02_RELEASE_DATE + 19000000 Settelment_Date
-- Date_of_PreAdvice
,C00_CURRENCY_CODE Doc_OverDrft_Cur_Code
,C46_BENE_NM_1 Beneficiary_Name1
,C46_BENE_NM_2 Beneficiary_Name2
,C46_BENE_NM_3 Beneficiary_Address1
,C46_BENE_NM_4 Beneficiary_Address2
,C50_AV_W_NAD_ORG_1 Available_WithBy1
,C50_AV_W_NAD_ORG_2 Available_WithBy2
,C50_AV_W_NAD_ORG_3 Available_With_By1
,C50_AV_W_NAD_ORG_4 Available_With_By2
,CASE WHEN C00_LC_TYPE='I' THEN C46_ACT_NM_1 WHEN C00_LC_TYPE='E'
THEN C46_BENE_NM_1 ELSE ' ' END Company_Name1
,CASE WHEN C00_LC_TYPE='I' THEN C46_ACT_NM_2 WHEN C00_LC_TYPE='E'
THEN C46_BENE_NM_2 ELSE ' ' END Company_Name2
,CASE WHEN C00_LC_TYPE='I' THEN C46_ACT_NM_1 WHEN C00_LC_TYPE='E'
THEN C46_BENE_NM_1 ELSE ' ' END Company_Address1
,CASE WHEN C00_LC_TYPE='I' THEN C46_ACT_NM_2 WHEN C00_LC_TYPE='E'
THEN C46_BENE_NM_2 ELSE ' ' END Company_Address2
--,C06_NUMBER Iss_Open_Bank_No1
--,C06_NAME_ADDR_1 Iss_Open_Bank_Name1
--,C06_NAME_ADDR_2 Iss_Open_Bank_Name2
--,C06_NAME_ADDR_3 Iss_Open_Bank_Address1
--,C06_NAME_ADDR_4 Iss_Open_Bank_Address2
,C50_AD_T_ACRN_LAT Advise_Bank_No1
,C50_AD_T_NAD_LAT_1 Advise_Bank_Name1
,C50_AD_T_NAD_LAT_2 Advise_Bank_Name2
,C50_AD_T_NAD_LAT_3 Advise_Bank_Address1
,C50_AD_T_NAD_LAT_4 Advise_Bank_Address2
,C50_AD_T_ACRN_ORG Reim_Cover_Bank_No1
,C50_AD_T_NAD_ORG_1 Reim_Cover_Bank_Name1
,C50_AD_T_NAD_ORG_2 Reim_Cover_Bank_Name2
,C50_AD_T_NAD_ORG_3 Reim_Cover_Bank_Address1
,C50_AD_T_NAD_ORG_4 Reim_Cover_Bank_Address2
,C50_AV_W_ACRN_ORG Confirming_Bank_No
,C50_AV_W_NAD_ORG_1 Confirming_Bank_Name1
,C50_AV_W_NAD_ORG_2 Confirming_Bank_Name2
,C50_AV_W_NAD_ORG_3 Confirming_Bank_Address1
,C50_AV_W_NAD_ORG_4 Confirming_Bank_Address2
,C50_AV_W_ACRN_ORG Paying_Bank_No
,C50_AV_W_NAD_ORG_1 Paying_Bank_Name1
,C50_AV_W_NAD_ORG_2 Paying_Bank_Name2
,C50_AV_W_NAD_ORG_3 Paying_Bank_Address1
,C50_AV_W_NAD_ORG_4 Paying_Bank_Address2
,C50_AV_W_ACRN_ORG Accepting_Bank_No
,C50_AV_W_NAD_ORG_1 Accepting_Bank_Name1
,C50_AV_W_NAD_ORG_2 Accepting_Bank_Name2
,C50_AV_W_NAD_ORG_3 Accepting_Bank_Address1
,C50_AV_W_NAD_ORG_4 Accepting_Bank_Address2
,C50_AV_W_ACRN_ORG Drawee_Bank_No
,C50_AV_W_NAD_ORG_1 Drawee_Bank_Name1
,C50_AV_W_NAD_ORG_2 Drawee_Bank_Name2
,C50_AV_W_NAD_ORG_3 Drawee_Bank_Address1
,C50_AV_W_NAD_ORG_4 Drawee_Bank_Address2
,CASE WHEN C00_LC_TYPE='I' THEN C46_ACT_NM_1 WHEN C00_LC_TYPE='E'
THEN C46_BENE_NM_1 ELSE ' ' END Application_Name1
,CASE WHEN C00_LC_TYPE='I' THEN C46_ACT_NM_2 WHEN C00_LC_TYPE='E'
THEN C46_BENE_NM_2 ELSE ' ' END Application_Name2
,CASE WHEN C00_LC_TYPE='I' THEN C46_ACT_NM_1 WHEN C00_LC_TYPE='E'
THEN C46_BENE_NM_1 ELSE ' ' END Application_Address1
,CASE WHEN C00_LC_TYPE='I' THEN C46_ACT_NM_2 WHEN C00_LC_TYPE='E'
THEN C46_BENE_NM_2 ELSE ' ' END Application_Address2
-- LC_About_Sight_BL
-- LC_About_Deferred_BL
,C00_COLL_TYPE Colateral_Type
-- the following 2 columns have to be reviewed!!!
,CASE WHEN(C00_TENORCODE_ORIG ='' AND C00_TENORDAYS_ORIG ='' AND
C00_TENORDATE_ORIG = '') THEN 'SIGHT' ELSE 'USANCE' END
Doc_OverD_Under_Settl
,CASE WHEN(C00_TENORCODE_ORIG ='' AND C00_TENORDAYS_ORIG ='' AND
C00_TENORDATE_ORIG = '') THEN 'SIGHT' ELSE 'USANCE' END
AG_Draft_Under_Settl
--,SUBSTR(XTX_LINE_ARRAY,1040,2) Insurance_Company_Code
,C60_DRAFT_AMT_BASE Draft_Margin_AMT
,C60_DRAFT_AMT_FGN Draft_Margin_AMT_JD
,C43_PY_VESSEL_NAME Vessel_Airline_Name
-----,SELECT C43_PY_VESSEL_NAME FROM LOLOC43 WHERE
C43_GROUP=C00_GROUP AND C43_NUMBER=C00_NUMBER Vessel_Airline_Name
,C00_SHPNG_DATELATE + 19000000 Boundary_Load_Date
,C05_ACCEPTANCE_AMT Doc_Draft_AMT
,C05_OFFICER_CD Credit_Officer_NO
--,SUBSTR(XTX_LINE_ARRAY,2925,12) Deal_NO
--,SUBSTR(XTX_LINE_ARRAY,2860,2) Good_Arrival_Place
-- Transfer_Comm_Percentage
,C02_PAY_COMM_RATE Covering_Comm_Percentage
,SUBSTR(C05_OFFICER_CD,1,3) Credit_Centre
,C01_COMM_AMT_OVERR Comm_Amendment_AMT
,C01_CAB_CHGS_AMT Telex_Charges_AMT
,C01_AMDADJ_REL_DAT + 19000000 Entry_Date
,C01_COMM_AMT_OVERR Amend_Comm_AMT
,C01_CAB_CHGS_AMT Telex__AMT
,C01_AMDADJ_REL_DAT + 19000000 Entry__Date
,C01_COMM_AMT_OVERR LC_Comm_Amend_AMT
,C01_AMDADJ_REL_DAT + 19000000 Entry___Date
FROM
LOLOC00
LEFT JOIN LOLOC01 ON LOLOC01.C01_GROUP = LOLOC00.C00_GROUP AND
LOLOC01.C01_NUMBER=LOLOC00.C00_NUMBER
--AND C00_TYPE_SEQ=C01_TYPE_SEQ
LEFT JOIN LOLOC50 ON LOLOC50.C50_GROUP = LOLOC00.C00_GROUP AND
LOLOC50.C50_NUMBER=LOLOC00.C00_NUMBER
LEFT JOIN LOLOC05 ON LOLOC05.C05_GROUP = LOLOC00.C00_GROUP AND
LOLOC05.C05_NUMBER=LOLOC00.C00_NUMBER AND C00_TYPE_SEQ=C05_TYPE_SEQ
LEFT JOIN LOLOC02 ON LOLOC02.C02_GROUP = LOLOC00.C00_GROUP AND
LOLOC02.C02_NUMBER=LOLOC00.C00_NUMBER AND C00_TYPE_SEQ=C02_TYPE_SEQ
LEFT JOIN LLOGA ON LLOGA.LGA_PREFIX = LOLOC00.C00_GROUP AND
LLOGA.LGA_NUMB=LOLOC00.C00_NUMBER AND TO_NUMBER(C00_TYPE_SEQ)=LGA_SEQ
LEFT JOIN LOLOC46 ON LOLOC46.C46_GROUP = LOLOC00.C00_GROUP AND
LOLOC46.C46_NUMBER=LOLOC00.C00_NUMBER AND C00_TYPE_SEQ=C46_TYPE_SEQ
LEFT JOIN LOLOC60 ON LOLOC60.C60_GROUP = LOLOC00.C00_GROUP AND
LOLOC60.C60_NUMBER=LOLOC00.C00_NUMBER AND C00_TYPE_SEQ=C60_TYPE_SEQ
LEFT JOIN LOLOC43 ON LOLOC43.C43_GROUP = LOLOC00.C00_GROUP AND
LOLOC43.C43_NUMBER=LOLOC00.C00_NUMBER AND C00_TYPE_SEQ=C43_TYPE_SEQ
--LEFT JOIN LOLOC06 ON LOLOC06.C06_GROUP = LOLOC00.C00_GROUP AND
LOLOC06.C06_NUMBER=LOLOC00.C00_NUMBER
--LEFT JOIN LLNIS05 ON LNI_TRN_NUMBER=C00_GROUP || C00_NUMBER AND
LNI_TRN_SEQ_NO=TO_NUMBER(C00_TYPE_SEQ) AND LNI_SYSTEM_IND='L'
--LEFT JOIN LTXTV ON LTXTV.XTX_LC_NUMBER = (C00_GROUP ||
C00_NUMBER) AND TX0_DOC_TYPE='K' AND TX0_SOURCE_IND='EI'
WHERE
-- C00_RELEASE_CODE_2>299 AND
C01_AMEND_ADJ_IND='N' AND C00_LC_TYPE IN ('I','E')
-- AND NUM2DATE(C01_AMDADJ_REL_DAT) BETWEEN TRUNC(SYSDATE,'MONTH')
AND LAST_DAY(TRUNC(SYSDATE,'MONTH'))
|
