beautifier bug

R

Rus

Member
beautifier can't parse this code. I must to shutdown a process pl/sql developer, when i try it. Function is compilable.

Code: 
FUNCTION get_fact_yield_rate(par_begin_date DATE, par_end_date date) RETURN NUMBER is
   cursor c1(cur_begin_date date, cur_end_date date) is
SELECT NVL(
 (select sum(res)/months_between(cur_end_date, cur_begin_date) from(
(select
 months_between(yr.date_change, lag(yr.date_change,1,cur_begin_date) over (order by yr.date_change))*yr.value as res
 FROM t_yield_rate_ross yr
 WHERE yr.date_change >= cur_begin_date
 and yr.date_change <= cur_end_date
 and yr.flag = 1)

union

(select months_between(cur_end_date,prev_date)*val as res from
(SELECT
NVL(
(SELECT yr.value
 FROM
  t_yield_rate_ross yr
WHERE yr.date_change =
(select min(yr1.date_change)
 from t_yield_rate_ross yr1
 where yr1.flag = yr.flag
 and yr1.date_change >= cur_end_date)
 and yr.flag = 1),
      (SELECT yr1.value
        FROM t_yield_rate_ross yr1
       WHERE yr1.date_change =
             (SELECT MAX(yr2.date_change)
                FROM t_yield_rate_ross yr2
               WHERE yr2.date_change <= cur_end_date
                 AND yr2.flag = yr1.flag)
         AND yr1.flag = 0))
 as val,
 (SELECT yr.date_change
  FROM t_yield_rate_ross yr
  WHERE yr.date_change =
(select max(yr1.date_change)
 from t_yield_rate_ross yr1
 where yr1.flag = yr.flag
 and yr1.date_change <= cur_end_date
 and yr1.date_change >= cur_begin_date)
 and yr.flag = 1) as prev_date
FROM DUAL)))), (SELECT yr1.value
        FROM t_yield_rate_ross yr1
       WHERE yr1.date_change =
             (SELECT MAX(yr2.date_change)
                FROM t_yield_rate_ross yr2
               WHERE yr2.date_change <= cur_end_date
                 AND yr2.flag = yr1.flag)
         AND yr1.flag = 0)) as res from dual;

    res_fact_yield_rate number;
  begin
    open c1(par_begin_date,par_end_date);
    fetch c1 into res_fact_yield_rate;
    close c1;
    return res_fact_yield_rate;
  end;
Thanks.
 
Rus,

Beautifies on mine using v 6.0.4.906. What version you using ? Although the beautifying doesn't exactly give great results due to sub-query mis-indenting and UNION on same line. But altogether not a bad effort, given such a mad SQL statement :D .

Here's the beautified version (using my rules of course) :-

Code: 
FUNCTION get_fact_yield_rate(par_begin_date DATE, par_end_date DATE) RETURN NUMBER IS
   CURSOR c1(cur_begin_date DATE, cur_end_date DATE) IS
      SELECT NVL((SELECT SUM(res) / months_between(cur_end_date, cur_begin_date)
                 FROM   ((SELECT months_between(yr.date_change, lag(yr.date_change, 1, cur_begin_date) over(ORDER BY yr.date_change)) *
                                 yr.VALUE AS res
                          FROM   t_yield_rate_ross yr
                          WHERE  yr.date_change >= cur_begin_date
                          AND    yr.date_change <= cur_end_date
                          AND    yr.flag = 1) UNION
                         (SELECT months_between(cur_end_date, prev_date) * val AS res
                          FROM   (SELECT NVL((SELECT yr.VALUE
                                             FROM   t_yield_rate_ross yr
                                             WHERE  yr.date_change = (SELECT MIN(yr1.date_change)
                                                                      FROM   t_yield_rate_ross yr1
                                                                      WHERE  yr1.flag = yr.flag
                                                                      AND    yr1.date_change >= cur_end_date)
                                             AND    yr.flag = 1),
                                             (SELECT yr1.VALUE
                                               FROM   t_yield_rate_ross yr1
                                               WHERE  yr1.date_change = (SELECT MAX(yr2.date_change)
                                                                         FROM   t_yield_rate_ross yr2
                                                                         WHERE  yr2.date_change <= cur_end_date
                                                                         AND    yr2.flag = yr1.flag)
                                               AND    yr1.flag = 0)) AS val
                                        ,(SELECT yr.date_change
                                          FROM   t_yield_rate_ross yr
                                          WHERE  yr.date_change = (SELECT MAX(yr1.date_change)
                                                                   FROM   t_yield_rate_ross yr1
                                                                   WHERE  yr1.flag = yr.flag
                                                                   AND    yr1.date_change <= cur_end_date
                                                                   AND    yr1.date_change >= cur_begin_date)
                                          AND    yr.flag = 1) AS prev_dateFROM DUAL)))),
                 (SELECT yr1.VALUE
                   FROM   t_yield_rate_ross yr1
                   WHERE  yr1.date_change = (SELECT MAX(yr2.date_change)
                                             FROM   t_yield_rate_ross yr2
                                             WHERE  yr2.date_change <= cur_end_date
                                             AND    yr2.flag = yr1.flag)
                   AND    yr1.flag = 0)) AS res
      FROM   dual;
   res_fact_yield_rate NUMBER;
BEGIN
   OPEN c1(par_begin_date, par_end_date);
   FETCH c1
      INTO res_fact_yield_rate;
   CLOSE c1;
   RETURN res_fact_yield_rate;
END;
Regards,
D.
 
You must log in or register to reply here.
Back
Top