• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 348
  • Last Modified:

Simplify SQL with loops?

Hello.  I am working on this VERY long sql statement.  It needs simplifying, using a loop.  Basically, the result is output into two sections in a Excel report.  The days section is a series of union and select statements.  The SQL accepts two parameters :posteda and :postedb.

SELECT   a.COUNT "Total Loans Posted",
         SUM (b.COUNT) AS "Total Payee & Parcel Matches",
         SUM (b.COUNT) * 2 AS "Total Data Elements",
         'ATLS Payee & Parcel' AS "Source", SUM (c.COUNT) AS "Loan Volume",
         2 AS "Factor", SUM (c.COUNT) * 2 "Data Elements",
         (SUM (c.COUNT) * 2) / (SUM (b.COUNT) * 2) AS "Rate",
         NULL AS "On Day 1", NULL AS "By Day 2", NULL AS "By Day 3",
         NULL AS "By Day 4", NULL AS "By Day 5", NULL AS "By Day 6",
         NULL AS "By Day 7", NULL AS "By Day 8", NULL AS "By Day 9",
         NULL AS "By Day 10", NULL AS "By Day 11", NULL AS "By Day 12",
         NULL AS "By Day 13", NULL AS "By Day 14", NULL AS "By Day 15",
         NULL AS "By Day 16", NULL AS "By Day 17", NULL AS "By Day 18",
         NULL AS "By Day 19", NULL AS "By Day 20", NULL AS "By Day 21",
         NULL AS "By Day 22", NULL AS "By Day 23", NULL AS "By Day 24",
         NULL AS "By Day 25", NULL AS "By Day 26", NULL AS "By Day 27",
         NULL AS "By Day 28", NULL AS "By Day 29", NULL AS "By Day 30",
         NULL AS "By Day 31"
    FROM (SELECT   l.lender_number, COUNT (DISTINCT l.loan_number) AS COUNT
              FROM loan l
             WHERE l.lender_number in (106,222,472,591,685,708,936)
               AND l.loan_posting_date > :posteda
               AND l.loan_posting_date < :postedb
          GROUP BY l.lender_number) a,
         (SELECT   /*+ index (l XIE1LOAN) index (ptdb XIE1PTDB_TAX_LINE))*/
                   l.lender_number, lender_payee_code,
                   ptdb.payee_parcel_number, l.loan_number,
                   COUNT (DISTINCT l.loan_number) AS COUNT
              FROM loan l, ptdb_tax_line ptdb
             WHERE l.lender_number in (106,222,472,591,685,708,936)
               AND l.loan_posting_date > :posteda
               AND l.loan_posting_date < :postedb
               AND l.lender_number = ptdb.lender_number
               AND l.loan_number = ptdb.loan_number
          GROUP BY l.lender_number,
                   lender_payee_code,
                   ptdb.payee_parcel_number,
                   l.loan_number) b,
         (SELECT   /*+ index (l XIE1LOAN) index (lp XPKLOAN_PARCEL) index (ptdb XIE1PTDB_TAX_LINE))*/
                   l.lender_number, lender_payee_code,
                   ptdb.payee_parcel_number, l.loan_number,
                   COUNT (DISTINCT l.loan_number) AS COUNT
              FROM loan l, loan_parcel lp, ptdb_tax_line ptdb
             WHERE l.lender_number in (106,222,472,591,685,708,936)
               AND l.lender_number = lp.lender_number
               AND l.loan_number = lp.loan_number
               AND l.lender_number = ptdb.lender_number
               AND l.loan_number = ptdb.loan_number
               AND l.loan_posting_date > :posteda
               AND l.loan_posting_date < :postedb
            GROUP BY l.lender_number,
                   lender_payee_code,
                   ptdb.payee_parcel_number,
                   l.loan_number) c
   WHERE b.lender_number(+) = a.lender_number
     AND c.lender_number(+) = b.lender_number
     AND c.lender_payee_code(+) = b.lender_payee_code
     AND c.loan_number(+) = b.loan_number
     AND c.payee_parcel_number(+) = b.payee_parcel_number
GROUP BY a.COUNT
UNION ALL
SELECT   NULL "Total Loans Posted", NULL AS "Total Payee & Parcel Matches",
         NULL AS "Total Data Elements", 'ATLS Payee - Analysis Only' AS "Source",
         SUM (c.COUNT) AS " Loan Volume", 1 "Factor",
         SUM (c.COUNT) * 1 "Data Elements",
         (SUM (c.COUNT) * 1) / (SUM (b.COUNT) * 2) AS "Rate",
         NULL AS "On Day 1", NULL AS "By Day 2", NULL AS "By Day 3",
         NULL AS "By Day 4", NULL AS "By Day 5", NULL AS "By Day 6",
         NULL AS "By Day 7", NULL AS "By Day 8", NULL AS "By Day 9",
         NULL AS "By Day 10", NULL AS "By Day 11", NULL AS "By Day 12",
         NULL AS "By Day 13", NULL AS "By Day 14", NULL AS "By Day 15",
         NULL AS "By Day 16", NULL AS "By Day 17", NULL AS "By Day 18",
         NULL AS "By Day 19", NULL AS "By Day 20", NULL AS "By Day 21",
         NULL AS "By Day 22", NULL AS "By Day 23", NULL AS "By Day 24",
         NULL AS "By Day 25", NULL AS "By Day 26", NULL AS "By Day 27",
         NULL AS "By Day 28", NULL AS "By Day 29", NULL AS "By Day 30",
         NULL AS "By Day 31"
    FROM (SELECT   l.lender_number, COUNT (DISTINCT l.loan_number) AS COUNT
              FROM loan l
             WHERE l.lender_number in (106,222,472,591,685,708,936)
               AND l.loan_posting_date > :posteda
               AND l.loan_posting_date < :postedb
          GROUP BY l.lender_number) a,
         (SELECT   /*+ index (l XIE1LOAN) index (ptdb XIE1PTDB_TAX_LINE))*/
                   l.lender_number, lender_payee_code,
                   ptdb.payee_parcel_number, l.loan_number,
                   COUNT (DISTINCT l.loan_number) AS COUNT
              FROM loan l, ptdb_tax_line ptdb
             WHERE l.lender_number in (106,222,472,591,685,708,936)
               AND l.loan_posting_date > :posteda
               AND l.loan_posting_date < :postedb
               AND l.lender_number = ptdb.lender_number
               AND l.loan_number = ptdb.loan_number
          GROUP BY l.lender_number,
                   lender_payee_code,
                   ptdb.payee_parcel_number,
                   l.loan_number) b,
         (SELECT   /*+ index (l XIE1LOAN) index (lp XPKLOAN_PARCEL) index (ptdb XIE1PTDB_TAX_LINE))*/
                   l.lender_number, lender_payee_code,
                   ptdb.payee_parcel_number, l.loan_number,
                   COUNT (DISTINCT l.loan_number) AS COUNT
              FROM loan l, ptdb_tax_line ptdb
             WHERE l.lender_number in (106,222,472,591,685,708,936)
               AND l.lender_number = ptdb.lender_number
               AND l.loan_number = ptdb.loan_number
               AND l.loan_posting_date > :posteda
               AND l.loan_posting_date < :postedb
               AND SUBSTR (l.match_code, 1, 1) IN ('P', 'Y', 'X')
          GROUP BY l.lender_number,
                   lender_payee_code,
                   ptdb.payee_parcel_number,
                   l.loan_number) c
   WHERE b.lender_number(+) = a.lender_number
     AND c.lender_number(+) = b.lender_number
     AND c.lender_payee_code(+) = b.lender_payee_code
     AND c.loan_number(+) = b.loan_number
     AND c.payee_parcel_number(+) = b.payee_parcel_number
