using Select count(*)

From: Brock <wade.brock_at_yahoo.com>
Date: Tue, 31 Mar 2009 12:45:29 -0700 (PDT)
Message-ID: <ab859e75-0ece-404d-89a9-d1eef54ca54d_at_m24g2000vbp.googlegroups.com>

Thanks in advance for any clues... I am rather new to Oracle so here goes. I have a package body / query that is returning data with no problems. I do however need to take this package body and rewrite the code above the " select p.pr_p_i_n "PIN"," line to allow me to use the " Select count(*)" function to return only a set of records that have 5 or more records for each "ListingOwnername1". Any ideas?

Procedure AppealsReportsInfo(beg_nbh in number,

                               end_nbh  in number,
                               pyear    in number,
                               r_cursor out reportcur) is

    effdate date;
  begin
    effdate := to_date(to_char('01/01' || pyear), 'mm/dd/yyyy');     open r_cursor for

     select p.pr_p_i_n "PIN",
             p.pr_blklot "Block Lot",
             ap.api_keyed_date "Appeal Date",
             ap.api_fmv_now "Appraised Value",
             ap.api_ask_price "Asking Price",
             ap.api_net_increase "Increase",
             ap.api_net_decrease "Decrease",
             getneighborhoodrevalappr(nb.nbh_id) "Appraiser",
             ap.api_new_value "New Value",
             ap.api_remarks "Remarks",
             ap.api_year "Tax Year",
             ap.api_ptc_number "PTC Number",
             ap.api_completion_date "Completed Date",
             ap.api_av14_date "AV14 Date",
             ap.api_inf_date "Informal Date",
             bd.bd_date "BER Date",
             ul.ul_user_login "User Login",
             a3.ad_description "App Type",
             a2.ad_description "Status",
             a1.ad_description "Outcome",
             nb.nbh_number "Neighborhood",
             nbi.nbhi_name "Neighborhood Name",
             nb.nbh_id "NBH ID",
             ju.ju_jurisdiction_name,

            (upper(getprintnameline(RealValNoticeListingOwner(p.pr_id,
                                                               pyear,
                                                               1))))

ListingOwnername1,

             (Select count(*)
                from propertyinfo_t1 pi
               where pi.pi_nbh_id = nb.nbh_id
                 and pi.pi_end_date is null
                 and pi.pi_eff_date >= effdate) "Property Count"

        from appeals_t1              a,
             property_t1             p,
             propertyinfo_t1         pi,
             appealsinfo_t1          ap,
             attributedescription_t1 a1,
             attributedescription_t1 a2,
             attributedescription_t1 a3,
             neighborhoods_t1        nb,
             neighborhoodsinfo_t1    nbi,
             userlogins_t1           ul,
             berdates_t1             bd,
             jurisslice_t1      js,
             jurisalteration_t1 ja,
             jurisdiction_t1    ju,
             listinginfo_t1     lsi,
             listing_t1         ls

       where a.app_pr_id = pi.pi_pr_id
         and ap.api_app_id = a.app_id
         and ap.api_end_date is null
         and ap.api_outcome_ad_id = a1.ad_id(+)
         and ap.api_appstatus_ad_id = a2.ad_id(+)
         and a.app_type_ad_id = a3.ad_id(+)
         and p.pr_id = pi.pi_pr_id
         and pi.pi_nbh_id = nb.nbh_id(+)
         and nb.nbh_id = nbi.nbhi_nbh_id
         and nb.nbh_date_archived is null
         and nbi.nbhi_end_date is null
         and nbi.nbhi_eff_date = effdate
         and nb.nbh_number >= beg_nbh
         and nb.nbh_number <= end_nbh
         and pi.pi_end_date is null
         and pi.pi_eff_date >= effdate
         and ap.api_completion_init_ul_id = ul.ul_id(+)
         and ap.api_berdate_bd_id = bd.bd_id(+)
         and js.js_ju_id = ju.ju_id
         and js.js_ja_id = ja.ja_id
         and ja.ja_pr_id = lsi.lsi_re_pr_id
         and lsi.lsi_situs_eff_date = ja.ja_eff_date
         and ja.ja_end_date is null
         and lsi.lsi_year = pyear
         and lsi.lsi_end_date is null
         and lsi.lsi_activated = 'Y'
         and lsi.lsi_ls_id = ls.ls_id
         and p.pr_id = ls.ls_acct_pr_id;

  end AppealsReportsInfo; Received on Tue Mar 31 2009 - 14:45:29 CDT