1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Regarding view output

Discussion in 'SQL PL/SQL' started by vjohnny8, Oct 3, 2011.

  1. vjohnny8

    vjohnny8 Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Hi,

    The below code is that of the view, please refer it for the output as given in the end of the code


    Code (SQL):

    CREATE OR REPLACE VIEW c2v_borr_secu_rpt AS
    SELECT
    bank_cust_id --1
    , bank_company_id --2
    , client_id --3
    , cust_name --4
    , segment_id --5
    , seg_name --6
    , sector_id --7
    , sector_name --8
    , industry_id --9
    , industry_name --10
    , client_group_id --11
    , group_name --12
    , npa_status --13
    , restructured --14
    , acl_id --15
    , asset_class --16
    , sub_acl_id --17
    , sub_asset_class --18
    , b_retail --19
    , SUM(advances_outstanding) advances_outstanding --20
    , SUM(investments_outstanding) investments_outstanding --21
    , SUM(Tot_FB_Outstanding) Tot_FB_Outstanding --22 (20+21)
    , SUM(nfb_nmr_outstanding) nfb_nmr_outstanding --23
    , SUM(nfb_mr_outstanding) nfb_mr_outstanding --24
    , SUM(Tot_NFB_Outstanding) Tot_NFB_Outstanding--25 (23+24)
    , SUM(advances_undisbursed) advances_undisbursed --26
    , SUM(investments_undisbursed) investments_undisbursed --27
    , SUM(Tot_FB_Undisbursed) Tot_FB_Undisbursed --28 (26+27)
    , SUM(nfb_nmr_undisbursed) nfb_nmr_undisbursed --29
    , SUM(nfb_mr_undisbursed) nfb_mr_undisbursed --30
    , SUM(Tot_NFB_Undisbursed) Tot_NFB_Undisbursed --31 (29+30)
    , SUM(Total_Outstanding) Total_Outstanding --32
    , SUM(Total_Undisbursed) Total_Undisbursed --33
    , SUM(Total_Exposure) Total_Exposure --34
    , SUM(own_bank_td) own_bank_td --35
    , SUM(own_bank_current_a_cs) own_bank_current_a_cs --36
    , SUM(own_bank_saving_a_cs) own_bank_saving_a_cs --37
    , SUM(own_bank_rd) own_bank_rd --38
    , SUM(Fund_Based_Security) Fund_Based_Security --39 (35+36+37+38)
    , SUM(security_code_lc) security_code_lc --40
    , SUM(security_code_bg) security_code_bg --41
    , SUM(other_secu) other_secu
    , SUM(Non_Fund_Based_Security) Non_Fund_Based_Security --42 (40+41)
    , SUM(Total_Liquid_Security) Total_Liquid_Security --43 (39+42)
    , SUM(Tot_Exposure_after_Tot_Secu) Tot_Exposure_after_Tot_Secu -- 44 (34-43)
    , SUM(Tot_FBC_before_Tot_secu) Tot_FBC_before_Tot_secu -- 45 (22+28)
    , SUM(Tot_FBC_after_Tot_FB_secu) Tot_FBC_after_Tot_FB_secu --46 (45-39)
    , SUM(Tot_NFBC_before_Tot_secu) Tot_NFBC_before_Tot_secu --47 (25+31)
    , SUM(Tot_NFBC_after_Tot_NFB_secu) Tot_NFBC_after_Tot_NFB_secu --48 (47-42)
    , SUM(provisions) provisions --49
    , SUM(sec_alloc_after_srf) sec_alloc_after_srf --50
    , trading_book_outstanding --51
    FROM
    (
    SELECT
    bank_cust_id --1
    , bank_company_id --2
    , client_id --3
    , cust_name --4
    , segment_id --5
    , seg_name --6
    , sector_id --7
    , sector_name --8
    , industry_id --9
    , industry_name --10
    , client_group_id --11
    , group_name --12
    , npa_status --13
    , restructured --14
    , acl_id --15
    , asset_class --16
    , sub_acl_id --17
    , sub_asset_class --18
    , b_retail --19
    , advances_outstanding --20
    , investments_outstanding --21
    , (advances_outstanding+investments_outstanding) AS Tot_FB_Outstanding --22 (20+21)
    , nfb_nmr_outstanding --23
    , nfb_mr_outstanding --24
    , (nfb_nmr_outstanding+nfb_mr_outstanding) AS Tot_NFB_Outstanding --25 (23+24)
    , advances_undisbursed --26
    , investments_undisbursed --27
    , (advances_undisbursed+investments_undisbursed) AS Tot_FB_Undisbursed --28 (26+27)
    , nfb_nmr_undisbursed --29
    , nfb_mr_undisbursed --30
    , (nfb_nmr_undisbursed+nfb_mr_undisbursed) AS Tot_NFB_Undisbursed --31 (29+30)
    , (advances_outstanding+investments_outstanding) + (nfb_nmr_outstanding+nfb_mr_outstanding) AS Total_Outstanding --32
    , (advances_undisbursed+investments_undisbursed) + (nfb_nmr_undisbursed+nfb_mr_undisbursed) AS Total_Undisbursed --33
    , (advances_outstanding+investments_outstanding) + (nfb_nmr_outstanding+nfb_mr_outstanding) +
    (advances_undisbursed+investments_undisbursed) + (nfb_nmr_undisbursed+nfb_mr_undisbursed) AS Total_Exposure --34
    , own_bank_td --35
    , own_bank_current_a_cs --36
    , own_bank_saving_a_cs --37
    , own_bank_rd --38
    , other_secu
    , (own_bank_td + own_bank_current_a_cs + own_bank_saving_a_cs + own_bank_rd + other_secu) Fund_Based_Security --39 (35+36+37+38)
    , security_code_lc --40
    , security_code_bg --41
    , (security_code_lc + security_code_bg) AS Non_Fund_Based_Security --42 (40+41)
    , (own_bank_td + own_bank_current_a_cs + own_bank_saving_a_cs + own_bank_rd) +
    (security_code_lc + security_code_bg) AS Total_Liquid_Security --43 (39+42)
    , (CASE WHEN
    ((advances_outstanding+investments_outstanding + nfb_nmr_outstanding+nfb_mr_outstanding +
    advances_undisbursed+investments_undisbursed + nfb_nmr_undisbursed+nfb_mr_undisbursed) -
    (security_code_lc + security_code_bg)) < 0
    THEN 0
    ELSE
    ((advances_outstanding+investments_outstanding + nfb_nmr_outstanding+nfb_mr_outstanding +
    advances_undisbursed+investments_undisbursed + nfb_nmr_undisbursed+nfb_mr_undisbursed) -
    (security_code_lc + security_code_bg))
    END) AS Tot_Exposure_after_Tot_Secu -- 44 (34-43)
    , (advances_outstanding+investments_outstanding + advances_undisbursed+investments_undisbursed)
    AS Tot_FBC_before_Tot_secu -- 45 (22+28)
    , (CASE WHEN
    ((advances_outstanding+investments_outstanding + advances_undisbursed+investments_undisbursed) -
    (own_bank_td + own_bank_current_a_cs + own_bank_saving_a_cs + own_bank_rd)) < 0
    THEN 0
    ELSE
    (advances_outstanding+investments_outstanding + advances_undisbursed+investments_undisbursed) -
    (own_bank_td + own_bank_current_a_cs + own_bank_saving_a_cs + own_bank_rd)
    END) AS Tot_FBC_after_Tot_FB_secu --46 (45-39)
    , (nfb_nmr_outstanding+nfb_mr_outstanding + nfb_nmr_undisbursed+nfb_mr_undisbursed)
    AS Tot_NFBC_before_Tot_secu --47 (25+31)
    , (CASE WHEN
    ((nfb_nmr_outstanding+nfb_mr_outstanding + nfb_nmr_undisbursed+nfb_mr_undisbursed) -
    (security_code_lc + security_code_bg)) < 0
    THEN 0
    ELSE
    ((nfb_nmr_outstanding+nfb_mr_outstanding + nfb_nmr_undisbursed+nfb_mr_undisbursed) -
    (security_code_lc + security_code_bg))
    END) AS Tot_NFBC_after_Tot_NFB_secu --48 (47-42)
    , provisions --49
    , sec_alloc_after_srf --50
    , trading_book_outstanding--51
    FROM
    (
    SELECT
    nvl((SELECT e.bank_cust_id
    FROM c2u_exposure e, c2m_exposure me
    WHERE t.limit_id = me.limit_id
    AND e.exposure_id = me.exposure_id
    AND t.limit_exp_id = 3),
    (SELECT u.bank_cust_id FROM c2u_limit u
    WHERE u.limit_id = t.p_limit_id)) bank_cust_id
    , (SELECT c.bank_company_id FROM c2u_client c WHERE c.client_id = d.client_id) bank_company_id
    , t.client_id
    ---------------------Client Details
    , (SELECT c.cust_name FROM c2m_client c WHERE c.client_id=t.client_id) AS cust_name
    , (SELECT m.bankcode FROM mst_mktsegment m WHERE m.seg_code= d.segment_id) AS segment_id
    , (SELECT m.seg_name FROM mst_mktsegment m WHERE m.seg_code= d.segment_id) AS seg_name
    , (SELECT m.sector_bankcode FROM mst_sector m WHERE m.sector_code= d.sector_id) AS sector_id
    , (SELECT m.sector_name FROM mst_sector m WHERE m.sector_code= d.sector_id) AS sector_name
    , (SELECT m.industry_bankcode FROM mst_industry m WHERE m.industry_id = d.industry_id) AS industry_id
    , (SELECT m.industry_name FROM mst_industry m WHERE m.industry_id = d.industry_id) AS industry_name
    , d.client_group_id
    , (SELECT g.client_group FROM c2c_client_group g WHERE g.client_group_id = d.client_group_id) AS group_name
    , (CASE WHEN t.b_npa=1 THEN 'Yes' ELSE 'No' END) AS NPA_Status
    , (CASE WHEN t.b_restructured=1 THEN 'Yes' ELSE 'No' END) AS Restructured
    , t.acl_id
    , (SELECT c.acl FROM c2c_acl c WHERE c.acl_id =t.acl_id) AS asset_class
    , t.sub_acl_id
    , (SELECT c.acl FROM c2c_acl c WHERE c.acl_id =t.sub_acl_id) AS sub_asset_class
    , (CASE t.b_retail WHEN 1 THEN 'Retail' WHEN 0 THEN 'Non-Retail' END) AS b_retail
    ----------------------Outstanding
    , (CASE WHEN f.class_id = 1 AND t.b_funded =1 THEN t.outstanding ELSE 0 END) AS Advances_Outstanding
    , (CASE WHEN f.class_id = 4 AND t.b_funded =1 THEN t.outstanding ELSE 0 END) AS Investments_Outstanding
    , (CASE WHEN f.class_id = 3 AND t.b_funded =0 THEN t.outstanding ELSE 0 END) AS NFB_NMR_Outstanding
    , (CASE WHEN f.class_id = 2 AND t.b_funded =0 THEN t.outstanding ELSE 0 END) AS NFB_MR_Outstanding
    ----------------------Undisbursed
    , (CASE WHEN f.class_id = 1 AND t.b_funded = 1 THEN nvl((t.ua_limit_ccy*t.ccy_cf),0) ELSE 0 END) AS Advances_Undisbursed
    , (CASE WHEN f.class_id = 4 AND t.b_funded = 1 THEN nvl((t.ua_limit_ccy*t.ccy_cf),0) ELSE 0 END) AS Investments_Undisbursed
    , (CASE WHEN f.class_id = 3 AND t.b_funded = 0 THEN nvl((t.ua_limit_ccy*t.ccy_cf),0) ELSE 0 END) AS NFB_NMR_Undisbursed
    , (CASE WHEN (f.class_id = 2 AND t.b_funded = 0) THEN nvl((t.ua_limit_ccy*t.ccy_cf),0) ELSE 0 END) AS NFB_MR_Undisbursed
    -----------------------Fund Based Security Value
    , nvl((SELECT SUM(s.security_value) FROM c2m_ls s, c2c_bk_secu b
    WHERE t.limit_id=s.limit_id AND b.bk_secu_id=s.bk_secu_id
    AND b.ref_secu_id IN ('D001','D007','D008','D009','D010')),0) AS OWN_BANK_TD
    , nvl((SELECT SUM(s.security_value) FROM c2m_ls s, c2c_bk_secu b
    WHERE t.limit_id=s.limit_id AND b.bk_secu_id=s.bk_secu_id
    AND b.ref_secu_id IN ('D003')),0) AS OWN_BANK_CURRENT_A_CS
    , nvl((SELECT SUM(s.security_value) FROM c2m_ls s, c2c_bk_secu b
    WHERE t.limit_id=s.limit_id AND b.bk_secu_id=s.bk_secu_id
    AND b.ref_secu_id IN ('D004')),0) AS OWN_BANK_SAVING_A_CS
    , nvl((SELECT SUM(s.security_value) FROM c2m_ls s, c2c_bk_secu b
    WHERE t.limit_id=s.limit_id AND b.bk_secu_id=s.bk_secu_id
    AND b.ref_secu_id IN ('D002')),0) AS OWN_BANK_RD
    , nvl((SELECT SUM(s.security_value) FROM c2m_ls s, c2c_bk_secu b
    WHERE t.limit_id=s.limit_id AND b.bk_secu_id=s.bk_secu_id
    AND b.ref_secu_id NOT IN ('D001','D007','D008','D009','D010','LC','BG')),0) AS OTHER_SECU
    -----------------------Non Fund Based Security Value
    , nvl((SELECT SUM(s.security_value) FROM c2m_ls s, c2c_bk_secu b
    WHERE t.limit_id=s.limit_id AND b.bk_secu_id=s.bk_secu_id
    AND b.ref_secu_id IN ('LC')),0) AS Security_code_LC
    , nvl((SELECT SUM(s.security_value) FROM c2m_ls s, c2c_bk_secu b
    WHERE t.limit_id=s.limit_id AND b.bk_secu_id=s.bk_secu_id
    AND b.ref_secu_id IN ('BG')),0) AS Security_code_BG ,
    t.provisions
    , nvl((SELECT SUM(s.net_security_value) FROM c2m_ls s WHERE s.limit_id=t.limit_id AND t.b_crm = 0),0) AS sec_alloc_after_srf
    , nvl((SELECT SUM(e.outstanding) FROM c2m_tb e WHERE e.client_id=t.client_id),0) AS trading_book_outstanding
    FROM c2m_limit t, c2m_client d, c2c_bk_facility f
    WHERE d.client_id = t.client_id
    AND t.bk_facility_id = f.bk_facility_id
    )t2
    )
    GROUP BY bank_cust_id --1
    , bank_company_id --2
    , client_id --3
    , cust_name --4
    , segment_id --5
    , seg_name --6
    , sector_id --7
    , sector_name --8
    , industry_id --9
    , industry_name --10
    , client_group_id --11
    , group_name --12
    , npa_status --13
    , restructured --14
    , acl_id --15
    , asset_class --16
    , sub_acl_id --17
    , sub_asset_class --18
    , b_retail, trading_book_outstanding;
     

    The output of the view is as follows:

    Bank_cust_id bank_company_id client_ID cust_name ........total_outstanding...... trading_book_outstanding
    RETS1 999009735 1002 ADITYA 10329249.93 100000
    RETS2 999009735 1002 ADITYA 10329249.93 100000
    RETS3 999009224 1000 MANPPURAM 500000 5074566764
    CBS1 999009224 1000 MANPPURAM 1573753.67 5074566764

    the desired output I require is: the bank_conmpany_ID should be unique even though there is a different Bank_cust_id, the output should be only 2 rows - one having 999009735 and 999009224

    how can i modify the view ?
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Eliminate the columns interfering with your desired output. You are returning distinct rows; the total_outstanding and bank_cust_id cause bank_company_id to be returned more than once. Returning only the bank_company_id, client_id, cust_name and trading_book_outstanding will give you the distinct bank_company_id output you want to see.