Re: using Select count(*)
From: ddf <oratune_at_msn.com>
Date: Tue, 31 Mar 2009 13:19:42 -0700 (PDT)
Message-ID: <ab2ebc2b-9048-4880-87f0-11179bd0fce0_at_e10g2000vbe.googlegroups.com>
On Mar 31, 2:45 pm, Brock <wade.br..._at_yahoo.com> wrote:
> 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;
begin
effdate := to_date(to_char('01/01' || pyear), 'mm/dd/yyyy'); open r_cursor for
Date: Tue, 31 Mar 2009 13:19:42 -0700 (PDT)
Message-ID: <ab2ebc2b-9048-4880-87f0-11179bd0fce0_at_e10g2000vbe.googlegroups.com>
On Mar 31, 2:45 pm, Brock <wade.br..._at_yahoo.com> wrote:
> 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;
Why won't this work:
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
and (upper(getprintnameline(RealValNoticeListingOwner
(p.pr_id, pyear, 1)))) in
(select (upper(getprintnameline(RealValNoticeListingOwner
(pr_id, pyear, 1))))
from property_t1
group by (upper(getprintnameline
(RealValNoticeListingOwner(p.pr_id, pyear, 1))))
having count(*) > 4);
end AppealsReportsInfo;
David Fitzjarrell Received on Tue Mar 31 2009 - 15:19:42 CDT