rename columns in a temp table

hello
I have a stored procedure that calculates the values of column data based
on an input variable. I would like to rename the columns to the input
variables so that i can directly graph the results of the table. Is this
possible?
for example, a column in the table #summary is called year1 but i need it to
be renamed to '@.inputYear - 5'. So if @.inputYear = 2000, i need the column
to be renamed to 1995.
thanks for any and all help!
benBen wrote:
> hello
> I have a stored procedure that calculates the values of column data
> based on an input variable. I would like to rename the columns to
> the input variables so that i can directly graph the results of the
> table. Is this possible?
> for example, a column in the table #summary is called year1 but i
> need it to be renamed to '@.inputYear - 5'. So if @.inputYear = 2000,
> i need the column to be renamed to 1995.
> thanks for any and all help!
> ben
You can use an alias for the column, but will have to use dynamic sql.
Or you can return two result sets. The first is the name of the columns,
the second is the data to graph.
EXEC ('Select year1 as "' + CAST(@.inputYear - 5 as VARCHAR(4)) + '"
From...')
David Gugick
Quest Software
www.imceda.com
www.quest.com

Removing Nulls

Hello Everyone, I have a View that is returning null values. This is not a problem except that I don't want the user to see "NULL" in the data. I want to replace "NULL" with a blank. The sql that I have is shown below:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER VIEW dbo.CUSVW_SCHEMATIC_DETAILS_VENDOR
AS
SELECT [DIVISION], [DIVISION NAME], [STOCKING SECTION #], [STOCKING SECTION NAME],
[FIXTURE], [SIZE], [STORE #], [AISLE NUMBER], [AISLE SIDE],
[LEFT STOCKING SECTION #], [LEFT STOCKING SECTION NAME],
[LEFT STOCKING SECTION SIZE], [RIGHT STOCKING SECTION #],
[RIGHT STOCKING SECTION NAME], [RIGHT STOCKING SECTION SIZE]
FROM [AVTP].[dbo].[CUSVW_SCHEMATIC_DETAILS]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

How can I get the nulls to be blanks instead of "NULL"??

Thanks!

Quote:

Originally Posted by jschmidt

Hello Everyone, I have a View that is returning null values. This is not a problem except that I don't want the user to see "NULL" in the data. I want to replace "NULL" with a blank. The sql that I have is shown below:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER VIEW dbo.CUSVW_SCHEMATIC_DETAILS_VENDOR
AS
SELECT [DIVISION], [DIVISION NAME], [STOCKING SECTION #], [STOCKING SECTION NAME],
[FIXTURE], [SIZE], [STORE #], [AISLE NUMBER], [AISLE SIDE],
[LEFT STOCKING SECTION #], [LEFT STOCKING SECTION NAME],
[LEFT STOCKING SECTION SIZE], [RIGHT STOCKING SECTION #],
[RIGHT STOCKING SECTION NAME], [RIGHT STOCKING SECTION SIZE]
FROM [AVTP].[dbo].[CUSVW_SCHEMATIC_DETAILS]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

How can I get the nulls to be blanks instead of "NULL"??

Thanks!

try:

SELECT isnull(DIVISION,' ') as division...and so on...|||Try using case statements

case when division is null then '' esle division end

sort of thins, this need to be in the select statement

Removing null values from a result set

Hi,

I have following query which is returing null values along with other values. Could you please tell me how I can restrict to not to return null values

SELECT [Measures].[Total Hours] ON 0,
[Employee].[Hierarchy].[Employee Key]ON 1
FROM[Labor Metrics]

Thanks,

Prudhvi

Prud,

I'm assuming that you want to filter out TotalHours

SELECT [Measures].[Total Hours] ON 0,
[Employee].[Hierarchy].[Employee Key]ON 1
FROM[Labor Metrics]

Where [Measures].[Total Hours] is not null

Ham

|||

Thanks Ham. I did tried this earlier but got the following error message

Executing the query ...
Query (5, 1) The IS function expects a member expression for the 2 argument. A string or numeric expression was used.
Execution complete

|||

Prud,

Are you using SQL 2000 or SQL 2005? The "is not null" is a valid for SQL in the Where clause.

Ham

|||

SSAS 2005 introduces a HAVING clause:

SELECT [Measures].[Total Hours] ON 0,
[Employee].[Hierarchy].[Employee Key]

HAVING [Measures].[Total Hours] <> 0 ON 1
FROM[Labor Metrics]

Or, you can use Filter().

|||

Thanks Teo, it worked.

removing embedded duplicate rows

I have a table that contains a field with data like:
dog 1
dog 2
cat 1
cat 2
I only what to return one of the values. DISTINCT doesn't do it. Any
suggestions?
Thanks,
BobWhich value do you want? Distinct rolls up multiple values that are the
same. Your four values are all different, and thus cannot be rolled up.
Describe what you expect to see from the sample data you quoted.
More info, please.|||Assuming table below with 2 cols name & id
name id
-- --
dog 1
dog 2
cat 1
cat 2
-- following will get u the 1st of every grp with same name
select name, id
from t
where id in (select min(id) from t group by name)
Rakesh
"bday55" wrote:

> I have a table that contains a field with data like:
> dog 1
> dog 2
> cat 1
> cat 2
> I only what to return one of the values. DISTINCT doesn't do it. Any
> suggestions?
> Thanks,
> Bob
>|||On Wed, 10 Aug 2005 22:51:01 -0700, Rakesh wrote:

>Assuming table below with 2 cols name & id
>name id
>-- --
>dog 1
>dog 2
>cat 1
>cat 2
>-- following will get u the 1st of every grp with same name
>select name, id
>from t
>where id in (select min(id) from t group by name)
Hi Rakesh,
But that won't work if the rows are changed to

>name id
>-- --
>dog 1
>dog 2
>cat 2
>cat 3
Here's a better one:
SELECT name, id
FROM t AS t1
WHERE id = (SELECT MIN(id) FROM t AS t2 WHERE t1.name = t2.name)
Or, even simpler:
SELECT name, MIN(id)
FROM t
GROUP BY t
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Removing Duplicate Values in Filter List...

Hello!
I am pulling two values from a field when the report is first run. The
value can either be ACTIVE or DISABLED. This is being accessed in a
parameter and I would like to be able to filter my current report by
selecting either ACTIVE or DISABLED records. The catch is that I need to do
this without taking a trip back to the server.
I could hardcode the two values in there, however that would require the
report to re-render each time the filter is applied. Can I have two distinct
values in my filter and have this applied at the time of the first rendering?
Right now I get all values for my status_cd column which is a bunch of
DISABLED and ACTIVE values. I just need two. Let me know what you think.
Thanks!Just a short clarification. After I wrote all that I realized that I needed
to explain a little more...
We are pulling in the values for the filter from a dataset. The
Active/Disabled example isn't all that great because I can hardcode the
values for that to get it to work. However, the functionality we really need
is as follows...
We have users that are associated with clients. So multiple users have the
same client. In this filter drop-down when it populates from the dataset it
is populating the same client multiple times based upon how many users are
associated with that client. All we need is each client a single time. We
can do this by creating another dataset, but we would like to do it with the
current dataset instead of making a trip back to the server. Your help is
appreciated. Thanks!
"jrennard" wrote:
> Hello!
> I am pulling two values from a field when the report is first run. The
> value can either be ACTIVE or DISABLED. This is being accessed in a
> parameter and I would like to be able to filter my current report by
> selecting either ACTIVE or DISABLED records. The catch is that I need to do
> this without taking a trip back to the server.
> I could hardcode the two values in there, however that would require the
> report to re-render each time the filter is applied. Can I have two distinct
> values in my filter and have this applied at the time of the first rendering?
> Right now I get all values for my status_cd column which is a bunch of
> DISABLED and ACTIVE values. I just need two. Let me know what you think.
> Thanks!

Removing decimals from a nvarchar field

I am trying to remove decimals...for example I have values like this:
01.234.678 and I want to update it to 01234678 or 12345678.
Thanks
SELECT REPLACE(column, '.', '') FROM table;
"Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
news:B084D109-B3E8-4D0D-B0BC-CC7D836ACA8D@.microsoft.com...
>I am trying to remove decimals...for example I have values like this:
> 01.234.678 and I want to update it to 01234678 or 12345678.
> Thanks

Removing decimals from a nvarchar field

I am trying to remove decimals...for example I have values like this:
01.234.678 and I want to update it to 01234678 or 12345678.
ThanksSELECT REPLACE(column, '.', '') FROM table;
"Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
news:B084D109-B3E8-4D0D-B0BC-CC7D836ACA8D@.microsoft.com...
>I am trying to remove decimals...for example I have values like this:
> 01.234.678 and I want to update it to 01234678 or 12345678.
> Thanks

Removing all values from an XML Document

Is there an easy way in SQL Server to take an XML Document and remove all
its values while leaving the structure, elements and attributes in place?
I have included a sample of what I would like below
For example take:
'<root>
<foo name="Joe"/>
<heading name="One">
<contents>some text</contents>
</heading>
</root>'
and return:
'<root>
<foo name=""/>
<heading name="">
<contents></contents>
</heading>
</root>'
--This is a hack of a sorts, but it seems to work...
CREATE FUNCTION fnClearXmlValues (@.x xml)
RETURNS varchar(max)
AS
BEGIN
DECLARE @.t varchar(max)
SET @.t = ''
SELECT @.t = @.t + '<' + element + ' ' + attributes
+ CASE WHEN ChildCount > 0
THEN '>' + IsNull(dbo.fnClearXmlValues(Children), '')
+'</' + element + '>'
ELSE '/>'
END
FROM (SELECT
T.C.value('count(./*)', 'int') AS ChildCount,
T.C.query('./*') AS Children,
T.C.value('for $s in . return local-name($s)',
'varchar(255)') AS element,
CAST(T.C.query('for $s in ./@.* return
concat(local-name($s), "=""""")') as varchar(1000)) as attributes
FROM @.x.nodes('*') AS T(C)) AS x
RETURN @.t
END
--To use:
declare @.x xml
SET @.x = '<root>
<foo name="Joe" id="1"/>
<heading name="One">
<contents>some text</contents>
<otherContent> x
<test>y</test>
</otherContent>
</heading>
</root>'
SELECT CAST(dbo.fnClearXmlValues(@.x) AS xml)
Peter DeBetta, MVP - SQL Server
http://sqlblog.com
"Mark" <marksullivan@.rcn.com> wrote in message
news:OiIBTU1KHHA.4384@.TK2MSFTNGP03.phx.gbl...
> Is there an easy way in SQL Server to take an XML Document and remove all
> its values while leaving the structure, elements and attributes in place?
> I have included a sample of what I would like below
> For example take:
> '<root>
> <foo name="Joe"/>
> <heading name="One">
> <contents>some text</contents>
> </heading>
> </root>'
> and return:
> '<root>
> <foo name=""/>
> <heading name="">
> <contents></contents>
> </heading>
> </root>'
>
>
|||Hello
You can remove every text-nodes with modify(),
set @.x.modify('delete /descendant::text()')
However you can't apply the same xpath to the value of attributes. So some
loop may be needed.
while (@.x.exist('/descendant::*[@.* != ""]')=1)
BEGIN
set @.x.modify('
replace value of (//@.*[. != ""])[1] with ""
')
END
Alternatively, if the data is huge or you want neat solution, you may want
XSL via CLR. It's almost crime use SQL2005 and not use CLR. ^^;
"Mark" <marksullivan@.rcn.com> wrote in message
news:OiIBTU1KHHA.4384@.TK2MSFTNGP03.phx.gbl...
> Is there an easy way in SQL Server to take an XML Document and remove all
> its values while leaving the structure, elements and attributes in place?
> I have included a sample of what I would like below
> For example take:
> '<root>
> <foo name="Joe"/>
> <heading name="One">
> <contents>some text</contents>
> </heading>
> </root>'
> and return:
> '<root>
> <foo name=""/>
> <heading name="">
> <contents></contents>
> </heading>
> </root>'
>
>
|||One caveat to Han's solution is that @.x needs to be unconstrained by a
schema collection. Otherwise text() on a simple typed content element will
raise a type error.
Best regards
Michael
"Han" <hp4444@.kornet.net.korea> wrote in message
news:uIvMp49KHHA.4000@.TK2MSFTNGP06.phx.gbl...
> Hello
> You can remove every text-nodes with modify(),
> set @.x.modify('delete /descendant::text()')
> However you can't apply the same xpath to the value of attributes. So some
> loop may be needed.
> while (@.x.exist('/descendant::*[@.* != ""]')=1)
> BEGIN
> set @.x.modify('
> replace value of (//@.*[. != ""])[1] with ""
> ')
> END
> Alternatively, if the data is huge or you want neat solution, you may want
> XSL via CLR. It's almost crime use SQL2005 and not use CLR. ^^;
> "Mark" <marksullivan@.rcn.com> wrote in message
> news:OiIBTU1KHHA.4384@.TK2MSFTNGP03.phx.gbl...
>

Removing all values from an XML Document

Is there an easy way in SQL Server to take an XML Document and remove all
its values while leaving the structure, elements and attributes in place?
I have included a sample of what I would like below
For example take:
'<root>
<foo name="Joe"/>
<heading name="One">
<contents>some text</contents>
</heading>
</root>'
and return:
'<root>
<foo name=""/>
<heading name="">
<contents></contents>
</heading>
</root>'--This is a hack of a sorts, but it seems to work...
CREATE FUNCTION fnClearXmlValues (@.x xml)
RETURNS varchar(max)
AS
BEGIN
DECLARE @.t varchar(max)
SET @.t = ''
SELECT @.t = @.t + '<' + element + ' ' + attributes
+ CASE WHEN ChildCount > 0
THEN '>' + IsNull(dbo.fnClearXmlValues(Children), '')
+'</' + element + '>'
ELSE '/>'
END
FROM (SELECT
T.C.value('count(./*)', 'int') AS ChildCount,
T.C.query('./*') AS Children,
T.C.value('for $s in . return local-name($s)',
'varchar(255)') AS element,
CAST(T.C.query('for $s in ./@.* return
concat(local-name($s), "=""""")') as varchar(1000)) as attributes
FROM @.x.nodes('*') AS T(C)) AS x
RETURN @.t
END
--To use:
declare @.x xml
SET @.x = '<root>
<foo name="Joe" id="1"/>
<heading name="One">
<contents>some text</contents>
<otherContent> x
<test>y</test>
</otherContent>
</heading>
</root>'
SELECT CAST(dbo.fnClearXmlValues(@.x) AS xml)
Peter DeBetta, MVP - SQL Server
http://sqlblog.com
--
"Mark" <marksullivan@.rcn.com> wrote in message
news:OiIBTU1KHHA.4384@.TK2MSFTNGP03.phx.gbl...
> Is there an easy way in SQL Server to take an XML Document and remove all
> its values while leaving the structure, elements and attributes in place?
> I have included a sample of what I would like below
> For example take:
> '<root>
> <foo name="Joe"/>
> <heading name="One">
> <contents>some text</contents>
> </heading>
> </root>'
> and return:
> '<root>
> <foo name=""/>
> <heading name="">
> <contents></contents>
> </heading>
> </root>'
>
>|||Hello
You can remove every text-nodes with modify(),
set @.x.modify('delete /descendant::text()')
However you can't apply the same xpath to the value of attributes. So some
loop may be needed.
while (@.x.exist('/descendant::*[@.* != ""]')=1)
BEGIN
set @.x.modify('
replace value of (//@.*[. != ""])[1] with ""
')
END
Alternatively, if the data is huge or you want neat solution, you may want
XSL via CLR. It's almost crime use SQL2005 and not use CLR. ^^;
"Mark" <marksullivan@.rcn.com> wrote in message
news:OiIBTU1KHHA.4384@.TK2MSFTNGP03.phx.gbl...
> Is there an easy way in SQL Server to take an XML Document and remove all
> its values while leaving the structure, elements and attributes in place?
> I have included a sample of what I would like below
> For example take:
> '<root>
> <foo name="Joe"/>
> <heading name="One">
> <contents>some text</contents>
> </heading>
> </root>'
> and return:
> '<root>
> <foo name=""/>
> <heading name="">
> <contents></contents>
> </heading>
> </root>'
>
>|||One caveat to Han's solution is that @.x needs to be unconstrained by a
schema collection. Otherwise text() on a simple typed content element will
raise a type error.
Best regards
Michael
"Han" <hp4444@.kornet.net.korea> wrote in message
news:uIvMp49KHHA.4000@.TK2MSFTNGP06.phx.gbl...
> Hello
> You can remove every text-nodes with modify(),
> set @.x.modify('delete /descendant::text()')
> However you can't apply the same xpath to the value of attributes. So some
> loop may be needed.
> while (@.x.exist('/descendant::*[@.* != ""]')=1)
> BEGIN
> set @.x.modify('
> replace value of (//@.*[. != ""])[1] with ""
> ')
> END
> Alternatively, if the data is huge or you want neat solution, you may want
> XSL via CLR. It's almost crime use SQL2005 and not use CLR. ^^;
> "Mark" <marksullivan@.rcn.com> wrote in message
> news:OiIBTU1KHHA.4384@.TK2MSFTNGP03.phx.gbl...
>