GROUP BY a.COUNT
UNION ALL
SELECT NULL AS "Total Loans Posted", NULL AS "Total Payee & Parcel Matches",
       NULL AS "Total Data Elements", 'ITLS From Analysis Line' AS "Source",
       SUM (e.COUNT) "Loan Volume", 1 "Factor", SUM (e.COUNT) * 1 "Data Elements",
       NULL AS "Rate", SUM (f.COUNT) AS "On Day 1",
       SUM (g.COUNT) "By Day 2", SUM (h.COUNT) "By Day 3",
       SUM (i.COUNT) "By Day 4", SUM (j.COUNT) "By Day 5",
       SUM (k.COUNT) "By Day 6", SUM (l.COUNT) "By Day 7",
       SUM (m.COUNT) "By Day 8", SUM (n.COUNT) "By Day 9",
       SUM (o.COUNT) "By Day 10", SUM (p.COUNT) "By Day 11",
       SUM (q.COUNT) "By Day 12", SUM (r.COUNT) "By Day 13",
       SUM (s.COUNT) "By Day 14", SUM (t.COUNT) "By Day 15",
       SUM (u.COUNT) "By Day 16", SUM (v.COUNT) "By Day 17",
       SUM (w.COUNT) "By Day 18", SUM (x.COUNT) "By Day 19",
       SUM (y.COUNT) "By Day 20", SUM (z.COUNT) "By Day 21",
       SUM (aa.COUNT) "By Day 22", SUM (bb.COUNT) "By Day 23",
       SUM (cc.COUNT) "By Day 24", SUM (dd.COUNT) "By Day 25",
       SUM (ee.COUNT) "By Day 26", SUM (ff.COUNT) "By Day 27",
       SUM (gg.COUNT) "By Day 28", SUM (hh.COUNT) "By Day 29",
       SUM (ii.COUNT) "By Day 30", SUM (ii.COUNT) "By Day 31"
  FROM (SELECT   l.lender_number, COUNT (DISTINCT l.loan_number) AS COUNT
            FROM loan l
           WHERE l.lender_number in (106,222,472,591,685,708,936)
             AND l.loan_posting_date > :posteda
             AND l.loan_posting_date < :postedb
        GROUP BY l.lender_number) d,
       (SELECT   /*+ index (l XIE1LOAN) index (ptdb XIE1PTDB_TAX_LINE))*/
                 l.lender_number, lender_payee_code, ptdb.payee_parcel_number,
                 l.loan_number, COUNT (DISTINCT l.loan_number) AS COUNT
            FROM loan l, ptdb_tax_line ptdb, loan_parcel lp
           WHERE l.lender_number in (106,222,472,591,685,708,936)
             AND l.loan_posting_date > :posteda
             AND l.loan_posting_date < :postedb
             AND l.lender_number = ptdb.lender_number
             AND l.loan_number = ptdb.loan_number
             AND SUBSTR (l.match_code, 1, 1) IN ('P', 'Y', 'X')
                   AND l.lender_number = lp.lender_number (+)
                   AND l.loan_number = lp.loan_number (+)
                   AND lp.lender_number is null
        GROUP BY l.lender_number,
                 lender_payee_code,
                 ptdb.payee_parcel_number,
                 l.loan_number) e,
       (SELECT   /*+ index (l XIE1LOAN) index (lp XPKLOAN_PARCEL) index (ptdb XIE1PTDB_TAX_LINE))*/
                 l.lender_number, ptdb.lender_payee_code,
                 ptdb.payee_parcel_number, l.loan_number,
                 COUNT (DISTINCT l.loan_number) AS COUNT
            FROM loan l, ptdb_tax_line ptdb, loan_parcel lp
           WHERE l.lender_number in (106,222,472,591,685,708,936)
             AND l.lender_number = ptdb.lender_number
             AND l.loan_number = ptdb.loan_number
             AND l.loan_posting_date > :posteda
             AND l.loan_posting_date < :postedb
             AND SUBSTR (l.match_code, 1, 1) IN ('P', 'Y', 'X')
             AND l.lender_number = lp.lender_number (+)
                   AND l.loan_number = lp.loan_number (+)
                   AND lp.lender_number is null
             AND TRUNC (ptdb.last_change_date) - TRUNC (loan_posting_date) < 1
        GROUP BY l.lender_number,
                 lender_payee_code,
                 ptdb.payee_parcel_number,
                 l.loan_number) f,
       (SELECT   /*+ index (l XIE1LOAN) index (lp XPKLOAN_PARCEL) index (ptdb XIE1PTDB_TAX_LINE))*/
                 l.lender_number, ptdb.lender_payee_code,
                 ptdb.payee_parcel_number, l.loan_number,
                 COUNT (DISTINCT l.loan_number) AS COUNT
            FROM loan l, ptdb_tax_line ptdb, loan_parcel lp
           WHERE l.lender_number in (106,222,472,591,685,708,936)
             AND l.lender_number = ptdb.lender_number
             AND l.loan_number = ptdb.loan_number
             AND l.loan_posting_date > :posteda
             AND l.loan_posting_date < :postedb
             AND SUBSTR (l.match_code, 1, 1) IN ('P', 'Y', 'X')
             AND l.lender_number = lp.lender_number (+)
                   AND l.loan_number = lp.loan_number (+)
                   AND lp.lender_number is null
             AND TRUNC (ptdb.last_change_date) - TRUNC (loan_posting_date) < 2
        GROUP BY l.lender_number,
                 lender_payee_code,
                 ptdb.payee_parcel_number,
                 l.loan_number) g,
       (SELECT   /*+ index (l XIE1LOAN) index (lp XPKLOAN_PARCEL) index (ptdb XIE1PTDB_TAX_LINE))*/
                 l.lender_number, ptdb.lender_payee_code,
                 ptdb.payee_parcel_number, l.loan_number,
                 COUNT (DISTINCT l.loan_number) AS COUNT
            FROM loan l, ptdb_tax_line ptdb, loan_parcel lp
           WHERE l.lender_number in (106,222,472,591,685,708,936)
             AND l.lender_number = ptdb.lender_number
             AND l.loan_number = ptdb.loan_number
             AND l.loan_posting_date > :posteda
             AND l.loan_posting_date < :postedb
             AND SUBSTR (l.match_code, 1, 1) IN ('P', 'Y', 'X')
             AND l.lender_number = lp.lender_number (+)
                   AND l.loan_number = lp.loan_number (+)
                   AND lp.lender_number is null
             AND TRUNC (ptdb.last_change_date) - TRUNC (loan_posting_date) < 3
        GROUP BY l.lender_number,
                 lender_payee_code,
                 ptdb.payee_parcel_number,
                 l.loan_number) h,
       (SELECT   /*+ index (l XIE1LOAN) index (lp XPKLOAN_PARCEL) index (ptdb XIE1PTDB_TAX_LINE))*/
                 l.lender_number, ptdb.lender_payee_code,
                 ptdb.payee_parcel_number, l.loan_number,
                 COUNT (DISTINCT l.loan_number) AS COUNT
            FROM loan l, ptdb_tax_line ptdb, loan_parcel lp
           WHERE l.lender_number in (106,222,472,591,685,708,936)
             AND l.lender_number = ptdb.lender_number
             AND l.loan_number = ptdb.loan_number
             AND l.loan_posting_date > :posteda
             AND l.loan_posting_date < :postedb
             AND SUBSTR (l.match_code, 1, 1) IN ('P', 'Y', 'X')
             AND l.lender_number = lp.lender_number (+)
                   AND l.loan_number = lp.loan_number (+)
                   AND lp.lender_number is null
             AND TRUNC (ptdb.last_change_date) - TRUNC (loan_posting_date) < 4
        GROUP BY l.lender_number,
                 lender_payee_code,
                 ptdb.payee_parcel_number,
                 l.loan_number) i,
       (SELECT   /*+ index (l XIE1LOAN) index (lp XPKLOAN_PARCEL) index (ptdb XIE1PTDB_TAX_LINE))*/
                 l.lender_number, ptdb.lender_payee_code,
                 ptdb.payee_parcel_number, l.loan_number,
                 COUNT (DISTINCT l.loan_number) AS COUNT
            FROM loan l, ptdb_tax_line ptdb, loan_parcel lp
           WHERE l.lender_number in (106,222,472,591,685,708,936)
             AND l.lender_number = ptdb.lender_number
             AND l.loan_number = ptdb.loan_number
             AND l.loan_posting_date > :posteda
             AND l.loan_posting_date < :postedb
             AND SUBSTR (l.match_code, 1, 1) IN ('P', 'Y', 'X')
             AND l.lender_number = lp.lender_number (+)
                   AND l.loan_number = lp.loan_number (+)
                   AND lp.lender_number is null
             AND TRUNC (ptdb.last_change_date) - TRUNC (loan_posting_date) < 5
        GROUP BY l.lender_number,
                 lender_payee_code,
                 ptdb.payee_parcel_number,
                 l.loan_number) j,
       (SELECT   /*+ index (l XIE1LOAN) index (lp XPKLOAN_PARCEL) index (ptdb XIE1PTDB_TAX_LINE))*/
                 l.lender_number, ptdb.lender_payee_code,
                 ptdb.payee_parcel_number, l.loan_number,
                 COUNT (DISTINCT l.loan_number) AS COUNT
            FROM loan l, ptdb_tax_line ptdb, loan_parcel lp
           WHERE l.lender_number in (106,222,472,591,685,708,936)
             AND l.lender_number = ptdb.lender_number
             AND l.loan_number = ptdb.loan_number
             AND l.loan_posting_date > :posteda
             AND l.loan_posting_date < :postedb
             AND SUBSTR (l.match_code, 1, 1) IN ('P', 'Y', 'X')
             AND l.lender_number = lp.lender_number (+)
                   AND l.loan_number = lp.loan_number (+)
                   AND lp.lender_number is null
             AND TRUNC (ptdb.last_change_date) - TRUNC (loan_posting_date) < 6
        GROUP BY l.lender_number,
                 lender_payee_code,
                 ptdb.payee_parcel_number,
                 l.loan_number) k,
       (SELECT   /*+ index (l XIE1LOAN) index (lp XPKLOAN_PARCEL) index (ptdb XIE1PTDB_TAX_LINE))*/
                 l.lender_number, ptdb.lender_payee_code,
                 ptdb.payee_parcel_number, l.loan_number,
                 COUNT (DISTINCT l.loan_number) AS COUNT
            FROM loan l, ptdb_tax_line ptdb, loan_parcel lp
           WHERE l.lender_number in (106,222,472,591,685,708,936)
             AND l.lender_number = ptdb.lender_number
             AND l.loan_number = ptdb.loan_number
             AND l.loan_posting_date > :posteda
             AND l.loan_posting_date < :postedb
             AND SUBSTR (l.match_code, 1, 1) IN ('P', 'Y', 'X')
             AND l.lender_number = lp.lender_number (+)
                   AND l.loan_number = lp.loan_number (+)
                   AND lp.lender_number is null
             AND TRUNC (ptdb.last_change_date) - TRUNC (loan_posting_date) < 7
        GROUP BY l.lender_number,
                 lender_payee_code,
                 ptdb.payee_parcel_number,
                 l.loan_number) l,
       (SELECT   /*+ index (l XIE1LOAN) index (lp XPKLOAN_PARCEL) index (ptdb XIE1PTDB_TAX_LINE))*/
                 l.lender_number, ptdb.lender_payee_code,
                 ptdb.payee_parcel_number, l.loan_number,
                 COUNT (DISTINCT l.loan_number) AS COUNT
            FROM loan l, ptdb_tax_line ptdb, loan_parcel lp
           WHERE l.lender_number in (106,222,472,591,685,708,936)
             AND l.lender_number = ptdb.lender_number
             AND l.loan_number = ptdb.loan_number
             AND l.loan_posting_date > :posteda
             AND l.loan_posting_date < :postedb
             AND SUBSTR (l.match_code, 1, 1) IN ('P', 'Y', 'X')
             AND l.lender_number = lp.lender_number (+)
                   AND l.loan_number = lp.loan_number (+)
                   AND lp.lender_number is null
             AND TRUNC (ptdb.last_change_date) - TRUNC (loan_posting_date) < 8
        GROUP BY l.lender_number,
                 lender_payee_code,
                 ptdb.payee_parcel_number,
                 l.loan_number) m,
       (SELECT   /*+ index (l XIE1LOAN) index (lp XPKLOAN_PARCEL) index (ptdb XIE1PTDB_TAX_LINE))*/
                 l.lender_number, ptdb.lender_payee_code,
                 ptdb.payee_parcel_number, l.loan_number,
                 COUNT (DISTINCT l.loan_number) AS COUNT
            FROM loan l, ptdb_tax_line ptdb, loan_parcel lp
           WHERE l.lender_number in (106,222,472,591,685,708,936)
             AND l.lender_number = ptdb.lender_number
             AND l.loan_number = ptdb.loan_number
             AND l.loan_posting_date > :posteda
             AND l.loan_posting_date < :postedb
             AND SUBSTR (l.match_code, 1, 1) IN ('P', 'Y', 'X')
             AND l.lender_number = lp.lender_number (+)
                   AND l.loan_number = lp.loan_number (+)
                   AND lp.lender_number is null
             AND TRUNC (ptdb.last_change_date) - TRUNC (loan_posting_date) < 9
        GROUP BY l.lender_number,
                 lender_payee_code,
                 ptdb.payee_parcel_number,
                 l.loan_number) n,
       (SELECT   /*+ index (l XIE1LOAN) index (lp XPKLOAN_PARCEL) index (ptdb XIE1PTDB_TAX_LINE))*/
                 l.lender_number, ptdb.lender_payee_code,
                 ptdb.payee_parcel_number, l.loan_number,
                 COUNT (DISTINCT l.loan_number) AS COUNT
            FROM loan l, ptdb_tax_line ptdb, loan_parcel lp
           WHERE l.lender_number in (106,222,472,591,685,708,936)
             AND l.lender_number = ptdb.lender_number
             AND l.loan_number = ptdb.loan_number
             AND l.loan_posting_date > :posteda
             AND l.loan_posting_date < :postedb
             AND l.lender_number = lp.lender_number (+)
                   AND l.loan_number = lp.loan_number (+)
                   AND lp.lender_number is null
             AND SUBSTR (l.match_code, 1, 1) IN ('P', 'Y', 'X')
             AND TRUNC (ptdb.last_change_date) - TRUNC (loan_posting_date) <
                                                                            10
        GROUP BY l.lender_number,
                 lender_payee_code,
                 ptdb.payee_parcel_number,
                 l.loan_number) o,
       (SELECT   /*+ index (l XIE1LOAN) index (lp XPKLOAN_PARCEL) index (ptdb XIE1PTDB_TAX_LINE))*/
                 l.lender_number, ptdb.lender_payee_code,
                 ptdb.payee_parcel_number, l.loan_number,
                 COUNT (DISTINCT l.loan_number) AS COUNT
            FROM loan l, ptdb_tax_line ptdb, loan_parcel lp
           WHERE l.lender_number in (106,222,472,591,685,708,936)
             AND l.lender_number = ptdb.lender_number
             AND l.loan_number = ptdb.loan_number
             AND l.loan_posting_date > :posteda
             AND l.loan_posting_date < :postedb
             AND l.lender_number = lp.lender_number (+)
                   AND l.loan_number = lp.loan_number (+)
                   AND lp.lender_number is null
             AND SUBSTR (l.match_code, 1, 1) IN ('P', 'Y', 'X')
             AND TRUNC (ptdb.last_change_date) - TRUNC (loan_posting_date) <
                                                                            11
        GROUP BY l.lender_number,
                 lender_payee_code,
                 ptdb.payee_parcel_number,
                 l.loan_number) p,
       (SELECT   /*+ index (l XIE1LOAN) index (lp XPKLOAN_PARCEL) index (ptdb XIE1PTDB_TAX_LINE))*/
                 l.lender_number, ptdb.lender_payee_code,
                 ptdb.payee_parcel_number, l.loan_number,
                 COUNT (DISTINCT l.loan_number) AS COUNT
            FROM loan l, ptdb_tax_line ptdb, loan_parcel lp
           WHERE l.lender_number in (106,222,472,591,685,708,936)
             AND l.lender_number = ptdb.lender_number
             AND l.loan_number = ptdb.loan_number
             AND l.loan_posting_date > :posteda
             AND l.loan_posting_date < :postedb
             AND l.lender_number = lp.lender_number (+)
                   AND l.loan_number = lp.loan_number (+)
                   AND lp.lender_number is null
             AND SUBSTR (l.match_code, 1, 1) IN ('P', 'Y', 'X')
             AND TRUNC (ptdb.last_change_date) - TRUNC (loan_posting_date) <
                                                                            12
        GROUP BY l.lender_number,
                 lender_payee_code,
                 ptdb.payee_parcel_number,
                 l.loan_number) q,
       (SELECT   /*+ index (l XIE1LOAN) index (lp XPKLOAN_PARCEL) index (ptdb XIE1PTDB_TAX_LINE))*/
                 l.lender_number, ptdb.lender_payee_code,
                 ptdb.payee_parcel_number, l.loan_number,
                 COUNT (DISTINCT l.loan_number) AS COUNT
            FROM loan l, ptdb_tax_line ptdb, loan_parcel lp
           WHERE l.lender_number in (106,222,472,591,685,708,936)
             AND l.lender_number = ptdb.lender_number
             AND l.loan_number = ptdb.loan_number
             AND l.loan_posting_date > :posteda
             AND l.loan_posting_date < :postedb
             AND l.lender_number = lp.lender_number (+)
                   AND l.loan_number = lp.loan_number (+)
                   AND lp.lender_number is null
             AND SUBSTR (l.match_code, 1, 1) IN ('P', 'Y', 'X')
             AND TRUNC (ptdb.last_change_date) - TRUNC (loan_posting_date) <
                                                                            13
        GROUP BY l.lender_number,
                 lender_payee_code,
                 ptdb.payee_parcel_number,
                 l.loan_number) r,
       (SELECT   /*+ index (l XIE1LOAN) index (lp XPKLOAN_PARCEL) index (ptdb XIE1PTDB_TAX_LINE))*/
                 l.lender_number, ptdb.lender_payee_code,
                 ptdb.payee_parcel_number, l.loan_number,
                 COUNT (DISTINCT l.loan_number) AS COUNT
            FROM loan l, ptdb_tax_line ptdb, loan_parcel lp
           WHERE l.lender_number in (106,222,472,591,685,708,936)
             AND l.lender_number = ptdb.lender_number
             AND l.loan_number = ptdb.loan_number
             AND l.loan_posting_date > :posteda
             AND l.loan_posting_date < :postedb
             AND l.lender_number = lp.lender_number (+)
                   AND l.loan_number = lp.loan_number (+)
                   AND lp.lender_number is null
             AND SUBSTR (l.match_code, 1, 1) IN ('P', 'Y', 'X')
             AND TRUNC (ptdb.last_change_date) - TRUNC (loan_posting_date) <
                                                                            14
        GROUP BY l.lender_number,
                 lender_payee_code,
                 ptdb.payee_parcel_number,
                 l.loan_number) s,
       (SELECT   /*+ index (l XIE1LOAN) index (lp XPKLOAN_PARCEL) index (ptdb XIE1PTDB_TAX_LINE))*/
                 l.lender_number, ptdb.lender_payee_code,
                 ptdb.payee_parcel_number, l.loan_number,
                 COUNT (DISTINCT l.loan_number) AS COUNT
            FROM loan l, ptdb_tax_line ptdb, loan_parcel lp
           WHERE l.lender_number in (106,222,472,591,685,708,936)
             AND l.lender_number = ptdb.lender_number
             AND l.loan_number = ptdb.loan_number
             AND l.loan_posting_date > :posteda
             AND l.loan_posting_date < :postedb
             AND l.lender_number = lp.lender_number (+)
                   AND l.loan_number = lp.loan_number (+)
                   AND lp.lender_number is null
             AND SUBSTR (l.match_code, 1, 1) IN ('P', 'Y', 'X')
             AND TRUNC (ptdb.last_change_date) - TRUNC (loan_posting_date) <
                                                                            15
        GROUP BY l.lender_number,
                 lender_payee_code,
                 ptdb.payee_parcel_number,
                 l.loan_number) t,
       (SELECT   /*+ index (l XIE1LOAN) index (lp XPKLOAN_PARCEL) index (ptdb XIE1PTDB_TAX_LINE))*/
                 l.lender_number, ptdb.lender_payee_code,
                 ptdb.payee_parcel_number, l.loan_number,
                 COUNT (DISTINCT l.loan_number) AS COUNT
            FROM loan l, ptdb_tax_line ptdb, loan_parcel lp
           WHERE l.lender_number in (106,222,472,591,685,708,936)
             AND l.lender_number = ptdb.lender_number
             AND l.loan_number = ptdb.loan_number
             AND l.loan_posting_date > :posteda
             AND l.loan_posting_date < :postedb
             AND l.lender_number = lp.lender_number (+)
                   AND l.loan_number = lp.loan_number (+)
                   AND lp.lender_number is null
             AND SUBSTR (l.match_code, 1, 1) IN ('P', 'Y', 'X')
             AND TRUNC (ptdb.last_change_date) - TRUNC (loan_posting_date) <
                                                                            16
        GROUP BY l.lender_number,
                 lender_payee_code,
                 ptdb.payee_parcel_number,
                 l.loan_number) u,
       (SELECT   /*+ index (l XIE1LOAN) index (lp XPKLOAN_PARCEL) index (ptdb XIE1PTDB_TAX_LINE))*/
                 l.lender_number, ptdb.lender_payee_code,
                 ptdb.payee_parcel_number, l.loan_number,
                 COUNT (DISTINCT l.loan_number) AS COUNT
            FROM loan l, ptdb_tax_line ptdb, loan_parcel lp
           WHERE l.lender_number in (106,222,472,591,685,708,936)
             AND l.lender_number = ptdb.lender_number
             AND l.loan_number = ptdb.loan_number
             AND l.loan_posting_date > :posteda
             AND l.loan_posting_date < :postedb
             AND l.lender_number = lp.lender_number (+)
                   AND l.loan_number = lp.loan_number (+)
                   AND lp.lender_number is null
             AND SUBSTR (l.match_code, 1, 1) IN ('P', 'Y', 'X')
             AND TRUNC (ptdb.last_change_date) - TRUNC (loan_posting_date) <
                                                                            17
        GROUP BY l.lender_number,
                 lender_payee_code,
                 ptdb.payee_parcel_number,
                 l.loan_number) v,
       (SELECT   /*+ index (l XIE1LOAN) index (lp XPKLOAN_PARCEL) index (ptdb XIE1PTDB_TAX_LINE))*/
                 l.lender_number, ptdb.lender_payee_code,
                 ptdb.payee_parcel_number, l.loan_number,
                 COUNT (DISTINCT l.loan_number) AS COUNT
            FROM loan l, ptdb_tax_line ptdb, loan_parcel lp
           WHERE l.lender_number in (106,222,472,591,685,708,936)
             AND l.lender_number = ptdb.lender_number
             AND l.loan_number = ptdb.loan_number
             AND l.loan_posting_date > :posteda
             AND l.loan_posting_date < :postedb
            AND l.lender_number = lp.lender_number (+)
                   AND l.loan_number = lp.loan_number (+)
                   AND lp.lender_number is null
             AND SUBSTR (l.match_code, 1, 1) IN ('P', 'Y', 'X')
             AND TRUNC (ptdb.last_change_date) - TRUNC (loan_posting_date) <
                                                                            18
        GROUP BY l.lender_number,
                 lender_payee_code,
                 ptdb.payee_parcel_number,
                 l.loan_number) w,
       (SELECT   /*+ index (l XIE1LOAN) index (lp XPKLOAN_PARCEL) index (ptdb XIE1PTDB_TAX_LINE))*/
                 l.lender_number, ptdb.lender_payee_code,
                 ptdb.payee_parcel_number, l.loan_number,
                 COUNT (DISTINCT l.loan_number) AS COUNT
            FROM loan l, ptdb_tax_line ptdb, loan_parcel lp
           WHERE l.lender_number in (106,222,472,591,685,708,936)
             AND l.lender_number = ptdb.lender_number
             AND l.loan_number = ptdb.loan_number
             AND l.loan_posting_date > :posteda
             AND l.loan_posting_date < :postedb
             AND l.lender_number = lp.lender_number (+)
                   AND l.loan_number = lp.loan_number (+)
                   AND lp.lender_number is null
             AND SUBSTR (l.match_code, 1, 1) IN ('P', 'Y', 'X')
             AND TRUNC (ptdb.last_change_date) - TRUNC (loan_posting_date) <
                                                                            19
        GROUP BY l.lender_number,
                 lender_payee_code,
                 ptdb.payee_parcel_number,
                 l.loan_number) x,
       (SELECT   /*+ index (l XIE1LOAN) index (lp XPKLOAN_PARCEL) index (ptdb XIE1PTDB_TAX_LINE))*/
                 l.lender_number, ptdb.lender_payee_code,
                 ptdb.payee_parcel_number, l.loan_number,
                 COUNT (DISTINCT l.loan_number) AS COUNT
            FROM loan l, ptdb_tax_line ptdb, loan_parcel lp
           WHERE l.lender_number in (106,222,472,591,685,708,936)
             AND l.lender_number = ptdb.lender_number
             AND l.loan_number = ptdb.loan_number
             AND l.loan_posting_date > :posteda
             AND l.loan_posting_date < :postedb
             AND l.lender_number = lp.lender_number (+)
                   AND l.loan_number = lp.loan_number (+)
                   AND lp.lender_number is null
             AND SUBSTR (l.match_code, 1, 1) IN ('P', 'Y', 'X')
             AND TRUNC (ptdb.last_change_date) - TRUNC (loan_posting_date) <
                                                                            20
        GROUP BY l.lender_number,
                 lender_payee_code,
                 ptdb.payee_parcel_number,
                 l.loan_number) y,
       (SELECT   /*+ index (l XIE1LOAN) index (lp XPKLOAN_PARCEL) index (ptdb XIE1PTDB_TAX_LINE))*/
                 l.lender_number, ptdb.lender_payee_code,
                 ptdb.payee_parcel_number, l.loan_number,
                 COUNT (DISTINCT l.loan_number) AS COUNT
            FROM loan l, ptdb_tax_line ptdb, loan_parcel lp
           WHERE l.lender_number in (106,222,472,591,685,708,936)
             AND l.lender_number = ptdb.lender_number
             AND l.loan_number = ptdb.loan_number
             AND l.loan_posting_date > :posteda
             AND l.loan_posting_date < :postedb
             AND l.lender_number = lp.lender_number (+)
                   AND l.loan_number = lp.loan_number (+)
                   AND lp.lender_number is null
             AND SUBSTR (l.match_code, 1, 1) IN ('P', 'Y', 'X')
             AND TRUNC (ptdb.last_change_date) - TRUNC (loan_posting_date) <
                                                                            21
        GROUP BY l.lender_number,
                 lender_payee_code,
                 ptdb.payee_parcel_number,
                 l.loan_number) z,
       (SELECT   /*+ index (l XIE1LOAN) index (lp XPKLOAN_PARCEL) index (ptdb XIE1PTDB_TAX_LINE))*/
                 l.lender_number, ptdb.lender_payee_code,
                 ptdb.payee_parcel_number, l.loan_number,
                 COUNT (DISTINCT l.loan_number) AS COUNT
            FROM loan l, ptdb_tax_line ptdb, loan_parcel lp
           WHERE l.lender_number in (106,222,472,591,685,708,936)
             AND l.lender_number = ptdb.lender_number
             AND l.loan_number = ptdb.loan_number
             AND l.loan_posting_date > :posteda
             AND l.loan_posting_date < :postedb
             AND l.lender_number = lp.lender_number (+)
                   AND l.loan_number = lp.loan_number (+)
                   AND lp.lender_number is null
             AND SUBSTR (l.match_code, 1, 1) IN ('P', 'Y', 'X')
             AND TRUNC (ptdb.last_change_date) - TRUNC (loan_posting_date) <
                                                                            22
        GROUP BY l.lender_number,
                 lender_payee_code,
                 ptdb.payee_parcel_number,
                 l.loan_number) aa,
       (SELECT   /*+ index (l XIE1LOAN) index (lp XPKLOAN_PARCEL) index (ptdb XIE1PTDB_TAX_LINE))*/
                 l.lender_number, ptdb.lender_payee_code,
                 ptdb.payee_parcel_number, l.loan_number,
                 COUNT (DISTINCT l.loan_number) AS COUNT
            FROM loan l, ptdb_tax_line ptdb, loan_parcel lp
           WHERE l.lender_number in (106,222,472,591,685,708,936)
             AND l.lender_number = ptdb.lender_number
             AND l.loan_number = ptdb.loan_number
             AND l.loan_posting_date > :posteda
             AND l.loan_posting_date < :postedb
             AND l.lender_number = lp.lender_number (+)
                   AND l.loan_number = lp.loan_number (+)
                   AND lp.lender_number is null
             AND SUBSTR (l.match_code, 1, 1) IN ('P', 'Y', 'X')
             AND TRUNC (ptdb.last_change_date) - TRUNC (loan_posting_date) <
                                                                            23
        GROUP BY l.lender_number,
                 lender_payee_code,
                 ptdb.payee_parcel_number,
                 l.loan_number) bb,
       (SELECT   /*+ index (l XIE1LOAN) index (lp XPKLOAN_PARCEL) index (ptdb XIE1PTDB_TAX_LINE))*/
                 l.lender_number, ptdb.lender_payee_code,
                 ptdb.payee_parcel_number, l.loan_number,
                 COUNT (DISTINCT l.loan_number) AS COUNT
            FROM loan l, ptdb_tax_line ptdb, loan_parcel lp
           WHERE l.lender_number in (106,222,472,591,685,708,936)
             AND l.lender_number = ptdb.lender_number
             AND l.loan_number = ptdb.loan_number
             AND l.loan_posting_date > :posteda
             AND l.loan_posting_date < :postedb
             AND l.lender_number = lp.lender_number (+)
                   AND l.loan_number = lp.loan_number (+)
                   AND lp.lender_number is null
             AND SUBSTR (l.match_code, 1, 1) IN ('P', 'Y', 'X')
             AND TRUNC (ptdb.last_change_date) - TRUNC (loan_posting_date) <
                                                                            24
        GROUP BY l.lender_number,
                 lender_payee_code,
                 ptdb.payee_parcel_number,
                 l.loan_number) cc,
       (SELECT   /*+ index (l XIE1LOAN) index (lp XPKLOAN_PARCEL) index (ptdb XIE1PTDB_TAX_LINE))*/
                 l.lender_number, ptdb.lender_payee_code,
                 ptdb.payee_parcel_number, l.loan_number,
                 COUNT (DISTINCT l.loan_number) AS COUNT
            FROM loan l, ptdb_tax_line ptdb, loan_parcel lp
           WHERE l.lender_number in (106,222,472,591,685,708,936)
             AND l.lender_number = ptdb.lender_number
             AND l.loan_number = ptdb.loan_number
             AND l.loan_posting_date > :posteda
             AND l.loan_posting_date < :postedb
             AND l.lender_number = lp.lender_number (+)
                   AND l.loan_number = lp.loan_number (+)
                   AND lp.lender_number is null
             AND SUBSTR (l.match_code, 1, 1) IN ('P', 'Y', 'X')
             AND TRUNC (ptdb.last_change_date) - TRUNC (loan_posting_date) <
                                                                            25
        GROUP BY l.lender_number,
                 lender_payee_code,
                 ptdb.payee_parcel_number,
                 l.loan_number) dd,
       (SELECT   /*+ index (l XIE1LOAN) index (lp XPKLOAN_PARCEL) index (ptdb XIE1PTDB_TAX_LINE))*/
                 l.lender_number, ptdb.lender_payee_code,
                 ptdb.payee_parcel_number, l.loan_number,
                 COUNT (DISTINCT l.loan_number) AS COUNT
            FROM loan l, ptdb_tax_line ptdb, loan_parcel lp
           WHERE l.lender_number in (106,222,472,591,685,708,936)
             AND l.lender_number = ptdb.lender_number
             AND l.loan_number = ptdb.loan_number
             AND l.loan_posting_date > :posteda
             AND l.loan_posting_date < :postedb
             AND l.lender_number = lp.lender_number (+)
                   AND l.loan_number = lp.loan_number (+)
                   AND lp.lender_number is null
             AND SUBSTR (l.match_code, 1, 1) IN ('P', 'Y', 'X')
             AND TRUNC (ptdb.last_change_date) - TRUNC (loan_posting_date) <
                                                                            26
        GROUP BY l.lender_number,
                 lender_payee_code,
                 ptdb.payee_parcel_number,
                 l.loan_number) ee,
       (SELECT   /*+ index (l XIE1LOAN) index (lp XPKLOAN_PARCEL) index (ptdb XIE1PTDB_TAX_LINE))*/
                 l.lender_number, ptdb.lender_payee_code,
                 ptdb.payee_parcel_number, l.loan_number,
                 COUNT (DISTINCT l.loan_number) AS COUNT
            FROM loan l, ptdb_tax_line ptdb, loan_parcel lp
           WHERE l.lender_number in (106,222,472,591,685,708,936)
             AND l.lender_number = ptdb.lender_number
             AND l.loan_number = ptdb.loan_number
             AND l.loan_posting_date > :posteda
             AND l.loan_posting_date < :postedb
            AND l.lender_number = lp.lender_number (+)
                   AND l.loan_number = lp.loan_number (+)
                   AND lp.lender_number is null
             AND SUBSTR (l.match_code, 1, 1) IN ('P', 'Y', 'X')
             AND TRUNC (ptdb.last_change_date) - TRUNC (loan_posting_date) <
                                                                            27
        GROUP BY l.lender_number,
                 lender_payee_code,
                 ptdb.payee_parcel_number,
                 l.loan_number) ff,
       (SELECT   /*+ index (l XIE1LOAN) index (lp XPKLOAN_PARCEL) index (ptdb XIE1PTDB_TAX_LINE))*/
                 l.lender_number, ptdb.lender_payee_code,
                 ptdb.payee_parcel_number, l.loan_number,
                 COUNT (DISTINCT l.loan_number) AS COUNT
            FROM loan l, ptdb_tax_line ptdb, loan_parcel lp
           WHERE l.lender_number in (106,222,472,591,685,708,936)
             AND l.lender_number = ptdb.lender_number
             AND l.loan_number = ptdb.loan_number
             AND l.loan_posting_date > :posteda
             AND l.loan_posting_date < :postedb
             AND l.lender_number = lp.lender_number (+)
                   AND l.loan_number = lp.loan_number (+)
                   AND lp.lender_number is null
             AND SUBSTR (l.match_code, 1, 1) IN ('P', 'Y', 'X')
             AND TRUNC (ptdb.last_change_date) - TRUNC (loan_posting_date) <
                                                                            28
        GROUP BY l.lender_number,
                 lender_payee_code,
                 ptdb.payee_parcel_number,
                 l.loan_number) gg,
       (SELECT   /*+ index (l XIE1LOAN) index (lp XPKLOAN_PARCEL) index (ptdb XIE1PTDB_TAX_LINE))*/
                 l.lender_number, ptdb.lender_payee_code,
                 ptdb.payee_parcel_number, l.loan_number,
                 COUNT (DISTINCT l.loan_number) AS COUNT
            FROM loan l, ptdb_tax_line ptdb, loan_parcel lp
           WHERE l.lender_number in (106,222,472,591,685,708,936)
             AND l.lender_number = ptdb.lender_number
             AND l.loan_number = ptdb.loan_number
             AND l.loan_posting_date > :posteda
             AND l.loan_posting_date < :postedb
             AND l.lender_number = lp.lender_number (+)
                   AND l.loan_number = lp.loan_number (+)
                   AND lp.lender_number is null
             AND SUBSTR (l.match_code, 1, 1) IN ('P', 'Y', 'X')
             AND TRUNC (ptdb.last_change_date) - TRUNC (loan_posting_date) <
                                                                            29
        GROUP BY l.lender_number,
                 lender_payee_code,
                 ptdb.payee_parcel_number,
                 l.loan_number) hh,
       (SELECT   /*+ index (l XIE1LOAN) index (lp XPKLOAN_PARCEL) index (ptdb XIE1PTDB_TAX_LINE))*/
                 l.lender_number, ptdb.lender_payee_code,
                 ptdb.payee_parcel_number, l.loan_number,
                 COUNT (DISTINCT l.loan_number) AS COUNT
            FROM loan l, ptdb_tax_line ptdb, loan_parcel lp
           WHERE l.lender_number in (106,222,472,591,685,708,936)
             AND l.lender_number = ptdb.lender_number
             AND l.loan_number = ptdb.loan_number
             AND l.loan_posting_date > :posteda
             AND l.loan_posting_date < :postedb
             AND l.lender_number = lp.lender_number (+)
                   AND l.loan_number = lp.loan_number (+)
                   AND lp.lender_number is null
             AND SUBSTR (l.match_code, 1, 1) IN ('P', 'Y', 'X')
             AND TRUNC (ptdb.last_change_date) - TRUNC (loan_posting_date) <
                                                                            30
        GROUP BY l.lender_number,
                 lender_payee_code,
                 ptdb.payee_parcel_number,
                 l.loan_number) ii,
       (SELECT   /*+ index (l XIE1LOAN) index (lp XPKLOAN_PARCEL) index (ptdb XIE1PTDB_TAX_LINE))*/
                 l.lender_number, ptdb.lender_payee_code,
                 ptdb.payee_parcel_number, l.loan_number,
                 COUNT (DISTINCT l.loan_number) AS COUNT
            FROM loan l, ptdb_tax_line ptdb, loan_parcel lp
           WHERE l.lender_number in (106,222,472,591,685,708,936)
             AND l.lender_number = ptdb.lender_number
             AND l.loan_number = ptdb.loan_number
             AND l.loan_posting_date > :posteda
             AND l.loan_posting_date < :postedb
             AND l.lender_number = lp.lender_number (+)
                   AND l.loan_number = lp.loan_number (+)
                   AND lp.lender_number is null
             AND SUBSTR (l.match_code, 1, 1) IN ('P', 'Y', 'X')
             AND TRUNC (ptdb.last_change_date) - TRUNC (loan_posting_date) <
                                                                            31
        GROUP BY l.lender_number,
                 lender_payee_code,
                 ptdb.payee_parcel_number,
                 l.loan_number) jj
 WHERE e.lender_number(+) = d.lender_number
   AND f.lender_number(+) = e.lender_number
   AND f.lender_payee_code(+) = e.lender_payee_code
   AND f.payee_parcel_number(+) = e.payee_parcel_number
   AND f.loan_number(+) = e.loan_number
   AND g.lender_number(+) = e.lender_number
   AND g.lender_payee_code(+) = e.lender_payee_code
   AND g.payee_parcel_number(+) = e.payee_parcel_number
   AND g.loan_number(+) = e.loan_number
   AND h.lender_number(+) = e.lender_number
   AND h.lender_payee_code(+) = e.lender_payee_code
   AND h.payee_parcel_number(+) = e.payee_parcel_number
   AND h.loan_number(+) = e.loan_number
   AND i.lender_number(+) = e.lender_number
   AND i.lender_payee_code(+) = e.lender_payee_code
   AND i.payee_parcel_number(+) = e.payee_parcel_number
   AND i.loan_number(+) = e.loan_number
   AND j.lender_number(+) = e.lender_number
   AND j.lender_payee_code(+) = e.lender_payee_code
   AND j.payee_parcel_number(+) = e.payee_parcel_number
   AND j.loan_number(+) = e.loan_number
   AND k.lender_number(+) = e.lender_number
   AND k.lender_payee_code(+) = e.lender_payee_code
   AND k.payee_parcel_number(+) = e.payee_parcel_number
   AND k.loan_number(+) = e.loan_number
   AND l.lender_number(+) = e.lender_number
   AND l.lender_payee_code(+) = e.lender_payee_code
   AND l.payee_parcel_number(+) = e.payee_parcel_number
   AND l.loan_number(+) = e.loan_number
   AND m.lender_number(+) = e.lender_number
   AND m.lender_payee_code(+) = e.lender_payee_code
   AND m.payee_parcel_number(+) = e.payee_parcel_number
   AND m.loan_number(+) = e.loan_number
   AND n.lender_number(+) = e.lender_number
   AND n.lender_payee_code(+) = e.lender_payee_code
   AND n.payee_parcel_number(+) = e.payee_parcel_number
   AND n.loan_number(+) = e.loan_number
   AND o.lender_number(+) = e.lender_number
   AND o.lender_payee_code(+) = e.lender_payee_code
   AND o.payee_parcel_number(+) = e.payee_parcel_number
   AND o.loan_number(+) = e.loan_number
   AND p.lender_number(+) = e.lender_number
   AND p.lender_payee_code(+) = e.lender_payee_code
   AND p.payee_parcel_number(+) = e.payee_parcel_number
   AND p.loan_number(+) = e.loan_number
   AND q.lender_number(+) = e.lender_number
   AND q.lender_payee_code(+) = e.lender_payee_code
   AND q.payee_parcel_number(+) = e.payee_parcel_number
   AND q.loan_number(+) = e.loan_number
   AND r.lender_number(+) = e.lender_number
   AND r.lender_payee_code(+) = e.lender_payee_code
   AND r.payee_parcel_number(+) = e.payee_parcel_number
   AND r.loan_number(+) = e.loan_number
   AND s.lender_number(+) = e.lender_number
   AND s.lender_payee_code(+) = e.lender_payee_code
   AND s.payee_parcel_number(+) = e.payee_parcel_number
   AND s.loan_number(+) = e.loan_number
   AND t.lender_number(+) = e.lender_number
   AND t.lender_payee_code(+) = e.lender_payee_code
   AND t.payee_parcel_number(+) = e.payee_parcel_number
   AND t.loan_number(+) = e.loan_number
   AND u.lender_number(+) = e.lender_number
   AND u.lender_payee_code(+) = e.lender_payee_code
   AND u.payee_parcel_number(+) = e.payee_parcel_number
   AND u.loan_number(+) = e.loan_number
   AND v.lender_number(+) = e.lender_number
   AND v.lender_payee_code(+) = e.lender_payee_code
   AND v.payee_parcel_number(+) = e.payee_parcel_number
   AND v.loan_number(+) = e.loan_number
   AND w.lender_number(+) = e.lender_number
   AND w.lender_payee_code(+) = e.lender_payee_code
   AND w.payee_parcel_number(+) = e.payee_parcel_number
   AND w.loan_number(+) = e.loan_number
   AND x.lender_number(+) = e.lender_number
   AND x.lender_payee_code(+) = e.lender_payee_code
   AND x.payee_parcel_number(+) = e.payee_parcel_number
   AND x.loan_number(+) = e.loan_number
   AND y.lender_number(+) = e.lender_number
   AND y.lender_payee_code(+) = e.lender_payee_code
   AND y.payee_parcel_number(+) = e.payee_parcel_number
   AND y.loan_number(+) = e.loan_number
   AND z.lender_number(+) = e.lender_number
   AND z.lender_payee_code(+) = e.lender_payee_code
   AND z.payee_parcel_number(+) = e.payee_parcel_number
   AND z.loan_number(+) = e.loan_number
   AND aa.lender_number(+) = e.lender_number
   AND aa.lender_payee_code(+) = e.lender_payee_code
   AND aa.payee_parcel_number(+) = e.payee_parcel_number
   AND aa.loan_number(+) = e.loan_number
   AND bb.lender_number(+) = e.lender_number
   AND bb.lender_payee_code(+) = e.lender_payee_code
   AND bb.payee_parcel_number(+) = e.payee_parcel_number
   AND bb.loan_number(+) = e.loan_number
   AND cc.lender_number(+) = e.lender_number
   AND cc.lender_payee_code(+) = e.lender_payee_code
   AND cc.payee_parcel_number(+) = e.payee_parcel_number
   AND cc.loan_number(+) = e.loan_number
   AND dd.lender_number(+) = e.lender_number
   AND dd.lender_payee_code(+) = e.lender_payee_code
   AND dd.payee_parcel_number(+) = e.payee_parcel_number
   AND dd.loan_number(+) = e.loan_number
   AND ee.lender_number(+) = e.lender_number
   AND ee.lender_payee_code(+) = e.lender_payee_code
   AND ee.payee_parcel_number(+) = e.payee_parcel_number
   AND ee.loan_number(+) = e.loan_number
   AND ff.lender_number(+) = e.lender_number
   AND ff.lender_payee_code(+) = e.lender_payee_code
   AND ff.payee_parcel_number(+) = e.payee_parcel_number
   AND ff.loan_number(+) = e.loan_number
   AND gg.lender_number(+) = e.lender_number
   AND gg.lender_payee_code(+) = e.lender_payee_code
   AND gg.payee_parcel_number(+) = e.payee_parcel_number
   AND gg.loan_number(+) = e.loan_number
   AND hh.lender_number(+) = e.lender_number
   AND hh.lender_payee_code(+) = e.lender_payee_code
   AND hh.payee_parcel_number(+) = e.payee_parcel_number
   AND hh.loan_number(+) = e.loan_number
   AND ii.lender_number(+) = e.lender_number
   AND ii.lender_payee_code(+) = e.lender_payee_code
   AND ii.payee_parcel_number(+) = e.payee_parcel_number
   AND ii.loan_number(+) = e.loan_number
   AND jj.lender_number(+) = e.lender_number
   AND jj.lender_payee_code(+) = e.lender_payee_code
   AND jj.payee_parcel_number(+) = e.payee_parcel_number
   AND jj.loan_number(+) = e.loan_number
