Showing posts with label null. Show all posts
Showing posts with label null. Show all posts

Monday, March 26, 2012

How create XMLSXHEMA with FOR XML EXPLICIT

Hi,
i have this query:
SELECT 1 as Tag, NULL as Parent,
nomecampo as [label!1!for],
etichetta as [label!1!],
null as [input!2!id],
null as [input!2!type],
null as [input!2!name],
null as [input!2!value]
FROM t_campiaree
UNION ALL
select 2 as tag,
0 as parent,
null as [label!2!for],
null as [label!2!],
id as [input!2!id],
tipointerfaccia as [input!2!type],
nomecampo as [input!2!name],
(seleas [input!2!value]
from t_campiaree
FOR XML EXPLICIT
and the results is this:
<label for=3D"nome">Nome</label>
<label for=3D"cognome">Cognome</label>
<label for=3D"eta">Et=E0</label>
<input id=3D"1" type=3D"input" name=3D"nome" value=3D"" />
<input id=3D"2" type=3D"input" name=3D"cognome" value=3D"" />
<input id=3D"3" type=3D"input" name=3D"eta" value=3D"" />
now, how i can create schema for this xml file?
the XMLSCHEMA option don't work with EXPLICIT mode right?
Since you already know the structure that you want, best is to create your
XML Schema a priori using for example the VisualStudio schema designer. You
can even use an example document to infer much of the schema structure...
Best regards
Michael
"maurox" <mrago@.inwind.it> wrote in message
news:1141917666.744839.229840@.j33g2000cwa.googlegr oups.com...
Hi,
i have this query:
SELECT 1 as Tag, NULL as Parent,
nomecampo as [label!1!for],
etichetta as [label!1!],
null as [input!2!id],
null as [input!2!type],
null as [input!2!name],
null as [input!2!value]
FROM t_campiaree
UNION ALL
select 2 as tag,
0 as parent,
null as [label!2!for],
null as [label!2!],
id as [input!2!id],
tipointerfaccia as [input!2!type],
nomecampo as [input!2!name],
(seleas [input!2!value]
from t_campiaree
FOR XML EXPLICIT
and the results is this:
<label for="nome">Nome</label>
<label for="cognome">Cognome</label>
<label for="eta">Et</label>
<input id="1" type="input" name="nome" value="" />
<input id="2" type="input" name="cognome" value="" />
<input id="3" type="input" name="eta" value="" />
now, how i can create schema for this xml file?
the XMLSCHEMA option don't work with EXPLICIT mode right?
sql

How create XMLSXHEMA with FOR XML EXPLICIT

Hi,
i have this query:
SELECT 1 as Tag, NULL as Parent,
nomecampo as [label!1!for],
etichetta as [label!1!],
null as [input!2!id],
null as [input!2!type],
null as [input!2!name],
null as [input!2!value]
FROM t_campiaree
UNION ALL
select 2 as tag,
0 as parent,
null as [label!2!for],
null as [label!2!],
id as [input!2!id],
tipointerfaccia as [input!2!type],
nomecampo as [input!2!name],
(seleas [input!2!value]
from t_campiaree
FOR XML EXPLICIT
and the results is this:
<label for=3D"nome">Nome</label>
<label for=3D"cognome">Cognome</label>
<label for=3D"eta">Et=E0</label>
<input id=3D"1" type=3D"input" name=3D"nome" value=3D"" />
<input id=3D"2" type=3D"input" name=3D"cognome" value=3D"" />
<input id=3D"3" type=3D"input" name=3D"eta" value=3D"" />
now, how i can create schema for this xml file?
the XMLSCHEMA option don't work with EXPLICIT mode right?Since you already know the structure that you want, best is to create your
XML Schema a priori using for example the VisualStudio schema designer. You
can even use an example document to infer much of the schema structure...
Best regards
Michael
"maurox" <mrago@.inwind.it> wrote in message
news:1141917666.744839.229840@.j33g2000cwa.googlegroups.com...
Hi,
i have this query:
SELECT 1 as Tag, NULL as Parent,
nomecampo as [label!1!for],
etichetta as [label!1!],
null as [input!2!id],
null as [input!2!type],
null as [input!2!name],
null as [input!2!value]
FROM t_campiaree
UNION ALL
select 2 as tag,
0 as parent,
null as [label!2!for],
null as [label!2!],
id as [input!2!id],
tipointerfaccia as [input!2!type],
nomecampo as [input!2!name],
(seleas [input!2!value]
from t_campiaree
FOR XML EXPLICIT
and the results is this:
<label for="nome">Nome</label>
<label for="cognome">Cognome</label>
<label for="eta">Et</label>
<input id="1" type="input" name="nome" value="" />
<input id="2" type="input" name="cognome" value="" />
<input id="3" type="input" name="eta" value="" />
now, how i can create schema for this xml file?
the XMLSCHEMA option don't work with EXPLICIT mode right?

Friday, March 23, 2012

How could I assign rank.

I have the below query

select [FI NAME], round (sum(FIWORKING.GROSPFT), 0)as FIGROSS from from FIWORKING
where FIWORKING.[FI NAME] IS NOT NULL
group by FIWORKING.[FI NAME]
order by sum (FIWORKING."GROSPFT") DESC

Which returns below

name1 65784
name2 32586
name3 37892

based on this, I would like to be able to set a rank (aka 1 , 2 , 3, etc)
for NAME1, NAME2, etc

I would like to store this in table GROSPFTRANK

That Table looks like

name1 1
name2 2

I haven't been able to figure out the SQL to do this.

Thanks for an help

ChrisYou can create a table with an identity column. Insert into that table using your query and the ranking will automatically be applied.|||There are several ways to skin this cat.

One method is to make the second field in your destination table an incrementing identity column, and then just insert your ordered data into it.

A second method would be to create a temporary table with an autoincrement column and load your data into it prior to storing it in your permanent table.

Or you could use this sql statement, which runs two totals and then counts the number of records in the second set which are less than the value in the first set:

Insert into GROSPFTRANK ([FI NAME], [RANK])
select [FIWORKINGOUTER].[FI NAME], count([FIGROSS].[FI NAME])
from
(select [FI NAME], round(sum(FIWORKING.GROSPFT), 0)as FIGROSS
from FIWORKING
where FIWORKING.[FI NAME] IS NOT NULL
group by FIWORKING.[FI NAME]) FIWORKINGOUTER
inner join
(select [FI NAME], round(sum(FIWORKING.GROSPFT), 0)as FIGROSS
from FIWORKING
where FIWORKING.[FI NAME] IS NOT NULL
group by FIWORKING.[FI NAME]) FIWORKINGSUB
on (FIWORKINGOUTER.FIGROSS < FIWORKINGSUB.FIGROSS)

Note that this assigns two [FI NAME] values the same rank if they have the same summary value. If you want unique ranks based on, say, the alphabetical order of [FI NAME], join the two subqueries with this ON statement:

on (FIWORKINGOUTER.FIGROSS < FIWORKINGSUB.FIGROSS)
or (FIWORKINGOUTER.FIGROSS = FIWORKINGSUB.FIGROSS
and [FIWORKINGOUTER].[FI NAME] < [FIWORKINGSUB].[FI NAME])

blindman

Monday, March 19, 2012

How can wrap a case statement around us

I would like to maybe wrap a case statement around the following so that I
can set any results that come back as NULL to 0 (the performance of updating
afterwards is horrible)
update STAGE_PHX_FACT_POLICY
set STAGE_PHX_FACT_POLICY.fire_fee_if = whdata1.dbo.premium_detail.fee,
STAGE_PHX_FACT_POLICY.fire_fee_written =
whdata1.dbo.premium_detail.billed_premium
from whdata1.dbo.premium_detail
inner join whdata1.dbo.lob_xref
on whdata1.dbo.premium_detail.premium_lob = whdata1.dbo.lob_xref.premium_lob
where whdata1.dbo.lob_xref.a_lob = '6'
and whdata1.dbo.premium_detail.policy_number =
STAGE_PHX_FACT_POLICY.policy_number
and whdata1.dbo.premium_detail.policy_date_time =
STAGE_PHX_FACT_POLICY.policy_date_time
Thanks you!>> ...around the following so that I can set any results that come back as
Lookup COALESCE or ISNULL function in SQL Server Books Online
Anith|||"Patrice" <Patrice@.discussions.microsoft.com> wrote in message
news:3B52A763-5C4B-4030-ACDF-7661406D5149@.microsoft.com...
>I would like to maybe wrap a case statement around the following so that I
> can set any results that come back as NULL to 0 (the performance of
> updating
> afterwards is horrible)
>
> update STAGE_PHX_FACT_POLICY
> set STAGE_PHX_FACT_POLICY.fire_fee_if = whdata1.dbo.premium_detail.fee,
> STAGE_PHX_FACT_POLICY.fire_fee_written =
> whdata1.dbo.premium_detail.billed_premium
> from whdata1.dbo.premium_detail
> inner join whdata1.dbo.lob_xref
> on whdata1.dbo.premium_detail.premium_lob =
> whdata1.dbo.lob_xref.premium_lob
> where whdata1.dbo.lob_xref.a_lob = '6'
> and whdata1.dbo.premium_detail.policy_number =
> STAGE_PHX_FACT_POLICY.policy_number
> and whdata1.dbo.premium_detail.policy_date_time =
> STAGE_PHX_FACT_POLICY.policy_date_time
You can use COALESCE or ISNULL to do this in a much simpler way. As you
didn't specify what needed updating, I'll take a guess that it's both
values:
update STAGE_PHX_FACT_POLICY
set STAGE_PHX_FACT_POLICY.fire_fee_if =
COALESCE(whdata1.dbo.premium_detail.fee,0) ,
STAGE_PHX_FACT_POLICY.fire_fee_written =
COALESCE(whdata1.dbo.premium_detail.billed_premium,0)
from whdata1.dbo.premium_detail
inner join whdata1.dbo.lob_xref
on whdata1.dbo.premium_detail.premium_lob = whdata1.dbo.lob_xref.premium_lob
where whdata1.dbo.lob_xref.a_lob = '6'
and whdata1.dbo.premium_detail.policy_number =
STAGE_PHX_FACT_POLICY.policy_number
and whdata1.dbo.premium_detail.policy_date_time =
STAGE_PHX_FACT_POLICY.policy_date_time
Dan

Monday, March 12, 2012

How can we replace any null cells with a particular value instead?

Hi, all experts here,

Thank you very much for your kind attention.

I am wondering if there is any way to replace all null cells when browsing dimensions dynamically? As when we browse cube data with whatever dimensions hierarchies etc. on rows and columns we always get some null cells which have not intersections between those selected dimension hierarchies on columns and rows at all as we never be able to know where null cells are. Therefore in order to make things better (looking better) like in client tool ProClarity, I want to find a way to replace those null cells with a particular value to make the reports look better?

Is it possible to achieve this in SQL Server 2005 Analysis Services and which can be inherited in its clients as well (e.g. ProClarity)? I am looking forward to hearing from you shortly and thanks again for your help.

With best regards,

Yours sincerely,

Hello Helen! In ProClarity you have two buttons in the dimension tool that will remove empty rows and columns in a table.

Be aware that the NON EMPTY buttons will only remove rows and columns where all the cells a null.

If you enter other values, with the MDX IIF-statement in a calculated member, or format-options(in the Calculations tab in the cube editor) you can hurt query performance.

HTH

Thomas Ivarsson

|||

Hi, Thomas,

Thank you very much for that.

The question to me is, some null cells on some rows where not the whole rows are empty. E.g some cells of those rows are with values, some are not. Therefore it does not look nice. So I want to replace them. But then as you kindly suggested replacing them with other values you have to add calculated members which to some extent hurt performance query. Therefore I think it is better to leave them as they are to the end users.

Thank you very much again for your kind help.

With best regards,

Yours sincerely,

Friday, March 9, 2012

How can remove a row from my report if the results is empty or a n

How can remove a row from my report if the results is empty or a null?
If i have a null value , then i get a white space row in report - this is
not looking good !
Can somebody help me, please !?
Thank you!victoras wrote:
> How can remove a row from my report if the results is empty or a null?
> If i have a null value , then i get a white space row in report - this is
> not looking good !
> Can somebody help me, please !?
You need to write your SQL query to exclude that row, something like:
SELECT col1, col2 ... FROM mytable WHERE col1 IS NOT NULL
Luke|||or just use an iif statement on the row which toggles the visibility.
click the row, go to properties and visibility. choose expression
type
=iif(fields!afield.value is system.dbnull.value or fields!afield.value = "",
true, false)
basically says if your field is null or an empty string then do not show
else do show. True means don't show and false means do show.
"Luke Plant" <luke@.mailinator.com> wrote in message
news:do8hgo$9vq$1$8302bc10@.news.demon.co.uk...
> victoras wrote:
>> How can remove a row from my report if the results is empty or a null?
>> If i have a null value , then i get a white space row in report - this is
>> not looking good !
>> Can somebody help me, please !?
> You need to write your SQL query to exclude that row, something like:
> SELECT col1, col2 ... FROM mytable WHERE col1 IS NOT NULL
> Luke

how can NULL be equal to todays date (was "Weird Query behavior plz help")

I have a query that is behaving a little a weird. here is the example:

i have 1 table in this table i have 2 columns wich are date and time

DATE_DEBUT_PERIODE_FISCALE DATE_FIN_PERIODE_FISCALE
---------- --------
1/27/1997 2/27/1997
1/1/2005 2/6/2005

here is my query:

BEGIN
declare @.datefin_flag datetime, @.strip datetime
SELECT @.strip = dateadd(d,datediff(d,0,getdate()),0)
SELECT @.datefin_flag = DATE_FIN_PERIODE_FISCALE FROM DM_LKP_CALENDRIER_PERIODE_F
WHERE DATE_DEBUT_PERIODE_FISCALE < @.strip AND DATE_FIN_PERIODE_FISCALE = @.strip
--select @.datefin_flag
--select @.strip
IF(@.datefin_flag != @.strip)
RAISERROR('You cant run this',16,1)
END

Well this Query should return the raiserror it returns completes successfuly
since todays date is not the same as the date in the database.
if you select @.datefin_flag it returns NULL and if you select @.strip it brings back todays date how can NULL be equal to to todays date assuming that todays date is equal to NULL. ?Your code is checking for NOT equal. NULL is not equal to anything (including another NULL).

-PatP|||Crap...didnt think of that how would i go about fixing this based on my expresion since if it looks in the table and doesnt find anything.|||How about:IF(@.datefin_flag != @.strip OR @.datein_flag IS NULL)
RAISERROR('You cant run this',16,1)
END-PatP|||I'd write it this way...

IF NOT EXISTS( SELECT *
FROM DM_LKP_CALENDRIER_PERIODE_F
WHERE DATE_DEBUT_PERIODE_FISCALE < dateadd(d,datediff(d,0,getdate()),0)
AND DATE_FIN_PERIODE_FISCALE = dateadd(d,datediff(d,0,getdate()),0)
)
RAISERROR('You cant run this',16,1)
END

But I'd handle the raise a little differently

http://weblogs.sqlteam.com/brettk/archive/2004/05/25/1378.aspx|||Thanks alot Pat worked fine i realy apreciated.|||thanks also Brett that is a realy good alternative|||Your welcome...

But it's not really an alternative...

Wednesday, March 7, 2012

How can i write a sproc for dynamic columns

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...