sql server programming:
Hi All,
I need a suggestion to modify existing code. Is it possible to modify the
below code to replace EXISTS clause with a UDF where function can use SET
ROWCOUNT 1 etc to return me true or false rather than me using EXISTS AND NOT
EXISTS logic here.
What are the alternatives to avoid using EXISTS AND NOT EXISTS?? I hate
using NOT EXISTS but need suggestions to replace it.
Any help will be greatly appreciated.
declare @portfolio_id NUMERIC
SET @portfolio_id = 90004573
SELECT COUNT(*) FROM
Org
, Issuer
, Debt
, Debt_curn_rating
, mdysid.DBO.portfolio_issuer portfolio_issuer
where portfolio_issuer.portfolio_id = @portfolio_id
and Org.org_id = convert(int,portfolio_issuer.issuer_number)
and Issuer.issr_num = Org.org_id
and (Issuer.fr_ref = "C"
or not exists(
select CFRAME.fr_ref
from Issuer CFRAME
where CFRAME.issr_num = Issuer.issr_num
and CFRAME.fr_ref = "C"
))
and exists(
select Debt.inst_id
from Debt
, Debt_curn_rating
where Debt.issr_num = Org.org_id
and Debt.debt_clss_cd in(73371)
and Debt_curn_rating.inst_id = Debt.inst_id
and Debt_curn_rating.ratg_txt != "NAV"
and Debt_curn_rating.ratg_txt != "TWR"
and (Debt_curn_rating.ratg_txt != "WR"
or datediff(month,Debt_curn_rating.ratg_dt,getdate()) <= 3)
and Debt_curn_rating.watch_indc = "N"
and not exists(
select OTHER.inst_id
from Debt OTHER
, Debt_curn_rating OTHER_curn_rating
where OTHER.issr_num = Org.org_id
and OTHER.debt_clss_cd in(73371)
and OTHER_curn_rating.inst_id = OTHER.inst_id
and OTHER_curn_rating.ratg_txt != "NAV"
and OTHER_curn_rating.ratg_txt != "TWR"
and (OTHER_curn_rating.ratg_txt != "WR"
or datediff(month,OTHER_curn_rating.ratg_dt,getdate()) <= 3)
and OTHER_curn_rating.watch_indc = "N"
and OTHER.inst_id != Debt.inst_id
)
)
and Debt.issr_num = Org.org_id
and Debt.debt_clss_cd in(73371)
and Debt_curn_rating.inst_id = Debt.inst_id
and Debt_curn_rating.ratg_txt != "NAV"
and Debt_curn_rating.ratg_txt != "TWR"
and (Debt_curn_rating.ratg_txt != "WR"
or datediff(month,Debt_curn_rating.ratg_dt,getdate()) <= 3)