UNION ALL
SELECT NULL AS "Total Loans Posted", NULL AS "Total Payee & Parcel Matches",
       NULL AS "Total Data Elements", 'ITLS Payee & Parcel' AS "Source",
       SUM (e.COUNT) "Loan Volume", 2 AS "Factor", SUM (e.COUNT) * 2 "Data Elements",
       NULL AS "Rate", SUM (f.COUNT) AS "On Day 1",
       SUM (g.COUNT) "By Day 2", SUM (h.COUNT) "By Day 3",
       SUM (i.COUNT) "By Day 4", SUM (j.COUNT) "By Day 5",
       SUM (k.COUNT) "By Day 6", SUM (l.COUNT) "By Day 7",
       SUM (m.COUNT) "By Day 8", SUM (n.COUNT) "By Day 9",
       SUM (o.COUNT) "By Day 10", SUM (p.COUNT) "By Day 11",
       SUM (q.COUNT) "By Day 12", SUM (r.COUNT) "By Day 13",
       SUM (s.COUNT) "By Day 14", SUM (t.COUNT) "By Day 15",
       SUM (u.COUNT) "By Day 16", SUM (v.COUNT) "By Day 17",
       SUM (w.COUNT) "By Day 18", SUM (x.COUNT) "By Day 19",
       SUM (y.COUNT) "By Day 20", SUM (z.COUNT) "By Day 21",
       SUM (aa.COUNT) "By Day 22", SUM (bb.COUNT) "By Day 23",
       SUM (cc.COUNT) "By Day 24", SUM (dd.COUNT) "By Day 25",
       SUM (ee.COUNT) "By Day 26", SUM (ff.COUNT) "By Day 27",
       SUM (gg.COUNT) "By Day 28", SUM (hh.COUNT) "By Day 29",
       SUM (ii.COUNT) "By Day 30", SUM (ii.COUNT) "By Day 31"
  FROM (SELECT   l.lender_number, COUNT (DISTINCT l.loan_number) AS COUNT
            FROM loan l
           WHERE l.lender_number in (106,222,472,591,685,708,936)
             AND l.loan_posting_date > :posteda
             AND l.loan_posting_date < :postedb
        GROUP BY l.lender_number) d,
       (SELECT   /*+ index (l XIE1LOAN) index (ptdb XIE1PTDB_TAX_LINE))*/
                 l.lender_number, lender_payee_code, ptdb.payee_parcel_number,
                 l.loan_number, COUNT (DISTINCT l.loan_number) AS COUNT
            FROM loan l, ptdb_tax_line ptdb, loan_parcel lp
           WHERE l.lender_number in (106,222,472,591,685,708,936)
             AND l.loan_posting_date > :posteda
             AND l.loan_posting_date < :postedb
             AND l.lender_number = ptdb.lender_number
             AND l.loan_number = ptdb.loan_number
                   AND SUBSTR (l.match_code, 1, 1) NOT IN ('P', 'Y', 'X')
             AND l.lender_number = lp.lender_number (+)
                   AND l.loan_number = lp.loan_number (+)
                   AND lp.lender_number is null
        GROUP BY l.lender_number,
                 lender_payee_code,
                 ptdb.payee_parcel_number,
                 l.loan_number) e,
       (SELECT   /*+ index (l XIE1LOAN) index (lp XPKLOAN_PARCEL) index (ptdb XIE1PTDB_TAX_LINE))*/
                 l.lender_number, ptdb.lender_payee_code,
                 ptdb.payee_parcel_number, l.loan_number,
                 COUNT (DISTINCT l.loan_number) AS COUNT
            FROM loan l, ptdb_tax_line ptdb, loan_parcel lp
           WHERE l.lender_number in (106,222,472,591,685,708,936)
             AND l.lender_number = ptdb.lender_number
             AND l.loan_number = ptdb.loan_number
                   AND SUBSTR (l.match_code, 1, 1) NOT IN ('P', 'Y', 'X')
             AND l.loan_posting_date > :posteda
             AND l.loan_posting_date < :postedb
             AND l.lender_number = lp.lender_number (+)
                   AND l.loan_number = lp.loan_number (+)
                   AND lp.lender_number is null
             AND TRUNC (ptdb.last_change_date) - TRUNC (loan_posting_date) < 1
        GROUP BY l.lender_number,
                 lender_payee_code,
                 ptdb.payee_parcel_number,
                 l.loan_number) f,
       (SELECT   /*+ index (l XIE1LOAN) index (lp XPKLOAN_PARCEL) index (ptdb XIE1PTDB_TAX_LINE))*/
                 l.lender_number, ptdb.lender_payee_code,
                 ptdb.payee_parcel_number, l.loan_number,
                 COUNT (DISTINCT l.loan_number) AS COUNT
            FROM loan l, ptdb_tax_line ptdb, loan_parcel lp
           WHERE l.lender_number in (106,222,472,591,685,708,936)
             AND l.lender_number = ptdb.lender_number
             AND l.loan_number = ptdb.loan_number
                   AND SUBSTR (l.match_code, 1, 1) NOT IN ('P', 'Y', 'X')
             AND l.loan_posting_date > :posteda
             AND l.loan_posting_date < :postedb
             AND l.lender_number = lp.lender_number (+)
                   AND l.loan_number = lp.loan_number (+)
                   AND lp.lender_number is null
             AND TRUNC (ptdb.last_change_date) - TRUNC (loan_posting_date) < 2
        GROUP BY l.lender_number,
                 lender_payee_code,
                 ptdb.payee_parcel_number,
                 l.loan_number) g,
       (SELECT   /*+ index (l XIE1LOAN) index (lp XPKLOAN_PARCEL) index (ptdb XIE1PTDB_TAX_LINE))*/
                 l.lender_number, ptdb.lender_payee_code,
                 ptdb.payee_parcel_number, l.loan_number,
                 COUNT (DISTINCT l.loan_number) AS COUNT
            FROM loan l, ptdb_tax_line ptdb, loan_parcel lp
           WHERE l.lender_number in (106,222,472,591,685,708,936)
             AND l.lender_number = ptdb.lender_number
             AND l.loan_number = ptdb.loan_number
                   AND SUBSTR (l.match_code, 1, 1) NOT IN ('P', 'Y', 'X')
             AND l.loan_posting_date > :posteda
             AND l.loan_posting_date < :postedb
             AND l.lender_number = lp.lender_number (+)
                   AND l.loan_number = lp.loan_number (+)
                   AND lp.lender_number is null
             AND TRUNC (ptdb.last_change_date) - TRUNC (loan_posting_date) < 3
        GROUP BY l.lender_number,
                 lender_payee_code,
                 ptdb.payee_parcel_number,
                 l.loan_number) h,
       (SELECT   /*+ index (l XIE1LOAN) index (lp XPKLOAN_PARCEL) index (ptdb XIE1PTDB_TAX_LINE))*/
                 l.lender_number, ptdb.lender_payee_code,
                 ptdb.payee_parcel_number, l.loan_number,
                 COUNT (DISTINCT l.loan_number) AS COUNT
            FROM loan l, ptdb_tax_line ptdb, loan_parcel lp
           WHERE l.lender_number in (106,222,472,591,685,708,936)
             AND l.lender_number = ptdb.lender_number
             AND l.loan_number = ptdb.loan_number
                   AND SUBSTR (l.match_code, 1, 1) NOT IN ('P', 'Y', 'X')
             AND l.loan_posting_date > :posteda
             AND l.loan_posting_date < :postedb
            AND l.lender_number = lp.lender_number (+)
                   AND l.loan_number = lp.loan_number (+)
                   AND lp.lender_number is null
             AND TRUNC (ptdb.last_change_date) - TRUNC (loan_posting_date) < 4
        GROUP BY l.lender_number,
                 lender_payee_code,
                 ptdb.payee_parcel_number,
                 l.loan_number) i,
       (SELECT   /*+ index (l XIE1LOAN) index (lp XPKLOAN_PARCEL) index (ptdb XIE1PTDB_TAX_LINE))*/
                 l.lender_number, ptdb.lender_payee_code,
                 ptdb.payee_parcel_number, l.loan_number,
                 COUNT (DISTINCT l.loan_number) AS COUNT
            FROM loan l, ptdb_tax_line ptdb, loan_parcel lp
           WHERE l.lender_number in (106,222,472,591,685,708,936)
             AND l.lender_number = ptdb.lender_number
             AND l.loan_number = ptdb.loan_number
                   AND SUBSTR (l.match_code, 1, 1) NOT IN ('P', 'Y', 'X')
             AND l.loan_posting_date > :posteda
             AND l.loan_posting_date < :postedb
             AND l.lender_number = lp.lender_number (+)
                   AND l.loan_number = lp.loan_number (+)
                   AND lp.lender_number is null
             AND TRUNC (ptdb.last_change_date) - TRUNC (loan_posting_date) < 5
        GROUP BY l.lender_number,
                 lender_payee_code,
                 ptdb.payee_parcel_number,
                 l.loan_number) j,
       (SELECT   /*+ index (l XIE1LOAN) index (lp XPKLOAN_PARCEL) index (ptdb XIE1PTDB_TAX_LINE))*/
                 l.lender_number, ptdb.lender_payee_code,
                 ptdb.payee_parcel_number, l.loan_number,
                 COUNT (DISTINCT l.loan_number) AS COUNT
            FROM loan l, ptdb_tax_line ptdb, loan_parcel lp
           WHERE l.lender_number in (106,222,472,591,685,708,936)
             AND l.lender_number = ptdb.lender_number
             AND l.loan_number = ptdb.loan_number
                   AND SUBSTR (l.match_code, 1, 1) NOT IN ('P', 'Y', 'X')
             AND l.loan_posting_date > :posteda
             AND l.loan_posting_date < :postedb
             AND l.lender_number = lp.lender_number (+)
                   AND l.loan_number = lp.loan_number (+)
                   AND lp.lender_number is null
             AND TRUNC (ptdb.last_change_date) - TRUNC (loan_posting_date) < 6
        GROUP BY l.lender_number,
                 lender_payee_code,
                 ptdb.payee_parcel_number,
                 l.loan_number) k,
       (SELECT   /*+ index (l XIE1LOAN) index (lp XPKLOAN_PARCEL) index (ptdb XIE1PTDB_TAX_LINE))*/
                 l.lender_number, ptdb.lender_payee_code,
                 ptdb.payee_parcel_number, l.loan_number,
                 COUNT (DISTINCT l.loan_number) AS COUNT
            FROM loan l, ptdb_tax_line ptdb, loan_parcel lp
           WHERE l.lender_number in (106,222,472,591,685,708,936)
             AND l.lender_number = ptdb.lender_number
             AND l.loan_number = ptdb.loan_number
                   AND SUBSTR (l.match_code, 1, 1) NOT IN ('P', 'Y', 'X')
             AND l.loan_posting_date > :posteda
             AND l.loan_posting_date < :postedb
             AND l.lender_number = lp.lender_number (+)
                   AND l.loan_number = lp.loan_number (+)
                   AND lp.lender_number is null
             AND TRUNC (ptdb.last_change_date) - TRUNC (loan_posting_date) < 7
        GROUP BY l.lender_number,
                 lender_payee_code,
                 ptdb.payee_parcel_number,
                 l.loan_number) l,
       (SELECT   /*+ index (l XIE1LOAN) index (lp XPKLOAN_PARCEL) index (ptdb XIE1PTDB_TAX_LINE))*/
                 l.lender_number, ptdb.lender_payee_code,
                 ptdb.payee_parcel_number, l.loan_number,
                 COUNT (DISTINCT l.loan_number) AS COUNT
            FROM loan l, ptdb_tax_line ptdb, loan_parcel lp
           WHERE l.lender_number in (106,222,472,591,685,708,936)
             AND l.lender_number = ptdb.lender_number
             AND l.loan_number = ptdb.loan_number
                   AND SUBSTR (l.match_code, 1, 1) NOT IN ('P', 'Y', 'X')
             AND l.loan_posting_date > :posteda
             AND l.loan_posting_date < :postedb
             AND l.lender_number = lp.lender_number (+)
                   AND l.loan_number = lp.loan_number (+)
                   AND lp.lender_number is null
             AND TRUNC (ptdb.last_change_date) - TRUNC (loan_posting_date) < 8
        GROUP BY l.lender_number,
                 lender_payee_code,
                 ptdb.payee_parcel_number,
                 l.loan_number) m,
       (SELECT   /*+ index (l XIE1LOAN) index (lp XPKLOAN_PARCEL) index (ptdb XIE1PTDB_TAX_LINE))*/
                 l.lender_number, ptdb.lender_payee_code,
                 ptdb.payee_parcel_number, l.loan_number,
                 COUNT (DISTINCT l.loan_number) AS COUNT
            FROM loan l, ptdb_tax_line ptdb, loan_parcel lp
           WHERE l.lender_number in (106,222,472,591,685,708,936)
             AND l.lender_number = ptdb.lender_number
             AND l.loan_number = ptdb.loan_number
                   AND SUBSTR (l.match_code, 1, 1) NOT IN ('P', 'Y', 'X')
             AND l.loan_posting_date > :posteda
             AND l.loan_posting_date < :postedb
             AND l.lender_number = lp.lender_number (+)
                   AND l.loan_number = lp.loan_number (+)
                   AND lp.lender_number is null
             AND TRUNC (ptdb.last_change_date) - TRUNC (loan_posting_date) < 9
        GROUP BY l.lender_number,
                 lender_payee_code,
                 ptdb.payee_parcel_number,
                 l.loan_number) n,
       (SELECT   /*+ index (l XIE1LOAN) index (lp XPKLOAN_PARCEL) index (ptdb XIE1PTDB_TAX_LINE))*/
                 l.lender_number, ptdb.lender_payee_code,
                 ptdb.payee_parcel_number, l.loan_number,
                 COUNT (DISTINCT l.loan_number) AS COUNT
            FROM loan l, ptdb_tax_line ptdb, loan_parcel lp
           WHERE l.lender_number in (106,222,472,591,685,708,936)
             AND l.lender_number = ptdb.lender_number
             AND l.loan_number = ptdb.loan_number
                   AND SUBSTR (l.match_code, 1, 1) NOT IN ('P', 'Y', 'X')
             AND l.loan_posting_date > :posteda
             AND l.loan_posting_date < :postedb
             AND l.lender_number = lp.lender_number (+)
                   AND l.loan_number = lp.loan_number (+)
                   AND lp.lender_number is null
             AND TRUNC (ptdb.last_change_date) - TRUNC (loan_posting_date) <
                                                                            10
        GROUP BY l.lender_number,
                 lender_payee_code,
                 ptdb.payee_parcel_number,
                 l.loan_number) o,
       (SELECT   /*+ index (l XIE1LOAN) index (lp XPKLOAN_PARCEL) index (ptdb XIE1PTDB_TAX_LINE))*/
                 l.lender_number, ptdb.lender_payee_code,
                 ptdb.payee_parcel_number, l.loan_number,
                 COUNT (DISTINCT l.loan_number) AS COUNT
            FROM loan l, ptdb_tax_line ptdb, loan_parcel lp
           WHERE l.lender_number in (106,222,472,591,685,708,936)
             AND l.lender_number = ptdb.lender_number
             AND l.loan_number = ptdb.loan_number
                   AND SUBSTR (l.match_code, 1, 1) NOT IN ('P', 'Y', 'X')
             AND l.loan_posting_date > :posteda
             AND l.loan_posting_date < :postedb
             AND l.lender_number = lp.lender_number (+)
                   AND l.loan_number = lp.loan_number (+)
                   AND lp.lender_number is null
             AND TRUNC (ptdb.last_change_date) - TRUNC (loan_posting_date) <
                                                                            11
        GROUP BY l.lender_number,
                 lender_payee_code,
                 ptdb.payee_parcel_number,
                 l.loan_number) p,
       (SELECT   /*+ index (l XIE1LOAN) index (lp XPKLOAN_PARCEL) index (ptdb XIE1PTDB_TAX_LINE))*/
                 l.lender_number, ptdb.lender_payee_code,
                 ptdb.payee_parcel_number, l.loan_number,
                 COUNT (DISTINCT l.loan_number) AS COUNT
            FROM loan l, ptdb_tax_line ptdb, loan_parcel lp
           WHERE l.lender_number in (106,222,472,591,685,708,936)
             AND l.lender_number = ptdb.lender_number
             AND l.loan_number = ptdb.loan_number
                   AND SUBSTR (l.match_code, 1, 1) NOT IN ('P', 'Y', 'X')
             AND l.loan_posting_date > :posteda
             AND l.loan_posting_date < :postedb
             AND l.lender_number = lp.lender_number (+)
                   AND l.loan_number = lp.loan_number (+)
                   AND lp.lender_number is null
             AND TRUNC (ptdb.last_change_date) - TRUNC (loan_posting_date) <
                                                                            12
        GROUP BY l.lender_number,
                 lender_payee_code,
                 ptdb.payee_parcel_number,
                 l.loan_number) q,
       (SELECT   /*+ index (l XIE1LOAN) index (lp XPKLOAN_PARCEL) index (ptdb XIE1PTDB_TAX_LINE))*/
                 l.lender_number, ptdb.lender_payee_code,
                 ptdb.payee_parcel_number, l.loan_number,
                 COUNT (DISTINCT l.loan_number) AS COUNT
            FROM loan l, ptdb_tax_line ptdb, loan_parcel lp
           WHERE l.lender_number in (106,222,472,591,685,708,936)
             AND l.lender_number = ptdb.lender_number
             AND l.loan_number = ptdb.loan_number
                   AND SUBSTR (l.match_code, 1, 1) NOT IN ('P', 'Y', 'X')
             AND l.loan_posting_date > :posteda
             AND l.loan_posting_date < :postedb
             AND l.lender_number = lp.lender_number (+)
                   AND l.loan_number = lp.loan_number (+)
                   AND lp.lender_number is null
             AND TRUNC (ptdb.last_change_date) - TRUNC (loan_posting_date) <
                                                                            13
        GROUP BY l.lender_number,
                 lender_payee_code,
                 ptdb.payee_parcel_number,
                 l.loan_number) r,
       (SELECT   /*+ index (l XIE1LOAN) index (lp XPKLOAN_PARCEL) index (ptdb XIE1PTDB_TAX_LINE))*/
                 l.lender_number, ptdb.lender_payee_code,
                 ptdb.payee_parcel_number, l.loan_number,
                 COUNT (DISTINCT l.loan_number) AS COUNT
            FROM loan l, ptdb_tax_line ptdb, loan_parcel lp
           WHERE l.lender_number in (106,222,472,591,685,708,936)
             AND l.lender_number = ptdb.lender_number
             AND l.loan_number = ptdb.loan_number
                   AND SUBSTR (l.match_code, 1, 1) NOT IN ('P', 'Y', 'X')
             AND l.loan_posting_date > :posteda
             AND l.loan_posting_date < :postedb
             AND l.lender_number = lp.lender_number (+)
                   AND l.loan_number = lp.loan_number (+)
                   AND lp.lender_number is null
             AND TRUNC (ptdb.last_change_date) - TRUNC (loan_posting_date) <
                                                                            14
        GROUP BY l.lender_number,
                 lender_payee_code,
                 ptdb.payee_parcel_number,
                 l.loan_number) s,
       (SELECT   /*+ index (l XIE1LOAN) index (lp XPKLOAN_PARCEL) index (ptdb XIE1PTDB_TAX_LINE))*/
                 l.lender_number, ptdb.lender_payee_code,
                 ptdb.payee_parcel_number, l.loan_number,
                 COUNT (DISTINCT l.loan_number) AS COUNT
            FROM loan l, ptdb_tax_line ptdb, loan_parcel lp
           WHERE l.lender_number in (106,222,472,591,685,708,936)
             AND l.lender_number = ptdb.lender_number
             AND l.loan_number = ptdb.loan_number
                   AND SUBSTR (l.match_code, 1, 1) NOT IN ('P', 'Y', 'X')
             AND l.loan_posting_date > :posteda
             AND l.loan_posting_date < :postedb
             AND l.lender_number = lp.lender_number (+)
                   AND l.loan_number = lp.loan_number (+)
                   AND lp.lender_number is null
             AND TRUNC (ptdb.last_change_date) - TRUNC (loan_posting_date) <
                                                                            15
        GROUP BY l.lender_number,
                 lender_payee_code,
                 ptdb.payee_parcel_number,
                 l.loan_number) t,
       (SELECT   /*+ index (l XIE1LOAN) index (lp XPKLOAN_PARCEL) index (ptdb XIE1PTDB_TAX_LINE))*/
                 l.lender_number, ptdb.lender_payee_code,
                 ptdb.payee_parcel_number, l.loan_number,
                 COUNT (DISTINCT l.loan_number) AS COUNT
            FROM loan l, ptdb_tax_line ptdb, loan_parcel lp
           WHERE l.lender_number in (106,222,472,591,685,708,936)
             AND l.lender_number = ptdb.lender_number
             AND l.loan_number = ptdb.loan_number
                   AND SUBSTR (l.match_code, 1, 1) NOT IN ('P', 'Y', 'X')
             AND l.loan_posting_date > :posteda
             AND l.loan_posting_date < :postedb
             AND l.lender_number = lp.lender_number (+)
                   AND l.loan_number = lp.loan_number (+)
                   AND lp.lender_number is null
             AND TRUNC (ptdb.last_change_date) - TRUNC (loan_posting_date) <
                                                                            16
        GROUP BY l.lender_number,
                 lender_payee_code,
                 ptdb.payee_parcel_number,
                 l.loan_number) u,
       (SELECT   /*+ index (l XIE1LOAN) index (lp XPKLOAN_PARCEL) index (ptdb XIE1PTDB_TAX_LINE))*/
                 l.lender_number, ptdb.lender_payee_code,
                 ptdb.payee_parcel_number, l.loan_number,
                 COUNT (DISTINCT l.loan_number) AS COUNT
            FROM loan l, ptdb_tax_line ptdb, loan_parcel lp
           WHERE l.lender_number in (106,222,472,591,685,708,936)
             AND l.lender_number = ptdb.lender_number
             AND l.loan_number = ptdb.loan_number
                   AND SUBSTR (l.match_code, 1, 1) NOT IN ('P', 'Y', 'X')
             AND l.loan_posting_date > :posteda
             AND l.loan_posting_date < :postedb
             AND l.lender_number = lp.lender_number (+)
                   AND l.loan_number = lp.loan_number (+)
                   AND lp.lender_number is null
             AND TRUNC (ptdb.last_change_date) - TRUNC (loan_posting_date) <
                                                                            17
        GROUP BY l.lender_number,
                 lender_payee_code,
                 ptdb.payee_parcel_number,
                 l.loan_number) v,
       (SELECT   /*+ index (l XIE1LOAN) index (lp XPKLOAN_PARCEL) index (ptdb XIE1PTDB_TAX_LINE))*/
                 l.lender_number, ptdb.lender_payee_code,
                 ptdb.payee_parcel_number, l.loan_number,
                 COUNT (DISTINCT l.loan_number) AS COUNT
            FROM loan l, ptdb_tax_line ptdb, loan_parcel lp
           WHERE l.lender_number in (106,222,472,591,685,708,936)
             AND l.lender_number = ptdb.lender_number
             AND l.loan_number = ptdb.loan_number
                   AND SUBSTR (l.match_code, 1, 1) NOT IN ('P', 'Y', 'X')
             AND l.loan_posting_date > :posteda
             AND l.loan_posting_date < :postedb
             AND l.lender_number = lp.lender_number (+)
                   AND l.loan_number = lp.loan_number (+)
                   AND lp.lender_number is null
             AND TRUNC (ptdb.last_change_date) - TRUNC (loan_posting_date) <
                                                                            18
        GROUP BY l.lender_number,
                 lender_payee_code,
                 ptdb.payee_parcel_number,
                 l.loan_number) w,
       (SELECT   /*+ index (l XIE1LOAN) index (lp XPKLOAN_PARCEL) index (ptdb XIE1PTDB_TAX_LINE))*/
                 l.lender_number, ptdb.lender_payee_code,
                 ptdb.payee_parcel_number, l.loan_number,
                 COUNT (DISTINCT l.loan_number) AS COUNT
            FROM loan l, ptdb_tax_line ptdb, loan_parcel lp
           WHERE l.lender_number in (106,222,472,591,685,708,936)
             AND l.lender_number = ptdb.lender_number
             AND l.loan_number = ptdb.loan_number
                   AND SUBSTR (l.match_code, 1, 1) NOT IN ('P', 'Y', 'X')
             AND l.loan_posting_date > :posteda
             AND l.loan_posting_date < :postedb
             AND l.lender_number = lp.lender_number (+)
                   AND l.loan_number = lp.loan_number (+)
                   AND lp.lender_number is null
             AND TRUNC (ptdb.last_change_date) - TRUNC (loan_posting_date) <
                                                                            19
        GROUP BY l.lender_number,
                 lender_payee_code,
                 ptdb.payee_parcel_number,
                 l.loan_number) x,
       (SELECT   /*+ index (l XIE1LOAN) index (lp XPKLOAN_PARCEL) index (ptdb XIE1PTDB_TAX_LINE))*/
                 l.lender_number, ptdb.lender_payee_code,
                 ptdb.payee_parcel_number, l.loan_number,
                 COUNT (DISTINCT l.loan_number) AS COUNT
            FROM loan l, ptdb_tax_line ptdb, loan_parcel lp
           WHERE l.lender_number in (106,222,472,591,685,708,936)
             AND l.lender_number = ptdb.lender_number
             AND l.loan_number = ptdb.loan_number
                   AND SUBSTR (l.match_code, 1, 1) NOT IN ('P', 'Y', 'X')
             AND l.loan_posting_date > :posteda
             AND l.loan_posting_date < :postedb
            AND l.lender_number = lp.lender_number (+)
                   AND l.loan_number = lp.loan_number (+)
                   AND lp.lender_number is null
             AND TRUNC (ptdb.last_change_date) - TRUNC (loan_posting_date) <
                                                                            20
        GROUP BY l.lender_number,
                 lender_payee_code,
                 ptdb.payee_parcel_number,
                 l.loan_number) y,
       (SELECT   /*+ index (l XIE1LOAN) index (lp XPKLOAN_PARCEL) index (ptdb XIE1PTDB_TAX_LINE))*/
                 l.lender_number, ptdb.lender_payee_code,
                 ptdb.payee_parcel_number, l.loan_number,
                 COUNT (DISTINCT l.loan_number) AS COUNT
            FROM loan l, ptdb_tax_line ptdb, loan_parcel lp
           WHERE l.lender_number in (106,222,472,591,685,708,936)
             AND l.lender_number = ptdb.lender_number
             AND l.loan_number = ptdb.loan_number
                   AND SUBSTR (l.match_code, 1, 1) NOT IN ('P', 'Y', 'X')
             AND l.loan_posting_date > :posteda
             AND l.loan_posting_date < :postedb
             AND l.lender_number = lp.lender_number (+)
                   AND l.loan_number = lp.loan_number (+)
                   AND lp.lender_number is null
             AND TRUNC (ptdb.last_change_date) - TRUNC (loan_posting_date) <
                                                                            21
        GROUP BY l.lender_number,
                 lender_payee_code,
                 ptdb.payee_parcel_number,
                 l.loan_number) z,
       (SELECT   /*+ index (l XIE1LOAN) index (lp XPKLOAN_PARCEL) index (ptdb XIE1PTDB_TAX_LINE))*/
                 l.lender_number, ptdb.lender_payee_code,
                 ptdb.payee_parcel_number, l.loan_number,
                 COUNT (DISTINCT l.loan_number) AS COUNT
            FROM loan l, ptdb_tax_line ptdb, loan_parcel lp
           WHERE l.lender_number in (106,222,472,591,685,708,936)
             AND l.lender_number = ptdb.lender_number
             AND l.loan_number = ptdb.loan_number
                   AND SUBSTR (l.match_code, 1, 1) NOT IN ('P', 'Y', 'X')
             AND l.loan_posting_date > :posteda
             AND l.loan_posting_date < :postedb
             AND l.lender_number = lp.lender_number (+)
                   AND l.loan_number = lp.loan_number (+)
                   AND lp.lender_number is null
             AND TRUNC (ptdb.last_change_date) - TRUNC (loan_posting_date) <
                                                                            22
        GROUP BY l.lender_number,
                 lender_payee_code,
                 ptdb.payee_parcel_number,
                 l.loan_number) aa,
       (SELECT   /*+ index (l XIE1LOAN) index (lp XPKLOAN_PARCEL) index (ptdb XIE1PTDB_TAX_LINE))*/
                 l.lender_number, ptdb.lender_payee_code,
                 ptdb.payee_parcel_number, l.loan_number,
                 COUNT (DISTINCT l.loan_number) AS COUNT
            FROM loan l, ptdb_tax_line ptdb, loan_parcel lp
           WHERE l.lender_number in (106,222,472,591,685,708,936)
             AND l.lender_number = ptdb.lender_number
             AND l.loan_number = ptdb.loan_number
                   AND SUBSTR (l.match_code, 1, 1) NOT IN ('P', 'Y', 'X')
             AND l.loan_posting_date > :posteda
             AND l.loan_posting_date < :postedb
             AND l.lender_number = lp.lender_number (+)
                   AND l.loan_number = lp.loan_number (+)
                   AND lp.lender_number is null
             AND TRUNC (ptdb.last_change_date) - TRUNC (loan_posting_date) <
                                                                            23
        GROUP BY l.lender_number,
                 lender_payee_code,
                 ptdb.payee_parcel_number,
                 l.loan_number) bb,
       (SELECT   /*+ index (l XIE1LOAN) index (lp XPKLOAN_PARCEL) index (ptdb XIE1PTDB_TAX_LINE))*/
                 l.lender_number, ptdb.lender_payee_code,
                 ptdb.payee_parcel_number, l.loan_number,
                 COUNT (DISTINCT l.loan_number) AS COUNT
            FROM loan l, ptdb_tax_line ptdb, loan_parcel lp
           WHERE l.lender_number in (106,222,472,591,685,708,936)
             AND l.lender_number = ptdb.lender_number
             AND l.loan_number = ptdb.loan_number
                   AND SUBSTR (l.match_code, 1, 1) NOT IN ('P', 'Y', 'X')
             AND l.loan_posting_date > :posteda
             AND l.loan_posting_date < :postedb
             AND l.lender_number = lp.lender_number (+)
                   AND l.loan_number = lp.loan_number (+)
                   AND lp.lender_number is null
             AND TRUNC (ptdb.last_change_date) - TRUNC (loan_posting_date) <
                                                                            24
        GROUP BY l.lender_number,
                 lender_payee_code,
                 ptdb.payee_parcel_number,
                 l.loan_number) cc,
       (SELECT   /*+ index (l XIE1LOAN) index (lp XPKLOAN_PARCEL) index (ptdb XIE1PTDB_TAX_LINE))*/
                 l.lender_number, ptdb.lender_payee_code,
                 ptdb.payee_parcel_number, l.loan_number,
                 COUNT (DISTINCT l.loan_number) AS COUNT
            FROM loan l, ptdb_tax_line ptdb, loan_parcel lp
           WHERE l.lender_number in (106,222,472,591,685,708,936)
             AND l.lender_number = ptdb.lender_number
             AND l.loan_number = ptdb.loan_number
                   AND SUBSTR (l.match_code, 1, 1) NOT IN ('P', 'Y', 'X')
             AND l.loan_posting_date > :posteda
             AND l.loan_posting_date < :postedb
             AND l.lender_number = lp.lender_number (+)
                   AND l.loan_number = lp.loan_number (+)
                   AND lp.lender_number is null
             AND TRUNC (ptdb.last_change_date) - TRUNC (loan_posting_date) <
                                                                            25
        GROUP BY l.lender_number,
                 lender_payee_code,
                 ptdb.payee_parcel_number,
                 l.loan_number) dd,
       (SELECT   /*+ index (l XIE1LOAN) index (lp XPKLOAN_PARCEL) index (ptdb XIE1PTDB_TAX_LINE))*/
                 l.lender_number, ptdb.lender_payee_code,
                 ptdb.payee_parcel_number, l.loan_number,
                 COUNT (DISTINCT l.loan_number) AS COUNT
            FROM loan l, ptdb_tax_line ptdb, loan_parcel lp
           WHERE l.lender_number in (106,222,472,591,685,708,936)
             AND l.lender_number = ptdb.lender_number
             AND l.loan_number = ptdb.loan_number
                   AND SUBSTR (l.match_code, 1, 1) NOT IN ('P', 'Y', 'X')
             AND l.loan_posting_date > :posteda
             AND l.loan_posting_date < :postedb
             AND l.lender_number = lp.lender_number (+)
                   AND l.loan_number = lp.loan_number (+)
                   AND lp.lender_number is null
             AND TRUNC (ptdb.last_change_date) - TRUNC (loan_posting_date) <
                                                                            26
        GROUP BY l.lender_number,
                 lender_payee_code,
                 ptdb.payee_parcel_number,
                 l.loan_number) ee,
       (SELECT   /*+ index (l XIE1LOAN) index (lp XPKLOAN_PARCEL) index (ptdb XIE1PTDB_TAX_LINE))*/
                 l.lender_number, ptdb.lender_payee_code,
                 ptdb.payee_parcel_number, l.loan_number,
                 COUNT (DISTINCT l.loan_number) AS COUNT
            FROM loan l, ptdb_tax_line ptdb, loan_parcel lp
           WHERE l.lender_number in (106,222,472,591,685,708,936)
             AND l.lender_number = ptdb.lender_number
             AND l.loan_number = ptdb.loan_number
                   AND SUBSTR (l.match_code, 1, 1) NOT IN ('P', 'Y', 'X')
             AND l.loan_posting_date > :posteda
             AND l.loan_posting_date < :postedb
             AND l.lender_number = lp.lender_number (+)
                   AND l.loan_number = lp.loan_number (+)
                   AND lp.lender_number is null
             AND TRUNC (ptdb.last_change_date) - TRUNC (loan_posting_date) <
                                                                            27
        GROUP BY l.lender_number,
                 lender_payee_code,
                 ptdb.payee_parcel_number,
                 l.loan_number) ff,
       (SELECT   /*+ index (l XIE1LOAN) index (lp XPKLOAN_PARCEL) index (ptdb XIE1PTDB_TAX_LINE))*/
                 l.lender_number, ptdb.lender_payee_code,
                 ptdb.payee_parcel_number, l.loan_number,
                 COUNT (DISTINCT l.loan_number) AS COUNT
            FROM loan l, ptdb_tax_line ptdb, loan_parcel lp
           WHERE l.lender_number in (106,222,472,591,685,708,936)
             AND l.lender_number = ptdb.lender_number
             AND l.loan_number = ptdb.loan_number
                   AND SUBSTR (l.match_code, 1, 1) NOT IN ('P', 'Y', 'X')
             AND l.loan_posting_date > :posteda
             AND l.loan_posting_date < :postedb
             AND l.lender_number = lp.lender_number (+)
                   AND l.loan_number = lp.loan_number (+)
                   AND lp.lender_number is null
             AND TRUNC (ptdb.last_change_date) - TRUNC (loan_posting_date) <
                                                                            28
        GROUP BY l.lender_number,
                 lender_payee_code,
                 ptdb.payee_parcel_number,
                 l.loan_number) gg,
       (SELECT   /*+ index (l XIE1LOAN) index (lp XPKLOAN_PARCEL) index (ptdb XIE1PTDB_TAX_LINE))*/
                 l.lender_number, ptdb.lender_payee_code,
                 ptdb.payee_parcel_number, l.loan_number,
                 COUNT (DISTINCT l.loan_number) AS COUNT
            FROM loan l, ptdb_tax_line ptdb, loan_parcel lp
           WHERE l.lender_number in (106,222,472,591,685,708,936)
             AND l.lender_number = ptdb.lender_number
             AND l.loan_number = ptdb.loan_number
                   AND SUBSTR (l.match_code, 1, 1) NOT IN ('P', 'Y', 'X')
             AND l.loan_posting_date > :posteda
             AND l.loan_posting_date < :postedb
             AND l.lender_number = lp.lender_number (+)
                   AND l.loan_number = lp.loan_number (+)
                   AND lp.lender_number is null
             AND TRUNC (ptdb.last_change_date) - TRUNC (loan_posting_date) <
                                                                            29
        GROUP BY l.lender_number,
                 lender_payee_code,
                 ptdb.payee_parcel_number,
                 l.loan_number) hh,
       (SELECT   /*+ index (l XIE1LOAN) index (lp XPKLOAN_PARCEL) index (ptdb XIE1PTDB_TAX_LINE))*/
                 l.lender_number, ptdb.lender_payee_code,
                 ptdb.payee_parcel_number, l.loan_number,
                 COUNT (DISTINCT l.loan_number) AS COUNT
            FROM loan l, ptdb_tax_line ptdb, loan_parcel lp
           WHERE l.lender_number in (106,222,472,591,685,708,936)
             AND l.lender_number = ptdb.lender_number
             AND l.loan_number = ptdb.loan_number
                   AND SUBSTR (l.match_code, 1, 1) NOT IN ('P', 'Y', 'X')
             AND l.loan_posting_date > :posteda
             AND l.loan_posting_date < :postedb
             AND l.lender_number = lp.lender_number (+)
                   AND l.loan_number = lp.loan_number (+)
                   AND lp.lender_number is null
             AND TRUNC (ptdb.last_change_date) - TRUNC (loan_posting_date) <
                                                                            30
        GROUP BY l.lender_number,
                 lender_payee_code,
                 ptdb.payee_parcel_number,
                 l.loan_number) ii,
       (SELECT   /*+ index (l XIE1LOAN) index (lp XPKLOAN_PARCEL) index (ptdb XIE1PTDB_TAX_LINE))*/
                 l.lender_number, ptdb.lender_payee_code,
                 ptdb.payee_parcel_number, l.loan_number,
                 COUNT (DISTINCT l.loan_number) AS COUNT
            FROM loan l, ptdb_tax_line ptdb, loan_parcel lp
           WHERE l.lender_number in (106,222,472,591,685,708,936)
             AND l.lender_number = ptdb.lender_number
             AND l.loan_number = ptdb.loan_number
                   AND SUBSTR (l.match_code, 1, 1) NOT IN ('P', 'Y', 'X')
             AND l.loan_posting_date > :posteda
             AND l.loan_posting_date < :postedb
             AND l.lender_number = lp.lender_number (+)
                   AND l.loan_number = lp.loan_number (+)
                   AND lp.lender_number is null
             AND TRUNC (ptdb.last_change_date) - TRUNC (loan_posting_date) <
                                                                            31
        GROUP BY l.lender_number,
                 lender_payee_code,
                 ptdb.payee_parcel_number,
                 l.loan_number) jj
 WHERE e.lender_number(+) = d.lender_number
   AND f.lender_number(+) = e.lender_number
   AND f.lender_payee_code(+) = e.lender_payee_code
   AND f.payee_parcel_number(+) = e.payee_parcel_number
   AND f.loan_number(+) = e.loan_number
   AND g.lender_number(+) = e.lender_number
   AND g.lender_payee_code(+) = e.lender_payee_code
   AND g.payee_parcel_number(+) = e.payee_parcel_number
   AND g.loan_number(+) = e.loan_number
   AND h.lender_number(+) = e.lender_number
   AND h.lender_payee_code(+) = e.lender_payee_code
   AND h.payee_parcel_number(+) = e.payee_parcel_number
   AND h.loan_number(+) = e.loan_number
   AND i.lender_number(+) = e.lender_number
   AND i.lender_payee_code(+) = e.lender_payee_code
   AND i.payee_parcel_number(+) = e.payee_parcel_number
   AND i.loan_number(+) = e.loan_number
   AND j.lender_number(+) = e.lender_number
   AND j.lender_payee_code(+) = e.lender_payee_code
   AND j.payee_parcel_number(+) = e.payee_parcel_number
   AND j.loan_number(+) = e.loan_number
   AND k.lender_number(+) = e.lender_number
   AND k.lender_payee_code(+) = e.lender_payee_code
   AND k.payee_parcel_number(+) = e.payee_parcel_number
   AND k.loan_number(+) = e.loan_number
   AND l.lender_number(+) = e.lender_number
   AND l.lender_payee_code(+) = e.lender_payee_code
   AND l.payee_parcel_number(+) = e.payee_parcel_number
   AND l.loan_number(+) = e.loan_number
   AND m.lender_number(+) = e.lender_number
   AND m.lender_payee_code(+) = e.lender_payee_code
   AND m.payee_parcel_number(+) = e.payee_parcel_number
   AND m.loan_number(+) = e.loan_number
   AND n.lender_number(+) = e.lender_number
   AND n.lender_payee_code(+) = e.lender_payee_code
   AND n.payee_parcel_number(+) = e.payee_parcel_number
   AND n.loan_number(+) = e.loan_number
   AND o.lender_number(+) = e.lender_number
   AND o.lender_payee_code(+) = e.lender_payee_code
   AND o.payee_parcel_number(+) = e.payee_parcel_number
   AND o.loan_number(+) = e.loan_number
   AND p.lender_number(+) = e.lender_number
   AND p.lender_payee_code(+) = e.lender_payee_code
   AND p.payee_parcel_number(+) = e.payee_parcel_number
   AND p.loan_number(+) = e.loan_number
   AND q.lender_number(+) = e.lender_number
   AND q.lender_payee_code(+) = e.lender_payee_code
   AND q.payee_parcel_number(+) = e.payee_parcel_number
   AND q.loan_number(+) = e.loan_number
   AND r.lender_number(+) = e.lender_number
   AND r.lender_payee_code(+) = e.lender_payee_code
   AND r.payee_parcel_number(+) = e.payee_parcel_number
   AND r.loan_number(+) = e.loan_number
   AND s.lender_number(+) = e.lender_number
   AND s.lender_payee_code(+) = e.lender_payee_code
   AND s.payee_parcel_number(+) = e.payee_parcel_number
   AND s.loan_number(+) = e.loan_number
   AND t.lender_number(+) = e.lender_number
   AND t.lender_payee_code(+) = e.lender_payee_code
   AND t.payee_parcel_number(+) = e.payee_parcel_number
   AND t.loan_number(+) = e.loan_number
   AND u.lender_number(+) = e.lender_number
   AND u.lender_payee_code(+) = e.lender_payee_code
   AND u.payee_parcel_number(+) = e.payee_parcel_number
   AND u.loan_number(+) = e.loan_number
   AND v.lender_number(+) = e.lender_number
   AND v.lender_payee_code(+) = e.lender_payee_code
   AND v.payee_parcel_number(+) = e.payee_parcel_number
   AND v.loan_number(+) = e.loan_number
   AND w.lender_number(+) = e.lender_number
   AND w.lender_payee_code(+) = e.lender_payee_code
   AND w.payee_parcel_number(+) = e.payee_parcel_number
   AND w.loan_number(+) = e.loan_number
   AND x.lender_number(+) = e.lender_number
   AND x.lender_payee_code(+) = e.lender_payee_code
   AND x.payee_parcel_number(+) = e.payee_parcel_number
   AND x.loan_number(+) = e.loan_number
   AND y.lender_number(+) = e.lender_number
   AND y.lender_payee_code(+) = e.lender_payee_code
   AND y.payee_parcel_number(+) = e.payee_parcel_number
   AND y.loan_number(+) = e.loan_number
   AND z.lender_number(+) = e.lender_number
   AND z.lender_payee_code(+) = e.lender_payee_code
   AND z.payee_parcel_number(+) = e.payee_parcel_number
   AND z.loan_number(+) = e.loan_number
   AND aa.lender_number(+) = e.lender_number
   AND aa.lender_payee_code(+) = e.lender_payee_code
   AND aa.payee_parcel_number(+) = e.payee_parcel_number
   AND aa.loan_number(+) = e.loan_number
   AND bb.lender_number(+) = e.lender_number
   AND bb.lender_payee_code(+) = e.lender_payee_code
   AND bb.payee_parcel_number(+) = e.payee_parcel_number
   AND bb.loan_number(+) = e.loan_number
   AND cc.lender_number(+) = e.lender_number
   AND cc.lender_payee_code(+) = e.lender_payee_code
   AND cc.payee_parcel_number(+) = e.payee_parcel_number
   AND cc.loan_number(+) = e.loan_number
   AND dd.lender_number(+) = e.lender_number
   AND dd.lender_payee_code(+) = e.lender_payee_code
   AND dd.payee_parcel_number(+) = e.payee_parcel_number
   AND dd.loan_number(+) = e.loan_number
   AND ee.lender_number(+) = e.lender_number
   AND ee.lender_payee_code(+) = e.lender_payee_code
   AND ee.payee_parcel_number(+) = e.payee_parcel_number
   AND ee.loan_number(+) = e.loan_number
   AND ff.lender_number(+) = e.lender_number
   AND ff.lender_payee_code(+) = e.lender_payee_code
   AND ff.payee_parcel_number(+) = e.payee_parcel_number
   AND ff.loan_number(+) = e.loan_number
   AND gg.lender_number(+) = e.lender_number
   AND gg.lender_payee_code(+) = e.lender_payee_code
   AND gg.payee_parcel_number(+) = e.payee_parcel_number
   AND gg.loan_number(+) = e.loan_number
   AND hh.lender_number(+) = e.lender_number
   AND hh.lender_payee_code(+) = e.lender_payee_code
   AND hh.payee_parcel_number(+) = e.payee_parcel_number
   AND hh.loan_number(+) = e.loan_number
   AND ii.lender_number(+) = e.lender_number
   AND ii.lender_payee_code(+) = e.lender_payee_code
   AND ii.payee_parcel_number(+) = e.payee_parcel_number
   AND ii.loan_number(+) = e.loan_number
   AND jj.lender_number(+) = e.lender_number
   AND jj.lender_payee_code(+) = e.lender_payee_code
   AND jj.payee_parcel_number(+) = e.payee_parcel_number
   AND jj.loan_number(+) = e.loan_number
