Archives of the TeradataForum

Message Posted: Thu, 27 Apr 2006 @ 18:50:08 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi All,

Need help from Sql experts...We want the output from the following query which uses lot og views as follows..

     select
     date-1 as dbs_business_dt,
     v_party_active_customer.dept_branch_cd,
     v_party_active_customer.customer_cin,
     v_party_active_customer.full_name,
     v_party_active_customer.party_type,
     v_sic.sic_cd,
     v_sic.sic_desc,
     v_party_active_customer.crr_rating,
     v_agmt_active_acct.product_id,
     v_agmt_balance.currency_cd,
     v_agmt_balance.dbs_agmt_hist_amt,
     v_exchange_rate.source_to_global_currency_rate
     from
     v_party_active_customer,   -->Big view
     DDCBIN_VBDW.v_sic,
     v_agmt_active_acct,  --> Big view
     v_agmt_balance,
     v_exchange_rate
     where
     v_party_active_customer.sic_code=v_sic.sic_cd
     and
     v_party_active_customer.customer_party_id=v_agmt_active_acct.accounthold
     er_party_id
     and v_agmt_active_acct.account_num=v_agmt_balance.account_num
     and
     v_agmt_active_acct.account_modifier_num=v_agmt_balance.account_modifier_
     num
     and v_exchange_rate.source_currency_cd=v_agmt_balance.currency_cd
     and v_exchange_rate.global_currency_cd='INR'
     and v_party_active_customer.party_type='Corporate'

     ------------------------------------------------------------------------
     ------------------------------------------------------------------------
     ---------
     View defination for v_party_active_customer,   -->Big view
     REPLACE VIEW DD_MART_CBIN_MIS.V_PARTY_ACTIVE_CUSTOMER AS
     SELECT
     P.Party_Id as Customer_Party_Id,
     PT.Party_Type_Desc as Party_Type,
     G.Gender_Type_Desc as Gender,
     P.Party_Start_Dt as Customer_Party_Start_Dt,
     P.Party_Host_Num as Customer_CIN,
     CASE
     WHEN P.Party_Type_Cd = 'INDV'
     AND INH.Name_Type_cd='FULL'
     THEN 'FULL NAME'
     WHEN P.Party_Type_Cd = 'INDV'
     AND INH.Name_Type_cd='SHORT'
     THEN 'SHORT NAME'
     END FULL_NAME,
     CASE
     WHEN P.Party_Type_Cd = 'ORG'
     AND ONH.Name_Type_cd='FULL'
     THEN 'FULL NAME'
     WHEN P.Party_Type_Cd = 'ORG'
     AND ONH.Name_Type_cd='SHORT'
     THEN 'SHORT NAME'
     END SHORT_NAME,
     PCR.Credit_Rating_Cd as CRR_Rating,
     PCR.Credit_Report_Dt as CRR_Rating_Dt,
     BSH.SIC_Cd as SIC_Code,
     INDV.Given_Name as Account_Officer ,
     INDV1.party_host_num as Customer_Owner,
     INDV2.party_host_num as Dept_Branch_Cd,
     P.Party_Host_Num as DBS_Legal_Entity,
     DSS.DBS_Source_System_Id,
     PPCA.Party_Class_Value_Cd as Affliate_code
     FROM DDCBIN_VBDW.V_PARTY P
     JOIN DDCBIN_VBDW.V_DBS_ETL_BMAP_PARTY DEBP
     ON DEBP.SRC_CD in ('0811','0820')
     LEFT OUTER JOIN DDCBIN_VBDW.V_DBS_SOURCE_SYSTEM DSS
     ON P.DBS_Source_System_Id = DSS.DBS_Source_System_Id
     AND P.Customer_Prospect_Ind = 'Y'
     AND P.Customer_Status_Cd = 'Active'
     AND P.DBS_Source_System_Id like any ('301%','302%')
     OR P.DBS_Source_System_Id = '13700'
     LEFT OUTER JOIN DDCBIN_VBDW.V_PARTY_TYPE PT
     ON P.Party_Type_Cd=PT.Party_Type_Cd
     AND P.DBS_Start_Dt =(SELECT MAX(P1.DBS_Start_Dt) FROM
     DDCBIN_VBDW.V_PARTY P1
     WHERE P.DBS_Legal_Entity_Party_Id = P1.Party_Id
     AND P1.Customer_Prospect_Ind = 'Y'
     AND P1.Customer_Status_Cd = 'Active'
     )

     LEFT OUTER JOIN
     (
     sel individual_party_id ,Gender_Type_Desc from DDCBIN_VBDW.V_GENDER_TYPE
     G1,DDCBIN_VBDW.V_individual I
     where I.gender_type_cd=G1.gender_type_cd ) G
     ON G.INDIVIDUAL_PARTY_ID=P.PARTY_ID
     LEFT OUTER JOIN DDCBIN_VBDW.V_INDIVIDUAL_NAME_HIST INH
     ON
     P.PARTY_ID =INH.Individual_Party_Id
     AND
     INH.Individual_Name_Start_Dt=(SELECT MAX(INH1.Individual_Name_Start_Dt)
     FROM DDCBIN_VBDW.V_INDIVIDUAL_NAME_HIST INH1
     WHERE INH.Individual_Party_Id= INH1.Individual_Party_Id
     AND INH.Name_Type_Cd=INH1.Name_Type_Cd
     )
     LEFT OUTER JOIN DDCBIN_VBDW.V_ORGANIZATION_NAME_HIST ONH
     ON
     P.PARTY_ID = ONH.ORG_PARTY_ID
     AND ONH.Org_Name_Start_Dt = (SELECT MAX(ONH1.Org_Name_Start_Dt) FROM
     DDCBIN_VBDW.V_ORGANIZATION_NAME_HIST ONH1
     WHERE ONH.Org_Party_Id=ONH1.Org_Party_Id )
     LEFT OUTER JOIN DDCBIN_VBDW.V_PARTY_CREDIT_RATING PCR
     ON P.PARTY_ID = PCR.Related_Party_Id
     AND PCR.Credit_Rating_Category_Cd = '4'
     AND PCR.Credit_Report_Dt = (SELECT MAX(PCR1.Credit_Report_Dt) FROM
     DDCBIN_VBDW.V_PARTY_CREDIT_RATING PCR1
     WHERE PCR.Related_Party_Id=PCR1.Related_Party_Id
     AND PCR.Reporting_Party_Id=PCR1.Reporting_Party_Id
     AND PCR.Credit_Rating_Category_Cd = PCR1.Credit_Rating_Category_Cd )
     LEFT OUTER JOIN DDCBIN_VBDW.V_Business_SIC_Hist BSH
     ON P.PARTY_ID = BSH.Business_Party_Id
     AND BSH.Business_SIC_End_Dt = (SELECT MAX(BSH1.Business_SIC_End_Dt )
     FROM DDCBIN_VBDW.V_Business_SIC_Hist BSH1
     WHERE BSH.Business_Party_Id=BSH1.Business_Party_Id
     AND BSH.SIC_Cd=BSH1.SIC_Cd)
     LEFT OUTER JOIN
     (
     sel individual_party_id ,Given_Name from
     DDCBIN_VBDW.V_PARTY_PARTY_RELATIONSHIP_HIS
     PPRH,DDCBIN_VBDW.V_INDIVIDUAL_NAME_HIST INH
     where
     PPRH.Party_Relationship_Start_Dttm =(SELECT
     MAX(PPRH1.Party_Relationship_Start_Dttm)
     FROM DDCBIN_VBDW.V_PARTY_PARTY_RELATIONSHIP_HIS PPRH1
     where pprh.relates_party_id=pprh1.relates_party_id
     and pprh.related_party_id=pprh1.related_party_id
     and pprh.Party_Relationship_Role_Cd=pprh1.Party_Relationship_Role_Cd )
     AND PPRH.Party_RelationshiP_Role_Cd = 'ACOFFCUS'
     and INH.individual_party_id=PPRH.relates_party_id
     and INH.Individual_Name_Start_Dt =(sel
     max(INH1.Individual_Name_Start_Dt) from
     DDCBIN_VBDW.V_INDIVIDUAL_NAME_HIST INH1
     where INH.individual_party_id= INH1.individual_party_id and
     INH.Name_Type_Cd = INH1.Name_Type_Cd )
     and INH.Name_Type_Cd = 'FULL') INDV
     ON INDV.Individual_party_id=P.party_id


     LEFT OUTER JOIN
     (
     sel p1.party_id,p1.party_host_num from
     DDCBIN_VBDW.V_PARTY_PARTY_RELATIONSHIP_HIS PPRH,DDCBIN_VBDW.V_party p1
     where
     PPRH.Party_Relationship_Start_Dttm =(SELECT
     MAX(PPRH2.Party_Relationship_Start_Dttm)
     FROM DDCBIN_VBDW.V_PARTY_PARTY_RELATIONSHIP_HIS PPRH2
     where p1.party_id=PPRH.related_party_id
     and pprh.related_party_id=pprh2.related_party_id
     and pprh.Party_Relationship_Role_Cd=pprh2.Party_Relationship_Role_Cd )
     AND PPRH.Party_RelationshiP_Role_Cd = 'ACCTCUS' ) INDV1
     ON INDV1.party_id=P.party_id

     LEFT OUTER JOIN
     (
     sel p2.party_id,p2.party_host_num from
     DDCBIN_VBDW.V_PARTY_PARTY_RELATIONSHIP_HIS PPRH,DDCBIN_VBDW.V_party p2
     where
     PPRH.Party_Relationship_Start_Dttm =(SELECT
     MAX(PPRH3.Party_Relationship_Start_Dttm)
     FROM DDCBIN_VBDW.V_PARTY_PARTY_RELATIONSHIP_HIS PPRH3
     where p2.party_id=PPRH.related_party_id
     and pprh.related_party_id=pprh3.related_party_id
     and pprh.Party_Relationship_Role_Cd=pprh3.Party_Relationship_Role_Cd )
     AND PPRH.Party_RelationshiP_Role_Cd = 'BRHCUS' ) INDV2
     ON INDV2.party_id=P.party_id
     LEFT OUTER JOIN DDCBIN_VBDW.V_PARTY_PARTY_CLASS_ASSOC PPCA
     ON P.Party_Id = PPCA.Party_Id
     AND PPCA.Party_Classification_Cd = '73'
     AND PPCA.Party_Class_Start_Dt =(SELECT MAX(PPCA1.Party_Class_Start_Dt )
     FROM DDCBIN_VBDW.V_PARTY_PARTY_CLASS_ASSOC PPCA1
     WHERE PPCA.Party_Id = PPCA1.Party_Id
     AND PPCA.Party_Classification_Cd = PPCA1.Party_Classification_Cd
     );
     -------------------------------
     view defination  for v_agmt_active_acct,  --> Big view
     REPLACE VIEW DD_MART_CBIN_MIS.V_AGMT_ACTIVE_ACCT AS
     SELECT
     AGR.Account_Num,
     AGR.Account_Modifier_Num,
     DAIH.DBS_Acct_Id_Num as Account_Identification_Number,
     AGR.Acct_Status_Type_Cd,
     AGR.Campaign_Id,
     AGR.Product_Id,
     substr(AGR.Product_Id,10,2) as Source_Product_Cd,
     AC.Currency_Cd,
     P.Product_Name,
     AGR.Account_Open_Dt,
     AGR.Account_Close_Dt,
     AGR.Account_Signed_Dt,
     AGR.contract_name as Account_Name,
     AGR.Contract_Expiration_Dt as Account_Expiry_Dt,
     LRH.Account_Review_Dt as Next_Interest_Review_Date,
     AGR.DBS_Start_Dt,
     AGR.DBS_End_Dt,
     AGR.DBS_Source_System_Id,
     VPAC.Customer_Party_Id as Accountholder_Party_Id,
     AP.Party_Id as Branch_of_Account,
     AGR.DBS_Legal_Entity_Party_Id as DBS_Legal_Entity,
     AGR.DBS_Source_System_Id as DBS_Source_System
     FROM DDCBIN_VBDW.V_AGREEMENT AGR
     LEFT OUTER JOIN DDCBIN_VBDW.V_PRODUCT P
     ON AGR.Product_Id=P.Product_Id
     AND AGR.DBS_Source_System_Id like any ('301%','302%')
     OR AGR.DBS_Source_System_Id = '13700'
     AND AGR.DBS_Start_Dt = (SELECT MAX(AGR1.DBS_Start_Dt) FROM
     DDCBIN_VBDW.V_AGREEMENT AGR1
     WHERE AGR.Account_Num=AGR1.Account_Num
     AND AGR.Account_Modifier_Num=AGR1.Account_Modifier_Num)
     AND P.Product_Start_Dt = (SELECT MAX(P1.Product_Start_Dt) FROM
     DDCBIN_VBDW.V_PRODUCT P1
     WHERE P.Product_Id=P1.Product_Id)

     LEFT OUTER JOIN DDCBIN_VBDW.V_ACCOUNT_CURRENCY AC
     ON AGR.Account_Num=AC.Account_Num
     AND AGR.Account_Modifier_Num=AC.Account_Modifier_Num
     AND AGR.DBS_Source_System_Id like any ('301%','302%')
     OR AGR.DBS_Source_System_Id = '13700'
     AND AC.Currency_Use_Cd = '1'
     AND AC.Account_Currency_Start_Dt = (SELECT
     MAX(AC1.Account_Currency_Start_Dt) FROM DDCBIN_VBDW.V_ACCOUNT_CURRENCY
     AC1
     WHERE AC.Account_Num =AC1.Account_Num
     AND AC.Account_Modifier_Num=AC1.Account_Modifier_Num )
     LEFT OUTER JOIN DDCBIN_VBDW.V_ACCOUNT_PARTY AP
     ON AGR.Account_Num=AP.Account_Num
     AND AGR.Account_Modifier_Num=AP.Account_Modifier_Num
     AND AP.Account_Party_Role_Cd IN ('202' , '201')
     AND AGR.DBS_Source_System_Id like any ('301%','302%')
     OR AGR.DBS_Source_System_Id = '13700'
     AND AP.Account_Party_Start_Dt = (SELECT MAX(AP1.Account_Party_Start_Dt)
     FROM DDCBIN_VBDW.V_ACCOUNT_PARTY AP1
     WHERE AP.Account_Num = AP1.Account_Num
     AND AP.Account_Modifier_Num =AP1.Account_Modifier_Num)
     LEFT OUTER JOIN DDCBIN_VBDW.V_PARTY PTY
     ON PTY.Party_Id = AP.Party_Id
     AND AP.Account_Party_Role_Cd IN ('202' , '201')
     AND AGR.DBS_Source_System_Id=PTY.DBS_Source_System_Id
     AND PTY.Party_Start_Dt = (SELECT MAX(PTY1.Party_Start_Dt) FROM
     DDCBIN_VBDW.V_PARTY PTY1
     WHERE PTY.Party_Id=PTY1.Party_Id )
     LEFT OUTER JOIN DD_MART_CBIN_MIS.V_PARTY_ACTIVE_CUSTOMER VPAC
     ON AP.Party_Id =VPAC.Customer_Party_Id
     AND AP.Account_Party_Role_Cd IN ('1912', '101')
     LEFT OUTER JOIN DDCBIN_VBDW.V_DBS_ACCT_IDENTIFICATION_HIST DAIH
     ON AGR.Account_Num=DAIH.Account_Num
     AND AGR.Account_Modifier_Num=DAIH.Account_Modifier_Num
     AND AGR.DBS_Source_System_Id like any ('301%','302%')
     OR AGR.DBS_Source_System_Id = '13700'
     AND DAIH.DBS_Start_Dt = (SELECT MAX(DAIH1.DBS_Start_Dt) FROM
     DDCBIN_VBDW.V_DBS_ACCT_IDENTIFICATION_HIST DAIH1
     WHERE DAIH.Account_Num = DAIH1.Account_Num
     AND DAIH.Account_Modifier_Num = DAIH1.Account_Modifier_Num
     AND DAIH.DBS_Acct_Id_Type_Cd = DAIH1.DBS_Acct_Id_Type_Cd )
     LEFT OUTER JOIN DDCBIN_VBDW.V_LOAN_REVIEW_HIST LRH
     ON AGR.Account_Num=LRH.Account_Num
     AND AGR.Account_Modifier_Num=LRH.Account_Modifier_Num
     AND AGR.DBS_Source_System_Id like any ('301%','302%')
     OR AGR.DBS_Source_System_Id = '13700'
     AND LRH.Loan_Review_Categ_Cd = '1'
     AND LRH.DBS_Start_Dt = (SELECT MAX(LRH1.DBS_Start_Dt) FROM
     DDCBIN_VBDW.V_LOAN_REVIEW_HIST LRH1
     WHERE LRH.Account_Num =LRH1.Account_Num
     AND LRH.Account_Modifier_Num = LRH1.Account_Modifier_Num
     AND LRH.Loan_Review_Categ_Cd = LRH1.Loan_Review_Categ_Cd
     AND LRH.Loan_Review_Party_Id = LRH1.Loan_Review_Party_Id
     );

My problem is when I am using above 2 views I am getting error 3540. Need solution little bit quickly...

Thanks in advance.

Can we simply above 2 views to resolve the problem.

Thanks & Regards,

Raghu

	<Prev	Next>		<<First	<Prev	Next>	Last>>