Nov 27, 2007
Hi...
Is it possible to eliminate values from a select statements if they are NULL or Blank..
the reason i have use cast as decimals is because that value is a varchar in my database... and i want to eliminate those values from my Final select statement that dont is blank so that i will have a data set which may be only 1 - 10 long...
ALTER PROCEDURE [dbo].[rpt_ParticipantPlanPeriodInvActivity]
@PlanId int,
@ParticipantId int,
@PeriodId int
AS
DECLARE @tbl table
(
tblId smallint IDENTITY(1,1),
ParticipantId int,
LoanId int,
Name1 char(2),
NDesc1 char(30),
TotAct1 decimal(19,4),
Name2 char(2),
NDesc2 char(30),
TotAct2 decimal(19,4),
Name3 char(2),
NDesc3 char(30),
TotAct3 decimal(19,4),
Name4 Char(2),
NDesc4 char(30),
TotAct4 decimal(19,4),
Name5 char(2),
NDesc5 char(30),
TotAct5 decimal(19,4),
Name6 char(2),
NDesc6 char(30),
TotAct6 decimal(19,4),
Name7 char(2),
NDesc7 char(30),
TotAct7 decimal(19,4),
Name8 char(2),
NDesc8 char(30),
TotAct8 decimal(19,4),
Name9 char(2),
NDesc9 char(30),
TotAct9 decimal(19,4),
Name10 char(2),
NDesc10 char(30),
TotAct10 decimal(19,4),
Name11 char(2),
NDesc11 char(30),
TotAct11 decimal(19,4),
Name12 char(2),
NDesc12 char(30),
TotAct12 decimal(19,4),
Name13 char(2),
NDesc13 char(30),
TotAct13 decimal(19,4),
Name14 char(2),
NDesc14 char(30),
TotAct14 decimal(19,4),
Name15 char(2),
NDesc15 char(30),
TotAct15 decimal(19,4),
Name16 char(2),
NDesc16 char(30),
TotAct16 decimal(19,4),
Name17 char(2),
NDesc17 char(30),
TotAct17 decimal(19,4),
Name18 char(2),
NDesc18 char(30),
TotAct18 decimal(19,4),
Name19 char(2),
NDesc19 char(30),
TotAct19 decimal(19,4),
Name20 char(2),
NDesc20 char(30),
TotAct20 decimal(19,4)
)
Insert Into @tbl
SELECT
pf.ParticipantId,
pf.FundId as LoanId,
--CASE When FundName Is Null Then ShortName ELSE FundName END as FundNames,
--pf.PortfolioId,
--PortfolioName,
Act1 as Name1,
a.Description as NDesc1,
cast(TotAct1 as decimal(19,4)) ,
Act2 as Name2,
b.Description as NDesc2,
Cast(TotAct2 as decimal(19,4)),
Act3 as Name3,
c.Description as NDesc3,
Cast(TotAct3 as decimal(19,4)),
Act4 as Name4,
d.Description as NDesc4,
Cast(TotAct4 as decimal(19,4)),
Act5 as Name5,
e.Description as NDesc5,
Cast(TotAct5 as decimal(19,4)),
Act6 as Name6,
fi.Description as NDesc6,
Cast(TotAct6 as decimal(19,4)),
Act7 as Name7,
g.Description as NDesc7,
Cast(TotAct7 as decimal(19,4)),
Act8 as Name8,
h.Description as NDesc8,
Cast(TotAct8 as decimal(19,4)),
Act9 as Name9,
i.Description as NDesc9,
Cast(TotAct9 as decimal(19,4)),
Act10 as Name10,
j.Description as NDesc10,
Cast(TotAct10 as decimal(19,4)),
Act11 as Name11,
k.Description as NDesc11,
Cast(TotAct11 as decimal(19,4)),
Act12 as Name12,
l.Description as NDesc12,
Cast(TotAct12 as decimal(19,4)),
Act13 as Name13,
m.Description as NDesc13,
Cast(TotAct13 as decimal(19,4)),
Act14 as Name14,
n.Description as NDesc14,
Cast(TotAct14 as decimal(19,4)),
Act15 as Name15,
o.Description as NDesc15,
Cast(TotAct15 as decimal(19,4)),
Act16 as Name16,
p1.Description as NDesc16,
Cast(TotAct16 as decimal(19,4)),
Act17 as Name17,
q.Description as NDesc17,
Cast(TotAct17 as decimal(19,4)),
Act18 as Name18,
r.Description as NDesc18,
Cast(TotAct18 as decimal(19,4)),
Act19 as Name19,
s.Description as NDesc19,
Cast(TotAct19 as decimal(19,4)),
Act20 as Name20,
t.Description as NDesc20,
Cast(TotAct20 as decimal(19,4))
FROM
ParticipantPlanFundBalances1 pf
Left Outer JOIN Fund f
On f.FundId = pf.FundId
LEFT Join PlanPortfolio p
On pf.PortfolioId = p.PortfolioId
Left outer Join AscActCodes a
on pf.Act1 = a.Name
left outer Join AscActCodes b
on pf.Act2 = b.Name
left outer Join AscActCodes c
on pf.Act3 = c.Name
left outer Join AscActCodes d
on pf.Act4 = d.Name
left outer Join AscActCodes e
on pf.Act5 = e.Name
left outer Join AscActCodes fi
on pf.Act6 = fi.Name
left outer Join AscActCodes g
on pf.Act7 = g.Name
left outer Join AscActCodes h
on pf.Act8 = h.Name
left Outer Join AscActCodes i
on pf.Act9 = i.Name
left Outer Join AscActCodes j
on pf.Act10 = j.Name
left outer Join AscActCodes k
on pf.Act11 = k.Name
left outer Join AscActCodes l
on pf.Act12 = l.Name
left outer Join AscActCodes m
on pf.Act13 = m.Name
left outer Join AscActCodes n
on pf.Act14 = n.Name
left outer Join AscActCodes o
on pf.Act15 = o.Name
left outer Join AscActCodes p1
on pf.Act16 = p1.Name
left outer Join AscActCodes q
on pf.Act17 = q.Name
left outer Join AscActCodes r
on pf.Act18 = r.Name
left outer Join AscActCodes s
on pf.Act19 = s.Name
left outer Join AscActCodes t
on pf.Act20 = t.Name
WHERE
pf.FundId = 0
AND
PeriodId = @PeriodId
AND
pf.PlanId = @PlanId
AND
pf.ParticipantId = @ParticipantId
--Get the Fund information for the report and combine it with the Loan information
-- in the table variable...
SELECT
pf.ParticipantId,
pf.PortfolioId,
PortfolioName,
pf.FundId LoanFundId,
CASE When FundName Is Null Then ShortName ELSE FundName END as FundNames,
Act1 as Name1,
a.Description as NDesc1,
Cast(TotAct1 as decimal(19,4)),
Act2 as Name2,
b.Description as NDesc2,
Cast(TotAct2 as decimal(19,4)),
Act3 as Name3,
c.Description as NDesc3,
Cast(TotAct3 as decimal(19,4)),
Act4 as Name4,
d.Description as NDesc4,
Cast(TotAct4 as decimal(19,4)),
Act5 as Name5,
e.Description as NDesc5,
Cast(TotAct5 as decimal(19,4)),
Act6 as Name6,
fi.Description as NDesc6,
Cast(TotAct6 as decimal(19,4)),
Act7 as Name7,
g.Description as NDesc7,
Cast(TotAct7 as decimal(19,4)),
Act8 as Name8,
h.Description as NDesc8,
Cast(TotAct8 as decimal(19,4)),
Act9 as Name9,
i.Description as NDesc9,
Cast(TotAct9 as decimal(19,4)),
Act10 as Name10,
j.Description as NDesc10,
Cast(TotAct10 as decimal(19,4)),
Act11 as Name11,
k.Description as NDesc11,
Cast(TotAct11 as decimal(19,4)),
Act12 as Name12,
l.Description as NDesc12,
Cast(TotAct12 as decimal(19,4)),
Act13 as Name13,
m.Description as NDesc13,
Cast(TotAct13 as decimal(19,4)),
Act14 as Name14,
n.Description as NDesc14,
Cast(TotAct14 as decimal(19,4)),
Act15 as Name15,
o.Description as NDesc15,
Cast(TotAct15 as decimal(19,4)),
Act16 as Name16,
p1.Description as NDesc16,
Cast(TotAct16 as decimal(19,4)),
Act17 as Name17,
q.Description as NDesc17,
Cast(TotAct17 as decimal(19,4)),
Act18 as Name18,
r.Description as NDesc18,
Cast(TotAct18 as decimal(19,4)),
Act19 as Name19,
s.Description as NDesc19,
Cast(TotAct19 as decimal(19,4)),
Act20 as Name20,
t.Description as NDesc20,
Cast(TotAct20 as decimal(19,4))
FROM
ParticipantPlanFundBalances1 pf
Left Outer JOIN Fund f
On f.FundId = pf.FundId
LEFT Join PlanPortfolio p
On pf.PortfolioId = p.PortfolioId
Left outer Join AscActCodes a
on pf.Act1 = a.Name
left outer Join AscActCodes b
on pf.Act2 = b.Name
left outer Join AscActCodes c
on pf.Act3 = c.Name
left outer Join AscActCodes d
on pf.Act4 = d.Name
left outer Join AscActCodes e
on pf.Act5 = e.Name
left outer Join AscActCodes fi
on pf.Act6 = fi.Name
left outer Join AscActCodes g
on pf.Act7 = g.Name
left outer Join AscActCodes h
on pf.Act8 = h.Name
left Outer Join AscActCodes i
on pf.Act9 = i.Name
left Outer Join AscActCodes j
on pf.Act10 = j.Name
left outer Join AscActCodes k
on pf.Act11 = k.Name
left outer Join AscActCodes l
on pf.Act12 = l.Name
left outer Join AscActCodes m
on pf.Act13 = m.Name
left outer Join AscActCodes n
on pf.Act14 = n.Name
left outer Join AscActCodes o
on pf.Act15 = o.Name
left outer Join AscActCodes p1
on pf.Act16 = p1.Name
left outer Join AscActCodes q
on pf.Act17 = q.Name
left outer Join AscActCodes r
on pf.Act18 = r.Name
left outer Join AscActCodes s
on pf.Act19 = s.Name
left outer Join AscActCodes t
on pf.Act20 = t.Name
WHERE
pf.FundId <> 0
AND
PeriodId = @PeriodId
AND
pf.PlanId = @PlanId
AND
ParticipantId = @ParticipantId
Union
SELECT
ParticipantId,
0,
'NA',
LoanId,
'Loan ' + cast(tblId as char(1)),
Name1,
NDesc1,
Cast(TotAct1 as decimal(19,4)),
Name2,
NDesc2,
Cast(TotAct2 as decimal(19,4)),
Name3,
NDesc3,
Cast(TotAct3 as decimal(19,4)),
Name4,
NDesc4,
Cast(TotAct4 as decimal(19,4)),
Name5,
NDesc5,
Cast(TotAct5 as decimal(19,4)),
Name6,
NDesc6,
Cast(TotAct6 as decimal(19,4)),
Name7,
NDesc7,
Cast(TotAct7 as decimal(19,4)),
Name8,
NDesc8,
Cast(TotAct8 as decimal(19,4)),
Name9,
NDesc9,
Cast(TotAct9 as decimal(19,4)),
Name10,
NDesc10,
Cast(TotAct10 as decimal(19,4)),
Name11,
NDesc11,
Cast(TotAct11 as decimal(19,4)),
Name12,
NDesc12,
Cast(TotAct12 as decimal(19,4)),
Name13,
NDesc13,
Cast(TotAct13 as decimal(19,4)),
Name14,
NDesc14,
Cast(TotAct14 as decimal(19,4)),
Name15,
NDesc15,
Cast(TotAct15 as decimal(19,4)),
Name16,
NDesc16,
Cast(TotAct16 as decimal(19,4)),
Name17,
NDesc17,
Cast(TotAct17 as decimal(19,4)),
Name18,
NDesc18,
Cast(TotAct18 as decimal(19,4)),
Name19,
NDesc19,
Cast(TotAct19 as decimal(19,4)),
Name20,
NDesc20,
Cast(TotAct20 as decimal(19,4))
FROM @tbl
Any help will be appreciated.
Regards
Karen
View 10 Replies
View Related