0
ksummers
Asked:
ksummers
3 Solutions
 
aesmikeCommented:
holly crap, ksummers!  That's the biggest dang SQL command I've ever seen!
Was there a question in there or did EE truncate you?
0
 
ksummersAuthor Commented:
LOL, I know!  I am looking for suggestions on simplifying this, hopefully using loops.  I did not write this SQL.  I am still trying to figure it out.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this looks like it is oracle :-)
and yes, that IS very large SQL ...

now, it contains the "old" syntax for outer join:
   AND f.loan_number(+) = e.loan_number

which should be replaced by the new syntax:

select ...
from f
left join e
on  f.loan_number = e.loan_number

that syntax will not only be more readable, but might get better chances to get good execution plan


for the rest, it is simply too big, so we cannot understand what it is doing without working for hours with you on some table diagrams.
you should try to get somme (eventually materialized) views in place to break the problem into parts...
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
slightwv (䄆 Netminder) Commented:
I also have to agree with aesmike.......   DAMN !!!

There's no way I can go through that to get enough understanding to offer exact advice on possible temp tables/loops/etc...

But we can offer great advice to help you figure it out.

You need to break it down and hit the trouble spots.  First, there are 3 'UNION ALL's.  I'd run each specific piece separately and tune them individually.

On a quick scan I'm betting it is the biggest problem is the last one with the 31 inline views all outer joined all over the place.

