复杂oracle示例

select t.s_info_windcode,
    REPLACE(t.s_info_windcode,'.','') as pilot_product_id ,
    'BOND' as instrument_class ,
     (select s_info_industrycode from CBondIndustryWind where s_info_windcode= t.s_info_windcode) as instrument_subclass ,
     b_info_act as accrual_basis,
     b_info_term_year_ as b_info_term_year,
     b_issue_announcement as  announce_date,
     b_info_fullname as security_full_name,
     s_info_name as security_short_name,
     (select distinct b_tender_tenderdate from CBondTender where s_info_windcode=t.s_info_windcode) as auction_date,
     (select case count(1) when 0 then 'N' else 'Y' end from CBondCall where s_info_windcode= t.s_info_windcode) as callable,
     (select min(CBondCall.b_info_redemptiondate-b_info_callannouncementdate) from CBondCall where s_info_windcode= t.s_info_windcode 
      and b_info_callannouncementdate is not null and b_info_redemptiondate is not null  group by s_info_windcode ) as call_notify_days,
    'Calendar day' as call_notify_days_type,
    '' as call_schedule_date,
    (select listagg(b_info_redemptiondate) within group (order by b_info_redemptiondate) 
               from CBondCall  where s_info_windcode = t.s_info_windcode) as call_schedule_date_actual,
    (select listagg(b_info_redemptionprice,',') within group (order by b_info_redemptionprice) 
               from CBondCall  where s_info_windcode = t.s_info_windcode) as call_schedule_strike,
    (select listagg(b_info_paymentdate,',') within group (order by b_info_paymentdate) 
               from CBondPayment  where s_info_windcode = t.s_info_windcode) as coupon_schedule_date,
    (select listagg(b_info_interestperthousands/10,',') within group (order by b_info_interestperthousands) 
               from CBondPayment  where s_info_windcode = t.s_info_windcode) as coupon_schedule_interest,                      
    b_info_interestfrequency as coupon_frequency,
    crncy_code as currency  ,
    nvl(b_info_couponrate,0) as coupon ,
    (case t.b_info_interesttype when 501001000 then nvl(t2.rate, 0)  when 501002000 then  nvl(t.b_info_couponrate, 0) when 501003000 then nvl(t3.rate, 0) else 0 end) as current_coupon,
    '' as cusip ,
    (select s_info_min_price_chg_unit from WindCustomCode where s_info_windcode= t.s_info_windcode) as min_price_tick ,
    b_info_carrydate as dated_date ,
    s_info_exchmarket as ecn ,
    b_info_listdate as first_trade_date,
    b_info_delistdate as last_trade_date,
    (select case count(1) when 0 then 'N' else 'Y' end from Cbondfloatingrate where b_info_interestfloor is not null and s_info_windcode= t.s_info_windcode) as inflation_protected ,
    (select case count(1) when 0 then 'N' else 'Y' end  from CBondDescription x where x.s_info_formerwindcode=t.s_info_windcode) as is_reopen,
    (case when to_char(sysdate,'yyyymmdd') between b_issue_firstissue and b_issue_lastissue then 'Y' else 'N' end) as is_wi ,
    (case when b_info_coupon='505003000' and b_info_interesttype is null and b_info_couponrate is null then 'Y' else 'N' end ) as is_zero_coupon ,
    (select s_info_isincode from WindCustomCode where s_info_windcode= t.s_info_windcode) as isin ,
     b_issue_firstissue as  issue_date ,
     b_info_issuer as issuer ,
     b_info_maturitydate as maturity_date ,
     t.b_issue_amountact+(select nvl(sum(nvl(b_issue_amountact,0)),0) from  CBondDescription x 
      where x.s_info_formerwindcode=t.s_info_windcode and x.s_info_windcode not in (select s_info_ralatedcode from RalatedSecuritiesCode)) as original_qty ,
     b_info_coupondatetxt as payment_date_adjustment ,
     (select case count(1) when 0 then 'N' else 'Y' end from CBondPut where s_info_windcode= t.s_info_windcode) as putable,
     (select min(b_info_putannouncementdate-b_info_repurchasedate) from CBondPut where s_info_windcode= t.s_info_windcode 
      and b_info_putannouncementdate is not null and b_info_repurchasedate is not null  group by s_info_windcode ) as put_notify_days,
    'Calendar day'  as put_notify_days_type ,
    '' as put_schedule_date,
    
    (select listagg(b_info_repurchasedate,',') within group (order by b_info_repurchasedate) 
               from CBondPut  where s_info_windcode = t.s_info_windcode) as put_schedule_date_actual, 
    (select listagg(b_info_repurchaseprice,',') within group (order by b_info_repurchaseprice) 
               from CBondPut  where s_info_windcode = t.s_info_windcode) as put_schedule_strike, 
    'WIND' as source_system ,
    (select B_INFO_OUTSTANDINGBALANCE from(
    select b.S_INFO_WINDCODE, b.B_INFO_OUTSTANDINGBALANCE from CBondAmount b ,
    (select c.S_INFO_WINDCODE,MAX(c.S_INFO_ENDDATE) S_INFO_ENDDATE from CBondAmount c group by c.S_INFO_WINDCODE) a
    where b.S_INFO_WINDCODE=a.S_INFO_WINDCODE and b.S_INFO_ENDDATE=a.S_INFO_ENDDATE
    ) d where d.S_INFO_WINDCODE=t.S_INFO_WINDCODE) as remaining_qty 
from CBondDescription t,
(select x.s_info_windcode, x.rate
          from FloatingCouponsRate x,
               (select s_info_windcode, max(CHANGE_DT) rq
                  from FloatingCouponsRate
                 where CHANGE_DT < to_char(sysdate, 'yyyymmdd')
                 group by s_info_windcode) y
         where x.s_info_windcode = y.s_info_windcode) t2,
(select x.s_info_windcode, x.rate
          from OptionEmbeddedBondRate x,
               (select s_info_windcode, max(START_DT) rq
                  from OptionEmbeddedBondRate
                 where START_DT < to_char(sysdate, 'yyyymmdd')
                 group by s_info_windcode) y
         where x.s_info_windcode = y.s_info_windcode) t3
where t.s_info_windcode not in (select s_info_ralatedcode from RalatedSecuritiesCode) and t.S_INFO_FORMERWINDCODE is  null
 and substr(t.S_INFO_WINDCODE,instr(t.S_INFO_WINDCODE,'.',1)+1,length(t.S_INFO_WINDCODE)) in ('IB','SH','BC','SZ') and t.B_ISSUE_LASTISSUE<=to_char(sysdate,'yyyymmdd') 
 and t.s_info_windcode = t2.s_info_windcode(+) 
 and t.s_info_windcode = t3.s_info_windcode(+)
--and t.s_info_windcode = '150003.IB'