more than 10 joins

From: Selami Ozlu <selamiozlu_at_gmail.com>
Date: Tue, 27 Nov 2007 10:09:05 -0800 (PST)
Message-ID: <a75fe790-7c1a-41ef-849a-df1d1fe8f27e@s36g2000prg.googlegroups.com>

hello everbody,
i have the following query that gives me the error message "ORA- 1445 cannot select ROWID from.." is there any limitation on the number of joins in a query in oracle ?

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_SHIP_FROM Shipping_Port
,C00_SHIP_TO Destination_Port
,C00_SHPNG_DATELATE + 19000000 Latest_Date_of_Shipment
,C00_EXPR_AT_CITY Place_of_LC_Expiry
,CASE WHEN C00_PART_SHIPT_IND='Y' THEN 1 ELSE 2 END
Partial_Shipment
,CASE WHEN C00_TRANSSHIP_IND= 'Y' THEN 1 ELSE 2 END Trans_Shipment
,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) Ship_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'))

Received on Tue Nov 27 2007 - 12:09:05 CST