Once you locate the major offending section, continue drilling down into the sub-selects.

If the main problem is the 31 inline views, I'd look at pulling the core SQL out into a view that either generates 31 rows or 31 columns and access the view 1 time to pull back all the data.  This would get rid of 31 individual table accesses (assuming you can write the view w/o 31 unions).
0
 
ksummersAuthor Commented:
Well, if you look at the 3rd and 4th set of SQL statements, they are a series of SQL selects which are based on the days of the month 1 .. 31.  Is there a way to get them into one loop w/o all the repeats?
0
 
slightwv (䄆 Netminder) Commented:
I'm not 100% sure there is because I'm not familiar with the data.  I'm pretty much going by gut feeling here.  I see something that complex and repeating, I look for the elegant over the brute force.

Before we all spend a lot of time playing with those specific queries, are they the ones performing badly?

If you can come up with a simplified example I'm sure one of us here can come up with the SQL.  If you can create some simple table definitions, some sample data and expected results it would help a lot.
0
 
WallaceAdrianCommented:
Most of the query repeats for each day range. e.g.

SELECT   /*+ index (l XIE1LOAN) index (lp XPKLOAN_PARCEL) index (ptdb XIE1PTDB_TAX_LINE))*/
                 l.lender_number, ptdb.lender_payee_code,
                 ptdb.payee_parcel_number, l.loan_number,
                 COUNT (DISTINCT l.loan_number) AS COUNT
            FROM loan l, ptdb_tax_line ptdb, loan_parcel lp
           WHERE l.lender_number in (106,222,472,591,685,708,936)
             AND l.lender_number = ptdb.lender_number
             AND l.loan_number = ptdb.loan_number
                   AND SUBSTR (l.match_code, 1, 1) NOT IN ('P', 'Y', 'X')
             AND l.loan_posting_date > :posteda
             AND l.loan_posting_date < :postedb
             AND l.lender_number = lp.lender_number (+)
                   AND l.loan_number = lp.loan_number (+)
                   AND lp.lender_number is null
             AND TRUNC (ptdb.last_change_date) - TRUNC (loan_posting_date) <
                                                                            31
        GROUP BY l.lender_number,
                 lender_payee_code,
                 ptdb.payee_parcel_number,
                 l.loan_number

