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]@.PlanIdint,@.ParticipantIdint,@.PeriodIdintASDECLARE @.tbl table (tblId smallint IDENTITY(1,1),ParticipantIdint,LoanIdint, Name1char(2), NDesc1char(30),TotAct1decimal(19,4),Name2char(2), NDesc2char(30), TotAct2decimal(19,4),Name3char(2), NDesc3char(30),TotAct3decimal(19,4),Name4 Char(2), NDesc4char(30),TotAct4decimal(19,4),Name5char(2),NDesc5char(30),TotAct5decimal(19,4),Name6char(2),NDesc6char(30), TotAct6decimal(19,4),Name7char(2),NDesc7char(30),TotAct7decimal(19,4), Name8char(2),NDesc8char(30),TotAct8decimal(19,4),Name9char(2),NDesc9char(30),TotAct9decimal(19,4), Name10char(2),NDesc10char(30),TotAct10decimal(19,4),Name11char(2),NDesc11char(30),TotAct11decimal(19,4),Name12char(2),NDesc12char(30),TotAct12decimal(19,4),Name13char(2),NDesc13char(30),TotAct13decimal(19,4), Name14char(2),NDesc14char(30), TotAct14decimal(19,4),Name15char(2),NDesc15char(30),TotAct15decimal(19,4),Name16char(2),NDesc16char(30),TotAct16decimal(19,4),Name17char(2),NDesc17char(30),TotAct17decimal(19,4),Name18char(2),NDesc18char(30),TotAct18decimal(19,4), Name19char(2),NDesc19char(30),TotAct19decimal(19,4),Name20char(2),NDesc20char(30),TotAct20decimal(19,4) )Insert Into @.tbl SELECTpf.ParticipantId,pf.FundIdas LoanId, --CASE When FundName Is Null Then ShortName ELSE FundName ENDas FundNames, --pf.PortfolioId,--PortfolioName, Act1as Name1, a.Descriptionas NDesc1, cast(TotAct1as decimal(19,4)) ,Act2as Name2, b.Descriptionas NDesc2,Cast(TotAct2as decimal(19,4)),Act3as Name3, c.Descriptionas NDesc3,Cast(TotAct3as decimal(19,4)),Act4as Name4, d.Descriptionas NDesc4,Cast(TotAct4as decimal(19,4)),Act5as Name5,e.Descriptionas NDesc5,Cast(TotAct5as decimal(19,4)),Act6as Name6, fi.Descriptionas NDesc6,Cast(TotAct6as decimal(19,4)),Act7as Name7,g.Descriptionas NDesc7,Cast(TotAct7as decimal(19,4)),Act8as Name8,h.Descriptionas NDesc8,Cast(TotAct8as decimal(19,4)),Act9as Name9, i.Descriptionas NDesc9,Cast(TotAct9as decimal(19,4)),Act10as Name10, j.Descriptionas NDesc10,Cast(TotAct10as decimal(19,4)),Act11as Name11,k.Descriptionas NDesc11,Cast(TotAct11as decimal(19,4)),Act12as Name12,l.Descriptionas NDesc12,Cast(TotAct12as decimal(19,4)),Act13as Name13, m.Descriptionas NDesc13,Cast(TotAct13as decimal(19,4)),Act14as Name14,n.Descriptionas NDesc14,Cast(TotAct14as decimal(19,4)),Act15as Name15,o.Descriptionas NDesc15,Cast(TotAct15as decimal(19,4)),Act16as Name16,p1.Descriptionas NDesc16,Cast(TotAct16as decimal(19,4)),Act17as Name17,q.Descriptionas NDesc17,Cast(TotAct17as decimal(19,4)),Act18as Name18,r.Descriptionas NDesc18,Cast(TotAct18as decimal(19,4)),Act19as Name19, s.Descriptionas NDesc19,Cast(TotAct19as decimal(19,4)),Act20as Name20,t.Descriptionas NDesc20,Cast(TotAct20as decimal(19,4))FROM ParticipantPlanFundBalances1 pfLeft Outer JOIN Fund f On f.FundId = pf.FundIdLEFT Join PlanPortfolio pOn 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.Nameleft outer Join AscActCodes e on pf.Act5 = e.Nameleft outer Join AscActCodes fi on pf.Act6 = fi.Nameleft outer Join AscActCodes g on pf.Act7 = g.Nameleft outer Join AscActCodes h on pf.Act8 = h.Nameleft Outer Join AscActCodes i on pf.Act9 = i.Nameleft Outer Join AscActCodes j on pf.Act10 = j.Name left outer Join AscActCodes k on pf.Act11 = k.Nameleft outer Join AscActCodes l on pf.Act12 = l.Nameleft outer Join AscActCodes m on pf.Act13 = m.Nameleft outer Join AscActCodes n on pf.Act14 = n.Nameleft outer Join AscActCodes o on pf.Act15 = o.Nameleft outer Join AscActCodes p1 on pf.Act16 = p1.Nameleft outer Join AscActCodes q on pf.Act17 = q.Nameleft outer Join AscActCodes r on pf.Act18 = r.Nameleft outer Join AscActCodes s on pf.Act19 = s.Nameleft outer Join AscActCodes t on pf.Act20 = t.Name WHERE pf.FundId = 0 ANDPeriodId = @.PeriodIdANDpf.PlanId = @.PlanIdANDpf.ParticipantId = @.ParticipantId--Get the Fund informationfor the report and combine it with the Loan information--in the table variable...SELECTpf.ParticipantId,pf.PortfolioId,PortfolioName,pf.FundId LoanFundId, CASE When FundName Is Null Then ShortName ELSE FundName ENDas FundNames,Act1as Name1, a.Descriptionas NDesc1, Cast(TotAct1as decimal(19,4)),Act2as Name2, b.Descriptionas NDesc2,Cast(TotAct2as decimal(19,4)),Act3as Name3, c.Descriptionas NDesc3,Cast(TotAct3as decimal(19,4)),Act4as Name4, d.Descriptionas NDesc4,Cast(TotAct4as decimal(19,4)),Act5as Name5,e.Descriptionas NDesc5,Cast(TotAct5as decimal(19,4)),Act6as Name6, fi.Descriptionas NDesc6,Cast(TotAct6as decimal(19,4)),Act7as Name7,g.Descriptionas NDesc7,Cast(TotAct7as decimal(19,4)),Act8as Name8,h.Descriptionas NDesc8,Cast(TotAct8as decimal(19,4)),Act9as Name9, i.Descriptionas NDesc9,Cast(TotAct9as decimal(19,4)),Act10as Name10, j.Descriptionas NDesc10,Cast(TotAct10as decimal(19,4)),Act11as Name11,k.Descriptionas NDesc11,Cast(TotAct11as decimal(19,4)),Act12as Name12,l.Descriptionas NDesc12,Cast(TotAct12as decimal(19,4)),Act13as Name13, m.Descriptionas NDesc13,Cast(TotAct13as decimal(19,4)),Act14as Name14,n.Descriptionas NDesc14,Cast(TotAct14as decimal(19,4)),Act15as Name15,o.Descriptionas NDesc15,Cast(TotAct15as decimal(19,4)),Act16as Name16,p1.Descriptionas NDesc16,Cast(TotAct16as decimal(19,4)),Act17as Name17,q.Descriptionas NDesc17,Cast(TotAct17as decimal(19,4)),Act18as Name18,r.Descriptionas NDesc18,Cast(TotAct18as decimal(19,4)),Act19as Name19, s.Descriptionas NDesc19,Cast(TotAct19as decimal(19,4)),Act20as Name20,t.Descriptionas NDesc20,Cast(TotAct20as decimal(19,4))FROM ParticipantPlanFundBalances1 pfLeft Outer JOIN Fund f On f.FundId = pf.FundIdLEFT Join PlanPortfolio pOn 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.Nameleft outer Join AscActCodes e on pf.Act5 = e.Nameleft outer Join AscActCodes fi on pf.Act6 = fi.Nameleft outer Join AscActCodes g on pf.Act7 = g.Nameleft outer Join AscActCodes h on pf.Act8 = h.Nameleft Outer Join AscActCodes i on pf.Act9 = i.Nameleft Outer Join AscActCodes j on pf.Act10 = j.Name left outer Join AscActCodes k on pf.Act11 = k.Nameleft outer Join AscActCodes l on pf.Act12 = l.Nameleft outer Join AscActCodes m on pf.Act13 = m.Nameleft outer Join AscActCodes n on pf.Act14 = n.Nameleft outer Join AscActCodes o on pf.Act15 = o.Nameleft outer Join AscActCodes p1 on pf.Act16 = p1.Nameleft outer Join AscActCodes q on pf.Act17 = q.Nameleft outer Join AscActCodes r on pf.Act18 = r.Nameleft outer Join AscActCodes s on pf.Act19 = s.Nameleft outer Join AscActCodes t on pf.Act20 = t.Name WHEREpf.FundId <> 0 ANDPeriodId = @.PeriodIdANDpf.PlanId = @.PlanIdANDParticipantId = @.ParticipantIdUnionSELECTParticipantId,0,'NA',LoanId,'Loan ' + cast(tblIdas char(1)),Name1,NDesc1,Cast(TotAct1as decimal(19,4)),Name2,NDesc2,Cast(TotAct2as decimal(19,4)),Name3,NDesc3,Cast(TotAct3as decimal(19,4)), Name4,NDesc4,Cast(TotAct4as decimal(19,4)),Name5,NDesc5,Cast(TotAct5as decimal(19,4)),Name6,NDesc6,Cast(TotAct6as decimal(19,4)),Name7,NDesc7,Cast(TotAct7as decimal(19,4)),Name8,NDesc8,Cast(TotAct8as decimal(19,4)),Name9,NDesc9,Cast(TotAct9as decimal(19,4)),Name10,NDesc10,Cast(TotAct10as decimal(19,4)),Name11,NDesc11,Cast(TotAct11as decimal(19,4)),Name12,NDesc12,Cast(TotAct12as decimal(19,4)),Name13,NDesc13,Cast(TotAct13as decimal(19,4)),Name14,NDesc14,Cast(TotAct14as decimal(19,4)),Name15,NDesc15,Cast(TotAct15as decimal(19,4)),Name16,NDesc16,Cast(TotAct16as decimal(19,4)),Name17,NDesc17,Cast(TotAct17as decimal(19,4)),Name18,NDesc18,Cast(TotAct18as decimal(19,4)),Name19,NDesc19,Cast(TotAct19as decimal(19,4)),Name20,NDesc20,Cast(TotAct20as decimal(19,4)) FROM @.tbl
Any help will be appreciated.
Regards
Karen
You can either limit rows in the WHERE clause by specifying the NOT NULL option or you can cast at the front end...
|||
huh?
Please post the minimum amount of code necessary to make your question clear. We don't want to read a book! :)
Your queries are bringing back values from many different columns in one resulting row.
Do you want to skip an entire row because one of the columns has a null or blank value? Or something else? I really don't understand.
|||
I want to skip an entire column because everything is NULL
|||
Never ask a query you don't want the answer to! :)
The short answer is no. If you query a value as a distinct column in the result set, you get that column in the result set.
The longer answer is maybe. If you don't mind concatenating multiple values together into one column in the result set, you can effectively skip the column. Just be sure to use isnull(columnname,'') to replace null values with an empty string, otherwise the whole string will end up null. That's a useful technique for writing lines of text that need to be printed out (as in queries that write sql code), but useless if you need the other column values back as discrete values.
|||
Thanks for your answer... can u tell a good place where i paste a screen shot of my report and show it to others..
Regards
Karen
|||
Karenros:
Thanks for your answer... can u tell a good place where i paste a screen shot of my report and show it to others..
Sorry, no. I'm sure you can get a free website with yahoo or geocities.
|||
David and Dinakar
Thanks for your answers..
Suppose if i have declared a table in my sproc.. like
Declare @.tbl table
(
Column1 - Column N
)
Is it possible to add columns dynamically to it.. like for eg..
my select statement has around 10 columns and in that 4 columns are returning no value... so can i adjust the number of columns in that table.. based on the select statement results...
Regards
Karen
|||
Karenros:
Declare @.tbl table
(
Column1 - Column N
)
I don't understand the syntax you are using in your example.
You could do this as a two step process.
Issue the query with all the columns.
Construct a query statement in a string that only includes the columns that got results and exec that query string.
Honestly, why not just set the Visible or Hidden property of the UI component to false for those columns that don't have values. I think that would be a lot simpler. :)
|||
The reason i am not doing it in the UI compenent,, cause in SSRS 2005 if i hide a column the width of the table would shrink.
Declare @.Tbl Table
(
tbld int identity,
col 1,
Col2,
.
.
Col N
)
|||
To add columns you need to ALTER TABLE. I dont think you can ALTER a table variable. you might either need to use an actual table or a temporary table... I havent looked at your entire post.. I've been quite busy last week and will be so next week too.. am just posting based on your most recent post...
