Merge branch 'master' of git@lemon:sqlgg
[sqlgg.git] / stress.sql
blobf5d86f9415898f8b6fa644d304c9eded1e3874a7
1  SELECT xx.xx_rec_id                                    promise_unit_ID, 
2         xx.xx_volume                                    aggregate_volume,       
3         xx_category                                     category,       
4         xx_difference                                   difference, 
5         xx_initial_liability_amt                        initial_liability, 
6         xx_out_of_pocket                                out_of_pocket, 
7         xx_holdings_Secured_Flag                        holdings_UNSECURED_FLAG,
8         xx.xx_normal_liability_chart                    normal_liability_chart,
9         xx.xx_facility_liability_chart                  FACILITY_liability_chart,       
10         yy.yy_rec_i                                     target_REC_ID,  
11         yy.yy_prediction_meter                          chart_prediction_meter,
12         yy.yy_unique_instance_liability_chart           unique_instance_liability_chart,
13         yy.yy_target_liability_chart                    target_liability_chart, 
14         xx.xx_indicator_id,                             
15         xx.xx_unused_src_calc                           UNUSED_count_SOURCE,    
16         yy.yy_target_nm                                 target_identifier, 
17         yy.yy_target_id                                 target_ID, 
18         xx.xx_facility_id                               mqxy_promise_unit_ID,
19         xx.xx_amt                                       mqxy_unit_present_count, 
20         xx.xx_units                                     mqxy_unit_present_units, 
21         xx.xx_standard_amt                              mqxy_standard_promise_count, 
22         xx.xx_units                                     mqxy_standard_unit_units,
23         to_CHAR(trunc(xx_during_contract_DT))           during_contract_DATE,
24         to_CHAR(trunc(xx_termination_DT))               termination_DATE,
25         to_CHAR(trunc(xx.xx_expiration_DT))             expiration_DATE,
26         nvl(agreement_aggregate.agreement_aggregate,0)  agreement_aggregate,
27         nvl(lc_aggregate.lc_aggregate,0)                LC_aggregate,   
28         r.r_xoynx_RTG                                   xoynxS_RATING,  
29         r.r_grrrr_RTG                                   grrrr_RATING    
30  FROM   (SELECT yy.xx_rec_id,   
31                 SUM(yy.l_count)                 agreement_aggregate     
32         FROM    target                  yy,     
33                 promise_agreement       zz, 
34                 promise_unit            aa,     
35                 agreement_unit          lo 
36         WHERE   zz.yy_rec_id = yy.yy_rec_id AND
37                 xx.ca_rec_id = zz.ca_rec_id AND
38                 lo.xx_rec_id = xx.xx_rec_id     
39         GROUP BY        yy.yy_rec_id,
40                         lo.xx_rec_id
41         )                       agreement_aggregate, 
42         (SELECT lc.xx_rec_id,   
43                 SUM(lc.lc_amt)          lc_aggregate
44         FROM    target                  yy,     
45                 promise_agreement       zz,     
46                 promise_unit            aa,     
47                 lc_unit                 lc
48         WHERE   zz.yy_rec_id = yy.yy_rec_id AND
49                 xx.ca_rec_id = zz.ca_rec_id AND
50                 lc.xx_rec_id = xx.xx_rec_id
51         GROUP BY        yy.yy_rec_id,   
52                         lc.xx_rec_id
53         )                       lc_aggregate, 
54         target                  yy,     
55         promise_agreement       zz,     
56         rating                  r, 
57         promise_unit            cs 
58  WHERE  zz.yy_rec_id = yy.yy_rec_id AND
59         r.yy_rec_id = yy.yy_rec_id AND
60         xx.ca_rec_id = zz.ca_rec_id AND
61         lc_aggregate.xx_rec_id = xx.xx_rec_id AND
62         agreement_aggegate.xx_rec_id    = xx.xx_rec_id AND
63         xx.xx_indicator_id = 1 AND
64         zz.ca_indicator_id = 1 AND
65         (       r.r_indicator_id = 1 OR 
66                 r.r_indicator_id IS NULL)
67  ORDER BY target_identifier