It looks like this is counting the number of distinct loan numbers over the previous number of days.

Using the following example, you could rewrite this set of queries into one.

I created a table called tmp_test_loan with 2 columns and filled it as below. The days column is the equivalent of your TRUNC (ptdb.last_change_date) - TRUNC (loan_posting_date)

DAYS      LOAN_NUMBER
1      1
1      2
1      3
2      4
2      5
3      6
4      7
4      8
4      9
5      10
2      8

This query returns the total number of distinct loan numbers over the last 31 days

select day_list.day_number,
  count(distinct loan_number) loan_numbers
from tmp_test_loan t, (select rownum day_number from user_objects where rownum <= 31) day_list
where day_list.day_number >= t.days
group by day_list.day_number

The subquery (select rownum day_number from user_objects where rownum <= 31) gives you a list of 31 numbers.

So you could replace all the queries with a day restriction with this

SELECT   /*+ index (l XIE1LOAN) index (lp XPKLOAN_PARCEL) index (ptdb XIE1PTDB_TAX_LINE))*/
                 day_list.day_number,
                 l.lender_number, ptdb.lender_payee_code,
                 ptdb.payee_parcel_number, l.loan_number,
                 COUNT (DISTINCT l.loan_number) AS COUNT
            FROM loan l, ptdb_tax_line ptdb, loan_parcel lp,
              (select rownum day_number from user_objects where rownum <= 31) day_list
           WHERE l.lender_number in (106,222,472,591,685,708,936)
             AND l.lender_number = ptdb.lender_number
             AND l.loan_number = ptdb.loan_number
             AND l.loan_posting_date > :posteda
             AND l.loan_posting_date < :postedb
             AND SUBSTR (l.match_code, 1, 1) IN ('P', 'Y', 'X')
             AND l.lender_number = lp.lender_number (+)
                   AND l.loan_number = lp.loan_number (+)
                   AND lp.lender_number is null
             AND day_list.day_number >= TRUNC (ptdb.last_change_date) - TRUNC (loan_posting_date)
        GROUP BY day_list.day_number,
                 l.lender_number,
                 lender_payee_code,
                 ptdb.payee_parcel_number,
                 l.loan_number



0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now