database collation: select

Showing posts with label select. Show all posts

Tuesday, March 27, 2012

Changing the owner of a table

Hi,
I have a table in SQL Server 2000, say its called: "tblName". When
I right mouse click on it and select "Properties" from the menu I see
that beside the "Owner" label is companyName\Administrator.
I want to change this to sa as I'm having problems creating an
index (I think its got to do with this). So my question is: how do you
change the owner of a table please?
Any comments/suggestions/advice greatly appreciated.
Thank you,
Al.
<almurph@.altavista.com> wrote in message
news:1e70ec60-b12a-49dc-b8d4-f070f15d739b@.j20g2000hsi.googlegroups.com...
> Hi,
> I have a table in SQL Server 2000, say its called: "tblName". When
> I right mouse click on it and select "Properties" from the menu I see
> that beside the "Owner" label is companyName\Administrator.
> I want to change this to sa as I'm having problems creating an
> index (I think its got to do with this). So my question is: how do you
> change the owner of a table please?
> Any comments/suggestions/advice greatly appreciated.
> Thank you,
> Al.
Take a look at the sp_changeobjectowner stored procedure. There are some
caveats to this process however. You may impact other views and stored
procedures that depend on that table having the
companyName\Administrator.Table1 name.
You cannot change the ownership to sa. You can change it to dbo.
As for your index, if you are the sa in the database, you could create your
index by specifying the qualified name of the table.
Example:
CREATE NONCLUSTERED INDEX IX_SomeIndex ON companyName\Administrator.Table1
(column list)
HTH
Rick Sawtell
MCT, MCSD, MCDBA

Changing the owner of a table

Hi,
I have a table in SQL Server 2000, say its called: "tblName". When
I right mouse click on it and select "Properties" from the menu I see
that beside the "Owner" label is companyName\Administrator.
I want to change this to sa as I'm having problems creating an
index (I think its got to do with this). So my question is: how do you
change the owner of a table please?
Any comments/suggestions/advice greatly appreciated.
Thank you,
Al.<almurph@.altavista.com> wrote in message
news:1e70ec60-b12a-49dc-b8d4-f070f15d739b@.j20g2000hsi.googlegroups.com...
> Hi,
> I have a table in SQL Server 2000, say its called: "tblName". When
> I right mouse click on it and select "Properties" from the menu I see
> that beside the "Owner" label is companyName\Administrator.
> I want to change this to sa as I'm having problems creating an
> index (I think its got to do with this). So my question is: how do you
> change the owner of a table please?
> Any comments/suggestions/advice greatly appreciated.
> Thank you,
> Al.
Take a look at the sp_changeobjectowner stored procedure. There are some
caveats to this process however. You may impact other views and stored
procedures that depend on that table having the
companyName\Administrator.Table1 name.
You cannot change the ownership to sa. You can change it to dbo.
As for your index, if you are the sa in the database, you could create your
index by specifying the qualified name of the table.
Example:
CREATE NONCLUSTERED INDEX IX_SomeIndex ON companyName\Administrator.Table1
(column list)
HTH
Rick Sawtell
MCT, MCSD, MCDBA

Changing the owner of a table

Hi,
I have a table in SQL Server 2000, say its called: "tblName". When
I right mouse click on it and select "Properties" from the menu I see
that beside the "Owner" label is companyName\Administrator.
I want to change this to sa as I'm having problems creating an
index (I think its got to do with this). So my question is: how do you
change the owner of a table please?
Any comments/suggestions/advice greatly appreciated.
Thank you,
Al.<almurph@.altavista.com> wrote in message
news:1e70ec60-b12a-49dc-b8d4-f070f15d739b@.j20g2000hsi.googlegroups.com...
> Hi,
> I have a table in SQL Server 2000, say its called: "tblName". When
> I right mouse click on it and select "Properties" from the menu I see
> that beside the "Owner" label is companyName\Administrator.
> I want to change this to sa as I'm having problems creating an
> index (I think its got to do with this). So my question is: how do you
> change the owner of a table please?
> Any comments/suggestions/advice greatly appreciated.
> Thank you,
> Al.
Take a look at the sp_changeobjectowner stored procedure. There are some
caveats to this process however. You may impact other views and stored
procedures that depend on that table having the
companyName\Administrator.Table1 name.
You cannot change the ownership to sa. You can change it to dbo.
As for your index, if you are the sa in the database, you could create your
index by specifying the qualified name of the table.
Example:
CREATE NONCLUSTERED INDEX IX_SomeIndex ON companyName\Administrator.Table1
(column list)
HTH
Rick Sawtell
MCT, MCSD, MCDBA

Sunday, March 25, 2012

Changing the file name stored in database properties

I need to change the file name of a database.
The name I need to change is shown when you right click on a database and
select properties then select the Data File tab.
I need to change the filename associated with the database.
Is this possible and how
Regards
Jeff
--
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.504 / Virus Database: 302 - Release Date: 24/07/2003Jeff,
The filename you mentioned is the "logical filename".Why I clarified is
because, normally the xxxx.mdf is referred as filename of data file.As Kalen
mentioned, you can change the logical filename only in SQL2000 and the
command is:
ALTER DATABASE <databasename>
MODIFY FILE (NAME= '<oldlogicalfilename>', NEWNAME = '<newlogicalfilename>')
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Jeff Williams" <jeff.williams@.hardsoft.com.au> wrote in message
news:eUENlIfXDHA.2484@.TK2MSFTNGP09.phx.gbl...
> I need to change the file name of a database.
> The name I need to change is shown when you right click on a database and
> select properties then select the Data File tab.
> I need to change the filename associated with the database.
> Is this possible and how
> Regards
> Jeff
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.504 / Virus Database: 302 - Release Date: 24/07/2003
>

Thursday, March 22, 2012

Changing the columns of a SELECT statement.

I have been "noramlizing" some of the tables that we have been using and I
ran into a problem in one of our stored procedures.
It used to be (with the old table) that you could do something like:
SELECT A, B FROM OldTable
The stored procedure essentially does this.
Now with the new table(s) it is something like:
SELECT *
FROM Table
LEFT OUTER JOIN Attributes ON Attributes.AttributesId = Table.AttributesId
There is in the Attributes table an AttibutesTypeId and an AttributesValue.
So I get 'A' only when AttributesTypeId = 1 and 'B' when AttributesTypeId =
2. The question is how do I return A and B from the stored procedure given
this new table structure? I am tempted to create a temporary table and use a
cursor to move row by row through the table, but there must be a more
efficient way.
Thank you.Kevin
I need a bit more clarification, if you don't mind
You used to have One table called "Big Daddy" now there are 3 tables I
presume.
Table, Attributes and Attributetype
if that assumption is correct, let us know how the columns(old table) have
been distributed in the normalized structure(have the static columns A and B
been converted to AttributeType) , there is probably a way to do it without
the cursor, but right now it is very hard to understand your problem.
Also can you give me the query again that you are trying to run from the OLD
table.
In the query you have specified , it's confusing when you are say the
Attribute has AttributeTypeId but you join it to Table using AttributesId
column, so seems like there is a 3rd table called AttributesType.
it might be just simpler to post the DDL , you don't mind doing it.
Rakesh Ajwani
MCSD, MCSD.NET
"Kevin Burton" wrote:

> I have been "noramlizing" some of the tables that we have been using and I
> ran into a problem in one of our stored procedures.
> It used to be (with the old table) that you could do something like:
> SELECT A, B FROM OldTable
> The stored procedure essentially does this.
> Now with the new table(s) it is something like:
> SELECT *
> FROM Table
> LEFT OUTER JOIN Attributes ON Attributes.AttributesId = Table.AttributesId
> There is in the Attributes table an AttibutesTypeId and an AttributesValue
.
> So I get 'A' only when AttributesTypeId = 1 and 'B' when AttributesTypeId
=
> 2. The question is how do I return A and B from the stored procedure given
> this new table structure? I am tempted to create a temporary table and use
a
> cursor to move row by row through the table, but there must be a more
> efficient way.
> Thank you.
>|||Find the moron that did this to you and kill him. This is called a EAV
design and it is totally wrong in an RDBMS. It confuses data and meta
data and makes your most basic queries run several orders of magnitude
slower. But the lack of speed does not matter; you will have no data
integrity so you cannot trust the answers anyway.
You might also want to get any book on data modeling. Then you will
know that there cannot be such a creature is a "type_id" -- the data
element is either a type with a known code or it is an identifier of
some kind of entity.
I am on a borrowed computer in Brazil, so I cannot cut & paste the
painful details; you can Google old postings on EAV, OTLT and MUCK as
bad design decisions frequently made by non-SQL programmers.|||Kevin Burton wrote:
> I have been "noramlizing" some of the tables that we have been using and I
> ran into a problem in one of our stored procedures.
> It used to be (with the old table) that you could do something like:
> SELECT A, B FROM OldTable
> The stored procedure essentially does this.
> Now with the new table(s) it is something like:
> SELECT *
> FROM Table
> LEFT OUTER JOIN Attributes ON Attributes.AttributesId = Table.AttributesId
> There is in the Attributes table an AttibutesTypeId and an AttributesValue
.
> So I get 'A' only when AttributesTypeId = 1 and 'B' when AttributesTypeId
=
> 2. The question is how do I return A and B from the stored procedure given
> this new table structure? I am tempted to create a temporary table and use
a
> cursor to move row by row through the table, but there must be a more
> efficient way.
> Thank you.
That is what you call "normalization"?!! Go lookup normalization in a
good book is my advice. This is a design problem of your own making I'm
afraid.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thank you for your reply. In an effort to make things simpler by removing
detail I probably confused things.
The OldTable had columns that looked like:
Id, A, B, C, D
A, B, C, D are types of attributes for the node of Id. We are adding new
types and in the future even more will be added. So rather than keep adding
columns to the OldTable a new table was thought up like:
Id, AttributeId
The AttributeId column is an index to the attributes of Id. It indexes
another new table where the attributes are stored called Attributes. It has
columns like:
AttributeId, Type, Value
So a human will be able to tell what 'Type' is there is a third table
AttributeTypes that has columns like:
Type, Description
So the OldTable would have a row like:
1000, 'A Attribute', 'B Attribute', 'C Attribute', 'D Attribute'
This would translate to the NewTable having content like:
1000, 10
1000, 20
1000, 30
1000, 40
The Attributes table would have content like:
10, 1, 'A Attribute'
20, 2, 'B Attribute'
30, 3, 'C Attribute'
40, 4, 'D Attribute'
The contents of the "lookup" table (AttributeTypes) would look like:
1, 'A'
2, 'B'
3, 'C'
4, 'D'
So to make the query return the same result set as before I am trying:
SELECT Id,
CASE Attributes,Type
WHEN 1 THEN Attributes.Value
ELSE NULL
END AS A,
CASE Attributes,Type
WHEN 2 THEN Attributes.Value
ELSE NULL
END AS B,
CASE Attributes,Type
WHEN 3 THEN Attributes.Value
ELSE NULL
END AS C,
CASE Attributes,Type
WHEN 4 THEN Attributes.Value
ELSE NULL
END AS D
FROM NewTable
INNER JOIN Attributes ON Attributes.AttributeId = NewTable.AttributeId
ORDER BY Id
The problem is that this returns
1000, 'A Attribute', NULL, NULL, NULL
1000, NULL, 'B Attribute', NULL, NULL
1000, NULL, NULL, 'C Attribute',NULL
1000, NULL, NULL, NULL, 'D Attribute'
So I either need to merge the above (preferred) so that it returns just one
row as before:
1000, 'A Attribute', 'B Attribute', 'C Attribute', 'D Attribute'
Or I need to be prepared to add a column any time a new attribute is
speicfied.
Suggestions?
Thank you.
Kevin
"Rakesh Ajwani" wrote:
[vbcol=seagreen]
> Kevin
> I need a bit more clarification, if you don't mind
> You used to have One table called "Big Daddy" now there are 3 tables I
> presume.
> Table, Attributes and Attributetype
> if that assumption is correct, let us know how the columns(old table) have
> been distributed in the normalized structure(have the static columns A and
B
> been converted to AttributeType) , there is probably a way to do it withou
t
> the cursor, but right now it is very hard to understand your problem.
> Also can you give me the query again that you are trying to run from the O
LD
> table.
> In the query you have specified , it's confusing when you are say the
> Attribute has AttributeTypeId but you join it to Table using AttributesId
> column, so seems like there is a 3rd table called AttributesType.
> it might be just simpler to post the DDL , you don't mind doing it.
> --
> Rakesh Ajwani
> MCSD, MCSD.NET
>
> "Kevin Burton" wrote:
>

Changing the columns of a SELECT statement.

I have been "noramlizing" some of the tables that we have been using and I
ran into a problem in one of our stored procedures.
It used to be (with the old table) that you could do something like:
SELECT A, B FROM OldTable
The stored procedure essentially does this.
Now with the new table(s) it is something like:
SELECT *
FROM Table
LEFT OUTER JOIN Attributes ON Attributes.AttributesId = Table.AttributesId
There is in the Attributes table an AttibutesTypeId and an AttributesValue.
So I get 'A' only when AttributesTypeId = 1 and 'B' when AttributesTypeId = 2. The question is how do I return A and B from the stored procedure given
this new table structure? I am tempted to create a temporary table and use a
cursor to move row by row through the table, but there must be a more
efficient way.
Thank you.Kevin
I need a bit more clarification, if you don't mind
You used to have One table called "Big Daddy" now there are 3 tables I
presume.
Table, Attributes and Attributetype
if that assumption is correct, let us know how the columns(old table) have
been distributed in the normalized structure(have the static columns A and B
been converted to AttributeType) , there is probably a way to do it without
the cursor, but right now it is very hard to understand your problem.
Also can you give me the query again that you are trying to run from the OLD
table.
In the query you have specified , it's confusing when you are say the
Attribute has AttributeTypeId but you join it to Table using AttributesId
column, so seems like there is a 3rd table called AttributesType.
it might be just simpler to post the DDL , you don't mind doing it.
--
Rakesh Ajwani
MCSD, MCSD.NET
"Kevin Burton" wrote:
> I have been "noramlizing" some of the tables that we have been using and I
> ran into a problem in one of our stored procedures.
> It used to be (with the old table) that you could do something like:
> SELECT A, B FROM OldTable
> The stored procedure essentially does this.
> Now with the new table(s) it is something like:
> SELECT *
> FROM Table
> LEFT OUTER JOIN Attributes ON Attributes.AttributesId = Table.AttributesId
> There is in the Attributes table an AttibutesTypeId and an AttributesValue.
> So I get 'A' only when AttributesTypeId = 1 and 'B' when AttributesTypeId => 2. The question is how do I return A and B from the stored procedure given
> this new table structure? I am tempted to create a temporary table and use a
> cursor to move row by row through the table, but there must be a more
> efficient way.
> Thank you.
>|||Find the moron that did this to you and kill him. This is called a EAV
design and it is totally wrong in an RDBMS. It confuses data and meta
data and makes your most basic queries run several orders of magnitude
slower. But the lack of speed does not matter; you will have no data
integrity so you cannot trust the answers anyway.
You might also want to get any book on data modeling. Then you will
know that there cannot be such a creature is a "type_id" -- the data
element is either a type with a known code or it is an identifier of
some kind of entity.
I am on a borrowed computer in Brazil, so I cannot cut & paste the
painful details; you can Google old postings on EAV, OTLT and MUCK as
bad design decisions frequently made by non-SQL programmers.|||Kevin Burton wrote:
> I have been "noramlizing" some of the tables that we have been using and I
> ran into a problem in one of our stored procedures.
> It used to be (with the old table) that you could do something like:
> SELECT A, B FROM OldTable
> The stored procedure essentially does this.
> Now with the new table(s) it is something like:
> SELECT *
> FROM Table
> LEFT OUTER JOIN Attributes ON Attributes.AttributesId = Table.AttributesId
> There is in the Attributes table an AttibutesTypeId and an AttributesValue.
> So I get 'A' only when AttributesTypeId = 1 and 'B' when AttributesTypeId => 2. The question is how do I return A and B from the stored procedure given
> this new table structure? I am tempted to create a temporary table and use a
> cursor to move row by row through the table, but there must be a more
> efficient way.
> Thank you.
That is what you call "normalization"?!! Go lookup normalization in a
good book is my advice. This is a design problem of your own making I'm
afraid.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thank you for your reply. In an effort to make things simpler by removing
detail I probably confused things.
The OldTable had columns that looked like:
Id, A, B, C, D
A, B, C, D are types of attributes for the node of Id. We are adding new
types and in the future even more will be added. So rather than keep adding
columns to the OldTable a new table was thought up like:
Id, AttributeId
The AttributeId column is an index to the attributes of Id. It indexes
another new table where the attributes are stored called Attributes. It has
columns like:
AttributeId, Type, Value
So a human will be able to tell what 'Type' is there is a third table
AttributeTypes that has columns like:
Type, Description
So the OldTable would have a row like:
1000, 'A Attribute', 'B Attribute', 'C Attribute', 'D Attribute'
This would translate to the NewTable having content like:
1000, 10
1000, 20
1000, 30
1000, 40
The Attributes table would have content like:
10, 1, 'A Attribute'
20, 2, 'B Attribute'
30, 3, 'C Attribute'
40, 4, 'D Attribute'
The contents of the "lookup" table (AttributeTypes) would look like:
1, 'A'
2, 'B'
3, 'C'
4, 'D'
So to make the query return the same result set as before I am trying:
SELECT Id,
CASE Attributes,Type
WHEN 1 THEN Attributes.Value
ELSE NULL
END AS A,
CASE Attributes,Type
WHEN 2 THEN Attributes.Value
ELSE NULL
END AS B,
CASE Attributes,Type
WHEN 3 THEN Attributes.Value
ELSE NULL
END AS C,
CASE Attributes,Type
WHEN 4 THEN Attributes.Value
ELSE NULL
END AS D
FROM NewTable
INNER JOIN Attributes ON Attributes.AttributeId = NewTable.AttributeId
ORDER BY Id
The problem is that this returns
1000, 'A Attribute', NULL, NULL, NULL
1000, NULL, 'B Attribute', NULL, NULL
1000, NULL, NULL, 'C Attribute',NULL
1000, NULL, NULL, NULL, 'D Attribute'
So I either need to merge the above (preferred) so that it returns just one
row as before:
1000, 'A Attribute', 'B Attribute', 'C Attribute', 'D Attribute'
Or I need to be prepared to add a column any time a new attribute is
speicfied.
Suggestions?
Thank you.
Kevin
"Rakesh Ajwani" wrote:
> Kevin
> I need a bit more clarification, if you don't mind
> You used to have One table called "Big Daddy" now there are 3 tables I
> presume.
> Table, Attributes and Attributetype
> if that assumption is correct, let us know how the columns(old table) have
> been distributed in the normalized structure(have the static columns A and B
> been converted to AttributeType) , there is probably a way to do it without
> the cursor, but right now it is very hard to understand your problem.
> Also can you give me the query again that you are trying to run from the OLD
> table.
> In the query you have specified , it's confusing when you are say the
> Attribute has AttributeTypeId but you join it to Table using AttributesId
> column, so seems like there is a 3rd table called AttributesType.
> it might be just simpler to post the DDL , you don't mind doing it.
> --
> Rakesh Ajwani
> MCSD, MCSD.NET
>
> "Kevin Burton" wrote:
> > I have been "noramlizing" some of the tables that we have been using and I
> > ran into a problem in one of our stored procedures.
> >
> > It used to be (with the old table) that you could do something like:
> >
> > SELECT A, B FROM OldTable
> >
> > The stored procedure essentially does this.
> >
> > Now with the new table(s) it is something like:
> >
> > SELECT *
> > FROM Table
> > LEFT OUTER JOIN Attributes ON Attributes.AttributesId = Table.AttributesId
> >
> > There is in the Attributes table an AttibutesTypeId and an AttributesValue.
> > So I get 'A' only when AttributesTypeId = 1 and 'B' when AttributesTypeId => > 2. The question is how do I return A and B from the stored procedure given
> > this new table structure? I am tempted to create a temporary table and use a
> > cursor to move row by row through the table, but there must be a more
> > efficient way.
> >
> > Thank you.
> >

Tuesday, March 20, 2012

changing SQL syntax

I am using MS SQL Server 2000.
I am using a java component which generates SELECT statements based on
different critieria. The SELECT statements generated are using the old SQL
syntax. For example it will generate :
SELECT e.employee_id, er.location_code
FROM employee e, employee_record er
WHERE e.employee_id = er.employee_id
What I would like is to get this SELECT statement as :
SELECT e.employee_id, er.location_code
FROM employee e INNER JOIN employee_record er ON (e.employee_id =
er.employee_id)
Now in Enterprise Manager if i create a new view and type in the old SQL
syntax, and see the result, it automatically changes it to new SQL syntax. I
would like to call that functionality from my code after I have received the
SELECT statement from the java component. Is it possible?
Thanks
Rizwan>> I would like to call that functionality from my code after I have
I guess no. It might be an internal parsing/tokenizing routine within the EM
interface & it seems unreasonable to expect some external application have
access to such internal routines.
Anith|||1) Why are you generating SQL instead of calling stored procedures?
2) Why do you care what the genrerated coee looks like anyway? They
run the same!

Sunday, March 11, 2012

changing servers dynamically via TSQL

I know you can easily change databases, as in Use "dbname". Can one
also easily change database servers without having to manually select
the server name from the dropdown?
Thanks a million!Not at the TSQL level. TSQL is executed by the database server, so you are already connected to the
database server when your TSQL code is executed. So this would have to be done by the client app.
SQL Server 2005 has a successor to OSQL.EXE named SQLCMD.EXE, and I believe that this tool has some
such functionality.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<google.1.jvmail@.spamgourmet.com> wrote in message
news:1143501877.734933.108360@.i39g2000cwa.googlegroups.com...
>I know you can easily change databases, as in Use "dbname". Can one
> also easily change database servers without having to manually select
> the server name from the dropdown?
> Thanks a million!
>|||no can do with tsql. if you use sql2k5 sqlcmd, you can use :Connect to
switch server connection.
:Connect server_name[\instance_name] [-l timeout] [-U user_name [-P
password]] ,
Connects to an instance of SQL Server. Also closes the current connection.
-oj
<google.1.jvmail@.spamgourmet.com> wrote in message
news:1143501877.734933.108360@.i39g2000cwa.googlegroups.com...
>I know you can easily change databases, as in Use "dbname". Can one
> also easily change database servers without having to manually select
> the server name from the dropdown?
> Thanks a million!
>|||Thanks all for the replies. Not what I wanted to hear, but truth leads
to wisdom, or so they say. :-)
Thanks again.

Wednesday, March 7, 2012

Changing Parameters Dynamically

Hello All,
How do I change paramters dynamically based on user selection. For
example if I let the user select country in a drop down, and based on
the country, I need to load the states parameter, and based on the
states, the counties parameter etc...Is such a dynamic layout possible
using reporting services and VS.NET? If so how? Any links or code
samples would be really helpful.
Thanks,
ChrisYes you can do this. Reference the data set in the report parameter. In the
code snippet below, I have 2 parameters. The first report Parameter is
passed into the query parameter. The resulting data set is then use in the
second parameter and the report filters the data set based on that
parameter. Play with the .rdl a bit and you will get it to work.
<ReportParameters>
<ReportParameter Name="FirstName">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>Brad</Value>
</Values>
</DefaultValue>
<ValidValues>
<ParameterValues>
<ParameterValue>
<Value>Brad</Value>
</ParameterValue>
<ParameterValue>
<Value>Jon</Value>
</ParameterValue>
<ParameterValue>
<Value>Steve</Value>
</ParameterValue>
</ParameterValues>
</ValidValues>
<Prompt>FirstName</Prompt>
</ReportParameter>
<ReportParameter Name="LastName">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>Syputa</Value>
</Values>
</DefaultValue>
<Prompt>LastName</Prompt>
<ValidValues>
<DataSetReference>
<DataSetName>Northwind</DataSetName>
<ValueField>EmployeeLastName</ValueField> --Field(column) in the
dataset.
<LabelField>EmployeeLastName</LabelField>
</DataSetReference>
</ValidValues>
</ReportParameter>
</ReportParameters>
--
| From: chrispragash@.hotmail.com (CPragash)
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| Subject: Changing Parameters Dynamically
| Date: 14 Sep 2004 10:57:39 -0700
| Organization: http://groups.google.com
| Lines: 11
| Message-ID: <79c9faae.0409140957.43a028f6@.posting.google.com>
| NNTP-Posting-Host: 167.7.17.3
| Content-Type: text/plain; charset=ISO-8859-1
| Content-Transfer-Encoding: 8bit
| X-Trace: posting.google.com 1095184659 14011 127.0.0.1 (14 Sep 2004
17:57:39 GMT)
| X-Complaints-To: groups-abuse@.google.com
| NNTP-Posting-Date: Tue, 14 Sep 2004 17:57:39 +0000 (UTC)
| Path:
cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed00.sul.t-online.de!t-onlin
e.de!news-spur1.maxwell.syr.edu!news.maxwell.syr.edu!postnews2.google.com!no
t-for-mail
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.reportingsvcs:29153
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Hello All,
|
| How do I change paramters dynamically based on user selection. For
| example if I let the user select country in a drop down, and based on
| the country, I need to load the states parameter, and based on the
| states, the counties parameter etc...Is such a dynamic layout possible
| using reporting services and VS.NET? If so how? Any links or code
| samples would be really helpful.
|
| Thanks,
| Chris
|

Changing output order for xml explicit

This one shouldn't be complicated or unusual but it's got me stumped!
Here's an example query.
select 1 as Tag,
0 as Parent,
us_id as [Person!1!Person!hide],
'' as [Address!2!Address1!element],
'' as [Address!2!Postcode!element],
us_telephone as [Person!1!Telephone!element]
from tblUser
union
select 2 as Tag,
1 as Parent,
us_id as [Person!1!Person!hide],
us_addr1 as [Address!2!Address1!element],
us_postcode as [Address!2!Postcode!element],
us_telephone as [Person!1!Telephone!element]
from tblUser
order by [Person!1!Person!hide]
for xml explicit
This gives the output :-
<Person>
<Telephone>t1</Telephone>
<Address>
<Address1>a1</Address1>
<Postcode>p1</Postcode>
</Address>
</Person>
The output I require is :-
<Person>
<Address>
<Address1>a1</Address1>
<Postcode>p1</Postcode>
</Address>
<Telephone>t1</Telephone>
</Person>
i.e. put the telephone number after the address.
The application this is for is currently on SQLServer 2000, but can be
migrated to 2005 if that would help me get the data in the required format.
Thanks for any help.Hello maggi,
The order follows the order in your query have you tried the query below.
In addition you are populating the telephone number in the select for the
address element (Tag 2) even through it is populated on the main Person elem
ent
(Tag 1).
This is only required if you need to order by telephone number.
select 1 as Tag,
0 as Parent,
us_id as [Person!1!Person!hide],
us_telephone as [Person!1!Telephone!element]
'' as [Address!2!Address1!element],
'' as [Address!2!Postcode!element],
from tblUser
union
select 2 as Tag,
1 as Parent,
us_id as [Person!1!Person!hide],
us_addr1 as [Address!2!Address1!element],
us_postcode as [Address!2!Postcode!element],
null
from tblUser
order by [Person!1!Person!hide]
for xml explicit
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons

> This one shouldn't be complicated or unusual but it's got me stumped!
> Here's an example query.
> select 1 as Tag,
> 0 as Parent,
> us_id as [Person!1!Person!hide],
> '' as [Address!2!Address1!element],
> '' as [Address!2!Postcode!element],
> us_telephone as [Person!1!Telephone!element]
> from tblUser
> union
> select 2 as Tag,
> 1 as Parent,
> us_id as [Person!1!Person!hide],
> us_addr1 as [Address!2!Address1!element],
> us_postcode as [Address!2!Postcode!element],
> us_telephone as [Person!1!Telephone!element]
> from tblUser
> order by [Person!1!Person!hide]
> for xml explicit
> This gives the output :-
> <Person>
> <Telephone>t1</Telephone>
> <Address>
> <Address1>a1</Address1>
> <Postcode>p1</Postcode>
> </Address>
> </Person>
> The output I require is :-
> <Person>
> <Address>
> <Address1>a1</Address1>
> <Postcode>p1</Postcode>
> </Address>
> <Telephone>t1</Telephone>
> </Person>
> i.e. put the telephone number after the address.
> The application this is for is currently on SQLServer 2000, but can be
> migrated to 2005 if that would help me get the data in the required
> format.
> Thanks for any help.
>|||In 62959f1a501d68c92bc11929e988@.msnews.microsoft.com, Simon Sabin
<SimonSabin@.noemail.noemail> typed:

> The order follows the order in your query have you tried the query
> below. In addition you are populating the telephone number in the select
> for
> the address element (Tag 2) even through it is populated on the main
> Person element (Tag 1).
> This is only required if you need to order by telephone number.
> select 1 as Tag,
> 0 as Parent,
> us_id as [Person!1!Person!hide],
> us_telephone as [Person!1!Telephone!element]
> '' as [Address!2!Address1!element],
> '' as [Address!2!Postcode!element],
> from tblUser
> union
> select 2 as Tag,
> 1 as Parent,
> us_id as [Person!1!Person!hide],
> us_addr1 as [Address!2!Address1!element],
> us_postcode as [Address!2!Postcode!element],
> null
> from tblUser
> order by [Person!1!Person!hide]
> for xml explicit
Cheers Simon, but that still wouldn't sort the telephone number AFTER the
address details to give me
<Person>
<Address>
<Address1>a1</Address1>
<Postcode>p1</Postcode>
</Address>
<Telephone>t1</Telephone>
</Person>
I've given up on it in SQL Server 2000, and rewritten it using XML AUTO for
2005. I'd still be interested to know if anyone ever figures out a method
for doing it though.|||Try this...
select 1 as Tag,
NULL as Parent,
us_id as [Person!1!Person!hide],
NULL as [Address!2!Address1!element],
NULL as [Address!2!Postcode!element],
us_telephone as [Telephone!3]
from tblUser
union all
select 2 as Tag,
1 as Parent,
us_id as [Person!1!Person!hide],
us_addr1 as [Address!2!Address1!element],
us_postcode as [Address!2!Postcode!element],
us_telephone as [Telephone!3]
from tblUser
union all
select 3 as Tag,
1 as Parent,
us_id as [Person!1!Person!hide],
NULL as [Address!2!Address1!element],
NULL as [Address!2!Postcode!element],
us_telephone as [Telephone!3]
from tblUser
order by [Person!1!Person!hide]
for xml explicit
Peter DeBetta, MVP - SQL Server
http://sqlblog.com
--
"maggi" <maggi_v@.gazeta.pl.invalid> wrote in message
news:eshutp$b8e$1@.inews.gazeta.pl...
> In 62959f1a501d68c92bc11929e988@.msnews.microsoft.com, Simon Sabin
> <SimonSabin@.noemail.noemail> typed:
>
> Cheers Simon, but that still wouldn't sort the telephone number AFTER the
> address details to give me
> <Person>
> <Address>
> <Address1>a1</Address1>
> <Postcode>p1</Postcode>
> </Address>
> <Telephone>t1</Telephone>
> </Person>
> I've given up on it in SQL Server 2000, and rewritten it using XML AUTO
> for 2005. I'd still be interested to know if anyone ever figures out a
> method for doing it though.
>|||In uWrpmZEYHHA.992@.TK2MSFTNGP02.phx.gbl, Peter W. DeBetta
<debettap@.hotmail.com> typed:

> Try this...
> select 1 as Tag,
> NULL as Parent,
> us_id as [Person!1!Person!hide],
> NULL as [Address!2!Address1!element],
> NULL as [Address!2!Postcode!element],
> us_telephone as [Telephone!3]
> from tblUser
> union all
> select 2 as Tag,
> 1 as Parent,
> us_id as [Person!1!Person!hide],
> us_addr1 as [Address!2!Address1!element],
> us_postcode as [Address!2!Postcode!element],
> us_telephone as [Telephone!3]
> from tblUser
> union all
> select 3 as Tag,
> 1 as Parent,
> us_id as [Person!1!Person!hide],
> NULL as [Address!2!Address1!element],
> NULL as [Address!2!Postcode!element],
> us_telephone as [Telephone!3]
> from tblUser
> order by [Person!1!Person!hide]
> for xml explicit
That's the one!
Thank you very much, Peter.

Saturday, February 25, 2012

Changing Number to Text in Query

I have two fields that have either a 0 or 1 in them. When I do a SELECT to
bring the data back, I would like to change the 0 to an "N" and a 1 to a
"Y".
I read up on the CASE statement and had something like but kept getting
syntax errors.
Select Name, Address, City,
CASE field_in_question
WHEN 0 THEN 'N'
WHEN 1 THEN 'Y'
END,
State, Zip from Notes
Can someone help a feebled old brain figure this one out?
Thanks,
Gary>> I read up on the CASE statement and had something like but kept getting
Can you post the error messages here?
Anith|||The error message says "Syntax error (missing operator) in query expression.
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:OCdGspmAFHA.3236@.TK2MSFTNGP15.phx.gbl...
> Can you post the error messages here?
> --
> Anith
>|||Since your original post has no attributions, let us start from simple
issues. Are you use SQL Server or Access? CASE is not supported in Access.
If you are using SQL Server, are you using Query Analyzer? Some other
interfaces have limitations on certain constructs they allow
If you are using Query Analyzer, can you post your CREATE TABLE statement
for the table Notes? For details refer to www.aspfaq.com/5006
Anith|||That's the problem. I am using Access with a Visual Basic front end.
Is there a way to do what I need to do in Access SQL?
Thanks,
Gary
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:eS5ywFnAFHA.3416@.TK2MSFTNGP09.phx.gbl...
> Since your original post has no attributions, let us start from simple
> issues. Are you use SQL Server or Access? CASE is not supported in Access.
> If you are using SQL Server, are you using Query Analyzer? Some other
> interfaces have limitations on certain constructs they allow
> If you are using Query Analyzer, can you post your CREATE TABLE statement
> for the table Notes? For details refer to www.aspfaq.com/5006
> --
> Anith
>|||Create a stored procedure or a view, and select from that within Access
instead of accessing the table directly.
http://www.aspfaq.com/
(Reverse address to reply.)
"Gary Paris" <garyparis@.yada.com> wrote in message
news:ePQKtTnAFHA.2712@.TK2MSFTNGP15.phx.gbl...
> That's the problem. I am using Access with a Visual Basic front end.
> Is there a way to do what I need to do in Access SQL?
> Thanks,
> Gary
> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> news:eS5ywFnAFHA.3416@.TK2MSFTNGP09.phx.gbl...
Access.
statement
>|||Oh, and don't use the view designer, because it doesn't support case! Use
Query Analyzer...
http://www.aspfaq.com/
(Reverse address to reply.)
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uNFbUYnAFHA.2196@.TK2MSFTNGP14.phx.gbl...
> Create a stored procedure or a view, and select from that within Access
> instead of accessing the table directly.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Gary Paris" <garyparis@.yada.com> wrote in message
> news:ePQKtTnAFHA.2712@.TK2MSFTNGP15.phx.gbl...
> Access.
> statement
>|||I am using Access as the back end and not using SQL server. I don't believe
Query Analyzer comes with Access (although I could be wrong). Would it be
better to post my question in the Access group or is there a way to do what
I want with Access SQL?
Thanks,
Gary
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:e4cZzanAFHA.3264@.TK2MSFTNGP12.phx.gbl...
> Oh, and don't use the view designer, because it doesn't support case! Use
> Query Analyzer...
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:uNFbUYnAFHA.2196@.TK2MSFTNGP14.phx.gbl...
>|||Sorry, I thought you were using Access to connect to SQL Server. Yes, you
can do this in Access using the IIF function. See the Access documentation
or post to an Access newsgroup. This group is dedicated to programming
in/for SQL Server.
"Gary Paris" <garyparis@.yada.com> wrote in message
news:O3$5VinAFHA.3988@.TK2MSFTNGP11.phx.gbl...
> I am using Access as the back end and not using SQL server. I don't
believe
> Query Analyzer comes with Access (although I could be wrong). Would it
be
> better to post my question in the Access group or is there a way to do
what
> I want with Access SQL?
> Thanks,
> Gary
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:e4cZzanAFHA.3264@.TK2MSFTNGP12.phx.gbl...
Use
other
>

Sunday, February 19, 2012

Changing from dropdowns to datepickers

Hi guys,

just a simple question here: i have some input parameters on my report that are datetimes (i.e. the user gets the date picker to select the date), if i want to use that parameter in a MDX statement what will it look like? IOW would today's date look like the string "13/02/07", or would i be expecting a string like this: "2007/02/13 00:00"?

I am looking to convert some dropdowns that contain dates extracted from a cube heirarchy with the datepickers so i need to know what i have to change in the MDX to accomodate this.

I also filter the dates that appear in the current dropdowns, is there a way to do this with the datepickers (maybe by pointing them to a dataset of dates extracted from the cube)?

Thanks!

sluggy

This report sample may help.

<?xml version="1.0" encoding="utf-8"?>

<DataSourceReference>AdventureWorksAS</DataSourceReference>

<rd:DataSourceID>aad8c909-02c6-4fb6-841c-0557ea327ec4</rd:DataSourceID>

</DataSource>

</DataSources>

<DataType>String</DataType>

<Value>[Product].[Product Categories].[Category].&[1]</Value>

</Values>

</DefaultValue>

<Prompt>Product Categories</Prompt>

<DataSetName>ProductProductCategories</DataSetName>

<ValueField>ParameterValue</ValueField>

<LabelField>ParameterCaptionIndented</LabelField>

</DataSetReference>

</ValidValues>

</ReportParameter>

<DataType>DateTime</DataType>

</Values>

</DefaultValue>

</ReportParameter>

</ReportParameters>

<rd:DrawGrid>true</rd:DrawGrid>

<rd:SnapToGrid>true</rd:SnapToGrid>

<Body>

<rd:DefaultName>textbox7</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox8</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox9</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox6</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox12</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

</TableRow>

</TableRows>

</Footer>

<rd:DefaultName>textbox16</rd:DefaultName>

<Style>

</Style>

<Value>Category Total:</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox17</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox18</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<Style>

<TextAlign>Right</TextAlign>

</Style>

<Value>=Sum(Fields!InternetSalesAmount.Value)</Value>

</Textbox>

</ReportItems>

</TableCell>

<Style>

<TextAlign>Right</TextAlign>

</Style>

<Value>=Sum(Fields!ResellerSalesAmount.Value)</Value>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

</TableRow>

</TableRows>

</Footer>

<rd:DefaultName>Category</rd:DefaultName>

<Style>

</Style>

<Value>=Fields!Category.Value</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox11</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox13</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox14</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox15</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

</TableRow>

</TableRows>

</Header>

<GroupExpression>=Fields!Category.Value</GroupExpression>

</GroupExpressions>

</Grouping>

</TableGroup>

<rd:DefaultName>textbox26</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox27</rd:DefaultName>

<Style>

</Style>

<Value>Subcategory Total:</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox28</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>InternetSalesAmount_1</rd:DefaultName>

<Style>

<TextAlign>Right</TextAlign>

</Style>

<Value>=Aggregate(Fields!InternetSalesAmount.Value)</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>ResellerSalesAmount_1</rd:DefaultName>

<Style>

<TextAlign>Right</TextAlign>

</Style>

<Value>=Sum(Fields!ResellerSalesAmount.Value)</Value>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

</TableRow>

</TableRows>

</Footer>

<rd:DefaultName>textbox21</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>Subcategory</rd:DefaultName>

<Style>

</Style>

<Value>=Fields!Subcategory.Value</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox23</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox24</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox25</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

</TableRow>

</TableRows>

</Header>

<GroupExpression>=Fields!Subcategory.Value</GroupExpression>

</GroupExpressions>

</Grouping>

</TableGroup>

</TableGroups>

<rd:DefaultName>textbox5</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox22</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>Product</rd:DefaultName>

<Style>

</Style>

<Value>=Fields!Product.Value</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>InternetSalesAmount</rd:DefaultName>

<Style>

<TextAlign>Right</TextAlign>

</Style>

<Value>=Fields!InternetSalesAmount.FormattedValue</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>ResellerSalesAmount</rd:DefaultName>

<Style>

<TextAlign>Right</TextAlign>

</Style>

<Value>=Fields!ResellerSalesAmount.Value</Value>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

</TableRow>

</TableRows>

</Details>

<rd:DefaultName>textbox1</rd:DefaultName>

<Style>

</Style>

<Value>Category</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox2</rd:DefaultName>

<Style>

</Style>

<Value>Subcategory</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox3</rd:DefaultName>

<Style>

</Style>

<Value>Product</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox4</rd:DefaultName>

<Style>

<TextAlign>Right</TextAlign>

</Style>

<Value>Internet Sales Amount</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox10</rd:DefaultName>

<Style>

<TextAlign>Right</TextAlign>

</Style>

<Value>Reseller Sales Amount</Value>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

</TableRow>

</TableRows>

</Header>

</TableColumn>

</TableColumn>

</TableColumn>

</TableColumn>

</TableColumn>

</TableColumns>

</Table>

</ReportItems>

</Body>

<rd:ReportID>03e1fd95-077c-4176-a8c7-4d09893efe4e</rd:ReportID>

<Query>

<rd:SuppressAutoUpdate>true</rd:SuppressAutoUpdate>

<CommandText>WITH MEMBER [Measures].[Total Sales] AS '[Measures].[Internet Sales Amount] + [Measures].[Reseller Sales Amount]' SELECT NON EMPTY { [Measures].[Internet Sales Amount], [Measures].[Reseller Sales Amount], [Measures].[Total Sales] } ON COLUMNS, NON EMPTY {[Product].[Product Categories].[Subcategory].ALLMEMBERS, ([Product].[Product Categories].[Product].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT (STRTOMEMBER(@.DateDate, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@.ProductProductCategories) ) ON COLUMNS FROM [Adventure Works])) WHERE ( STRTOMEMBER(@.DateDate)) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS</CommandText>

<Value>=Parameters!ProductProductCategories.Value</Value>

</QueryParameter>

<Value>="[Date].[Date].[" & CDate(Parameters!DateDate.Value).ToString("MMMM d, yyyy") & "]"</Value>

</QueryParameter>

</QueryParameters>

<DataSourceName>AdventureWorksAS</DataSourceName>

<rd:MdxQuery><QueryDefinition xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://schemas.microsoft.com/AnalysisServices/QueryDefinition"><CommandType>MDX</CommandType><Type>Query</Type><QuerySpecification xsi:type="MDXQuerySpecification"><Select><Items><Item><ID xsi:type="Level"><DimensionName>Product</DimensionName><HierarchyName>Product Categories</HierarchyName><HierarchyUniqueName>[Product].[Product Categories]</HierarchyUniqueName><LevelName>Category</LevelName><UniqueName>[Product].[Product Categories].[Category]</UniqueName></ID><ItemCaption>Category</ItemCaption><UniqueName>true</UniqueName></Item><Item><ID xsi:type="Level"><DimensionName>Product</DimensionName><HierarchyName>Product Categories</HierarchyName><HierarchyUniqueName>[Product].[Product Categories]</HierarchyUniqueName><LevelName>Subcategory</LevelName><UniqueName>[Product].[Product Categories].[Subcategory]</UniqueName></ID><ItemCaption>Subcategory</ItemCaption><UniqueName>true</UniqueName></Item><Item><ID xsi:type="Level"><DimensionName>Product</DimensionName><HierarchyName>Product Categories</HierarchyName><HierarchyUniqueName>[Product].[Product Categories]</HierarchyUniqueName><LevelName>Product</LevelName><UniqueName>[Product].[Product Categories].[Product]</UniqueName></ID><ItemCaption>Product</ItemCaption><UniqueName>true</UniqueName></Item><Item><ID xsi:type="Measure"><MeasureName>Internet Sales Amount</MeasureName><UniqueName>[Measures].[Internet Sales Amount]</UniqueName></ID><ItemCaption>Internet Sales Amount</ItemCaption><BackColor>true</BackColor><ForeColor>true</ForeColor><FontFamily>true</FontFamily><FontSize>true</FontSize><FontWeight>true</FontWeight><FontStyle>true</FontStyle><FontDecoration>true</FontDecoration><FormattedValue>true</FormattedValue><FormatString>true</FormatString></Item><Item><ID xsi:type="Measure"><MeasureName>Reseller Sales Amount</MeasureName><UniqueName>[Measures].[Reseller Sales Amount]</UniqueName></ID><ItemCaption>Reseller Sales Amount</ItemCaption><BackColor>true</BackColor><ForeColor>true</ForeColor><FontFamily>true</FontFamily><FontSize>true</FontSize><FontWeight>true</FontWeight><FontStyle>true</FontStyle><FontDecoration>true</FontDecoration><FormattedValue>true</FormattedValue><FormatString>true</FormatString></Item><Item><ID xsi:type="Measure"><MeasureName>Total Sales</MeasureName><UniqueName>[Measures].[Total Sales]</UniqueName></ID><ItemCaption>Total Sales</ItemCaption><BackColor>true</BackColor><ForeColor>true</ForeColor><FontFamily>true</FontFamily><FontSize>true</FontSize><FontWeight>true</FontWeight><FontStyle>true</FontStyle><FontDecoration>true</FontDecoration><FormattedValue>true</FormattedValue><FormatString>true</FormatString></Item></Items></Select><From>Adventure Works</From><Filter><FilterItems /></Filter><Calculations /><Aggregates /><QueryProperties /></QuerySpecification><Query><Statement>WITH MEMBER [Measures].[Total Sales] AS '[Measures].[Internet Sales Amount] + [Measures].[Reseller Sales Amount]' SELECT NON EMPTY { [Measures].[Internet Sales Amount], [Measures].[Reseller Sales Amount], [Measures].[Total Sales] } ON COLUMNS, NON EMPTY {[Product].[Product Categories].[Subcategory].ALLMEMBERS, ([Product].[Product Categories].[Product].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT (STRTOMEMBER(@.DateDate, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@.ProductProductCategories) ) ON COLUMNS FROM [Adventure Works])) WHERE ( STRTOMEMBER(@.DateDate)) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS</Statement><ParameterDefinitions><ParameterDefinition><Name>ProductProductCategories</Name><DefaultValues><DefaultValue>[Product].[Product Categories].[Category].&[1]</DefaultValue></DefaultValues><Caption>Product Categories</Caption><HierarchyUniqueName>[Product].[Product Categories]</HierarchyUniqueName><ParameterValuesQuery><Statement>WITH MEMBER [Measures].[ParameterCaption] AS '[Product].[Product Categories].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Product].[Product Categories].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Product].[Product Categories].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Product].[Product Categories].ALLMEMBERS ON ROWS FROM [Adventure Works]</Statement><ParameterizedStatement><ReferencedParameters /></ParameterizedStatement></ParameterValuesQuery><MultipleValues>true</MultipleValues></ParameterDefinition><ParameterDefinition><Name>DateDate</Name><DefaultValues><DefaultValue>[Date].[Date].[All Periods]</DefaultValue></DefaultValues><Caption>Date.Date</Caption><HierarchyUniqueName>[Date].[Date]</HierarchyUniqueName><ParameterValuesQuery><Statement>WITH MEMBER [Measures].[ParameterCaption] AS '[Date].[Date].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Date].[Date].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Date].[Date].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Date].[Date].ALLMEMBERS ON ROWS FROM [Adventure Works]</Statement><ParameterizedStatement><ReferencedParameters /></ParameterizedStatement></ParameterValuesQuery><MultipleValues>true</MultipleValues></ParameterDefinition></ParameterDefinitions></Query></QueryDefinition></rd:MdxQuery>

</Query>

<rd:TypeName>System.String</rd:TypeName>

</Field>

<rd:TypeName>System.String</rd:TypeName>

</Field>

<rd:TypeName>System.String</rd:TypeName>

</Field>

<rd:TypeName>System.Int32</rd:TypeName>

</Field>

<rd:TypeName>System.Int32</rd:TypeName>

</Field>

<rd:TypeName>System.Int32</rd:TypeName>

</Field>

</Fields>

</DataSet>

<Query>

<rd:SuppressAutoUpdate>true</rd:SuppressAutoUpdate>

<CommandText>WITH MEMBER [Measures].[ParameterCaption] AS '[Product].[Product Categories].CURRENTMEMBER.MEMBER_CAPTION'

MEMBER [Measures].[ParameterValue] AS '[Product].[Product Categories].CURRENTMEMBER.UNIQUENAME'

MEMBER [Measures].[ParameterLevel] AS '[Product].[Product Categories].CURRENTMEMBER.LEVEL.ORDINAL'

SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS ,

DrilldownLevel([Product].[Product Categories].[Category].ALLMEMBERS) ON ROWS

FROM [Adventure Works]</CommandText>

<DataSourceName>AdventureWorksAS</DataSourceName>

<rd:AutoGenerated>true</rd:AutoGenerated>

MEMBER [Measures].[ParameterValue] AS '[Product].[Product Categories].CURRENTMEMBER.UNIQUENAME'

MEMBER [Measures].[ParameterLevel] AS '[Product].[Product Categories].CURRENTMEMBER.LEVEL.ORDINAL'

SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS ,

DrilldownLevel([Product].[Product Categories].[Category].ALLMEMBERS) ON ROWS

FROM [Adventure Works]</Statement><ParameterDefinitions /></Query></QueryDefinition></rd:MdxQuery>

<rd:Hidden>true</rd:Hidden>

</Query>

<rd:TypeName>System.String</rd:TypeName>

</Field>

<rd:TypeName>System.String</rd:TypeName>

</Field>

<rd:TypeName>System.Int32</rd:TypeName>

</Field>

<rd:TypeName>System.Int32</rd:TypeName>

</Field>

<rd:TypeName>System.Int32</rd:TypeName>

</Field>

<Value>=Space(3*Fields!ParameterLevel.Value) + Fields!ParameterCaption.Value</Value>

</Field>

</Fields>

</DataSet>

</DataSets>

</Report>

|||

Thanks Teo, I'll pull that apart and have a look at it.

Cheers

sluggy

|||

Note that whatever you do with the date parameter, you need to comply with the AS member format, e.g. [Date][Day].[Day].&[20070213]. For the sample report, I think I pulled a little trick where I changed the Value property of the dimension key in the AW Date dimension to load the DateTime value. Also you can try:

1. Using the MDX query designer for your main query, define a date parameter.

2. Edit the report parameter it created. Make it DateTime type. Don't query the database. Uncheck multi-value. Default it to null.

3. Delete the extra dataset it created which would have driven the date parameter.

4. Edit the query parameter and write an expression which converts the DateTime from the parameter into an MDX member name... such as:
="[Date].[Date].&[" & Year(Parameters!DateDate.Value) & Right("0" & Month(Parameters!DateDate.Value),2) & Right("0" & Day(Parameters!DateDate.Value),2) & "]"

Sunday, February 12, 2012

Changing date to string of numbers

I basic question but can someone help.

I have a SELECT statement, the result of which populates adatagrid. The first column has consecutive dates in it and I wantto hyperlink each date to a seperate Javascript function (theJavascript is created on the fly and is unique for each date). Ineed a different function name for each function and so tried the datebut "/" is not allowed in the Javasript function name. I thinkthe easiest way will be to produce a new column with the date expressesddmmyyyy, ddmmyy or some such unique number (but not dd/mm/yyyy). I tried :-

"CASE " & _
"WHEN t3.date = t3.date THEN (DAY(t3.Date) + MONTH(t3.Date) + YEAR(t3.Date)) ELSE NULL END AS [javaKey]

but this adds the year to the month to the day - not a unique result as 1/2/06 and 2/1/06 are the same.

I am just getting to grips with VB.Net (as an amature) but am a distinct beginner at SQL!

Many thanks

Mike

Hi Mike,

You can use the ISO format in this place:

CONVERT(NCHAR(8), [Date], 112)AS newDate

the date format will be yyyymmdd.

You can always check CONVERT DATE function from Books Online to convert your date.

Hope this helps.

|||Hi Limno

Many thanks. Your reply is just what I need. It works great.

I am sorry that could not figure it out for myself. I do usebooks on line and I have "Microsoft SQL Server 2005 A Beginner'sGuide" (which I got before I realise my server uses 2000!) and "SAMSTeach Yourself SQL 24 Hours". I started off learning VB.net butas my project goes on, rather than feeling that I am becoming competentat producing the web pages I want, I seem require more and moreknowledge (like SQL, JavaScript & CSS). Sometimes I feel I amgetting there, the next minute feel totally inadequate!! I strive tolearn, and in the meantime I really do appreciate the help of peoplelike yourself.

Many many thanks for your time and patience.

Regards

Mike

changing date formats

Hello. I am using Microsoft SQL Server Management Studio (SQL Server 2005). When I select a date column from a table, the date is displayed in "mm/dd/yyyy hh:mm:ss" format. Is there a way i can change this date format so that it shows "dd/mm/yyyy hh:mm:ss" permanently? Thanks.If u're using VB then it can be done.
say its a DateTimePicker so...
Format(dtp.Value, "d/M/yyyy")
|||If you want to do it in SQL Server check out the CONVERT function.
It takes three parameters (for dates)
eg. SELECT CONVERT(varchar, GetDate(), 121)
Check out BOL for possible values and a description of the last parameter, this defines the format.
hth

Changing Database Context in T-SQL

How can one change the database context within a stored procedure? In other
words, something like this ...
Declare @.DB varchar(20)
set @.DB = (Select databasename from databases where activedatabase = 1)
Use @.DB
Select * from Table1
This code doesn't work, but I'm sure there's a way to do this.
Thanks for the help.
Ross> Use @.DB
> Select * from Table1
> This code doesn't work, but I'm sure there's a way to do this.
Sure, you can use dynamic SQL, blecch.
EXEC('SELECT <column_list> FROM '+@.DB+'.dbo.Table1')
Don't use SELECT * in production code.|||Ok, is there another option?
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uWfLR9PnFHA.3448@.TK2MSFTNGP12.phx.gbl...
> Sure, you can use dynamic SQL, blecch.
> EXEC('SELECT <column_list> FROM '+@.DB+'.dbo.Table1')
> Don't use SELECT * in production code.
>|||Why?
Can't you use four-part names for objects?
ML|||USE takes a literal string for the database name. In your case, you would
have to use dynamic query to for further processing.
So, the answer is 'no'.
-oj
"Ross Culver" <rculver@.alliant-solutions.com> wrote in message
news:Ok23XBQnFHA.2484@.TK2MSFTNGP15.phx.gbl...
> Ok, is there another option?
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:uWfLR9PnFHA.3448@.TK2MSFTNGP12.phx.gbl...
>|||What are you trying to accomplish? Most likely, you need to do something
like:
databasename.owner.tablename
SQL code stored in SQL Server is not really made to do dynamic stuff like
this. T-SQL is a super duper language for querying the database, but lousy
for doing much string manipulation. If you want to do stuff like this, use
your middle tier objects where it is far easier to build robust string
building facilities.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Ross Culver" <rculver@.alliant-solutions.com> wrote in message
news:O$TAu4PnFHA.3120@.TK2MSFTNGP09.phx.gbl...
> How can one change the database context within a stored procedure? In
> other words, something like this ...
> Declare @.DB varchar(20)
> set @.DB = (Select databasename from databases where activedatabase = 1)
> Use @.DB
> Select * from Table1
> This code doesn't work, but I'm sure there's a way to do this.
> Thanks for the help.
> Ross
>

Friday, February 10, 2012

Changing Data Format On Select

Hey all,
I have a basic table that looks something like this.

CREATE TABLE MyTable
(
ID INT IDENTITY PRIMARY KEY,
Company_ID INT NOT NULL,
Round VARCHAR(50) NOT NULL,
Details VARCHAR(250) NOT NULL
)

It has a few rows of data that look like this:

Identity Company_ID Round Details
--------------
1 5 A Blah, blah.
2 5 B Generic data, blah blah.
3 5 WERT More generic blah blah.

Now what i'm trying to do during my select statement is select all the rows
that belong to company_id 5 but if any of the rows round value contains the
text "WERT" convert that text into just a "--" for presentation purposes,
but still select that row. I can't seem to figure out how i would transform
the text in the select statement? My immediate thought was substring /
replace but i would need to combine it with an if else statement which i've
no idea how to make work in a select (sub-query maybe?) statement. Is this
possible? Perhaps i'm stuck iterating through the returned data within the
client application before presenting?

Any help, as always, would be greatly appreciated.

MuhdMuhd,

SELECT ID, Company_ID,
[Round] = CASE [Round] WHEN 'WERT' THEN '--' ELSE [Round] END,
Details
FROM MyTable
WHERE Company_ID = 5

-Andy

"Muhd" <eat@.joes.com> wrote in message news:7MZ4d.91084$%S.84951@.pd7tw2no...
> Hey all,
> I have a basic table that looks something like this.
> CREATE TABLE MyTable
> (
> ID INT IDENTITY PRIMARY KEY,
> Company_ID INT NOT NULL,
> Round VARCHAR(50) NOT NULL,
> Details VARCHAR(250) NOT NULL
> )
> It has a few rows of data that look like this:
> Identity Company_ID Round Details
> --------------
> 1 5 A Blah, blah.
> 2 5 B Generic data, blah blah.
> 3 5 WERT More generic blah blah.
>
> Now what i'm trying to do during my select statement is select all the
> rows that belong to company_id 5 but if any of the rows round value
> contains the text "WERT" convert that text into just a "--" for
> presentation purposes, but still select that row. I can't seem to figure
> out how i would transform the text in the select statement? My immediate
> thought was substring / replace but i would need to combine it with an if
> else statement which i've no idea how to make work in a select (sub-query
> maybe?) statement. Is this possible? Perhaps i'm stuck iterating through
> the returned data within the client application before presenting?
> Any help, as always, would be greatly appreciated.
> Muhd|||Although Andy has shown you how you can accomplish the task using
Transact-SQL, formatting data for presentation purposes is generally best
handled on the client side, IMHO. Most programming languages provide a
number of methods to format data.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Muhd" <eat@.joes.com> wrote in message news:7MZ4d.91084$%S.84951@.pd7tw2no...
> Hey all,
> I have a basic table that looks something like this.
> CREATE TABLE MyTable
> (
> ID INT IDENTITY PRIMARY KEY,
> Company_ID INT NOT NULL,
> Round VARCHAR(50) NOT NULL,
> Details VARCHAR(250) NOT NULL
> )
> It has a few rows of data that look like this:
> Identity Company_ID Round Details
> --------------
> 1 5 A Blah, blah.
> 2 5 B Generic data, blah blah.
> 3 5 WERT More generic blah blah.
>
> Now what i'm trying to do during my select statement is select all the
> rows that belong to company_id 5 but if any of the rows round value
> contains the text "WERT" convert that text into just a "--" for
> presentation purposes, but still select that row. I can't seem to figure
> out how i would transform the text in the select statement? My immediate
> thought was substring / replace but i would need to combine it with an if
> else statement which i've no idea how to make work in a select (sub-query
> maybe?) statement. Is this possible? Perhaps i'm stuck iterating through
> the returned data within the client application before presenting?
> Any help, as always, would be greatly appreciated.
> Muhd|||While the front end is a very good place for presentation or value decoding
...
having a record transformation in the database allows the logic to be
centralized and recorded somewhere.

what if in addition to WERT you need to ignore SPAM, just add a row to the
table. no front end changes needed.

create a decoder table and join to that returning --

create view round_decoder
(
Round VARCHAR(50) NOT NULL,
DisplayName VARCHAR(50) NOT NULL
)

insert into round_decoder ( 'WERT' , '--' )

SELECT
Identity,
Company_ID ,
IsNull( d.DisplayName , x.Round ) as Round ,
Details
FROM MyTable x
LEFT JOIN round_decoder d on x.Round = d.Round

you can query this decoder table for drop down lists or other data
selection / presentation stuff in the front end.
i would recommend a reusable object that get's this data and does the
decoding in the front end.

"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:pfd6d.1939$zc1.1654@.newssvr12.news.prodigy.co m...
> Although Andy has shown you how you can accomplish the task using
> Transact-SQL, formatting data for presentation purposes is generally best
> handled on the client side, IMHO. Most programming languages provide a
> number of methods to format data.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Muhd" <eat@.joes.com> wrote in message
news:7MZ4d.91084$%S.84951@.pd7tw2no...
> > Hey all,
> > I have a basic table that looks something like this.
> > CREATE TABLE MyTable
> > (
> > ID INT IDENTITY PRIMARY KEY,
> > Company_ID INT NOT NULL,
> > Round VARCHAR(50) NOT NULL,
> > Details VARCHAR(250) NOT NULL
> > )
> > It has a few rows of data that look like this:
> > Identity Company_ID Round Details
> > --------------
> > 1 5 A Blah, blah.
> > 2 5 B Generic data, blah
blah.
> > 3 5 WERT More generic blah blah.
> > Now what i'm trying to do during my select statement is select all the
> > rows that belong to company_id 5 but if any of the rows round value
> > contains the text "WERT" convert that text into just a "--" for
> > presentation purposes, but still select that row. I can't seem to
figure
> > out how i would transform the text in the select statement? My
immediate
> > thought was substring / replace but i would need to combine it with an
if
> > else statement which i've no idea how to make work in a select
(sub-query
> > maybe?) statement. Is this possible? Perhaps i'm stuck iterating
through
> > the returned data within the client application before presenting?
> > Any help, as always, would be greatly appreciated.
> > Muhd|||Thanks for that, David... Great idea!

-Andy

"David Rawheiser" <rawhide58@.hotmail.com> wrote in message
news:4Cx6d.645799$Gx4.11917@.bgtnsc04-news.ops.worldnet.att.net...
> While the front end is a very good place for presentation or value
> decoding
> ...
> having a record transformation in the database allows the logic to be
> centralized and recorded somewhere.
> what if in addition to WERT you need to ignore SPAM, just add a row to the
> table. no front end changes needed.
> create a decoder table and join to that returning --
> create view round_decoder
> (
> Round VARCHAR(50) NOT NULL,
> DisplayName VARCHAR(50) NOT NULL
> )
> insert into round_decoder ( 'WERT' , '--' )
> SELECT
> Identity,
> Company_ID ,
> IsNull( d.DisplayName , x.Round ) as Round ,
> Details
> FROM MyTable x
> LEFT JOIN round_decoder d on x.Round = d.Round
> you can query this decoder table for drop down lists or other data
> selection / presentation stuff in the front end.
> i would recommend a reusable object that get's this data and does the
> decoding in the front end.
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:pfd6d.1939$zc1.1654@.newssvr12.news.prodigy.co m...
>> Although Andy has shown you how you can accomplish the task using
>> Transact-SQL, formatting data for presentation purposes is generally best
>> handled on the client side, IMHO. Most programming languages provide a
>> number of methods to format data.
>>
>> --
>> Hope this helps.
>>
>> Dan Guzman
>> SQL Server MVP
>>
>> "Muhd" <eat@.joes.com> wrote in message
> news:7MZ4d.91084$%S.84951@.pd7tw2no...
>> > Hey all,
>> > I have a basic table that looks something like this.
>>> > CREATE TABLE MyTable
>> > (
>> > ID INT IDENTITY PRIMARY KEY,
>> > Company_ID INT NOT NULL,
>> > Round VARCHAR(50) NOT NULL,
>> > Details VARCHAR(250) NOT NULL
>> > )
>>> > It has a few rows of data that look like this:
>>> > Identity Company_ID Round Details
>> > --------------
>> > 1 5 A Blah, blah.
>> > 2 5 B Generic data, blah
> blah.
>> > 3 5 WERT More generic blah blah.
>>>> > Now what i'm trying to do during my select statement is select all the
>> > rows that belong to company_id 5 but if any of the rows round value
>> > contains the text "WERT" convert that text into just a "--" for
>> > presentation purposes, but still select that row. I can't seem to
> figure
>> > out how i would transform the text in the select statement? My
> immediate
>> > thought was substring / replace but i would need to combine it with an
> if
>> > else statement which i've no idea how to make work in a select
> (sub-query
>> > maybe?) statement. Is this possible? Perhaps i'm stuck iterating
> through
>> > the returned data within the client application before presenting?
>>> > Any help, as always, would be greatly appreciated.
>>> > Muhd
>>>
>>|||Thanks all !!!

I've decided to keep the presentation logic on the presentation tier but i
did rig up a "decoder" table to give it a try and it worked for me really
well. It's something i'll have to add to my toolkit of tricks in case i
need it in the future.

Thanks,
Muhd.

"Andy Williams" <f_u_b_a_r_1_1_1_9@.y_a_h_o_o_._c_o_m> wrote in message
news:c1z6d.11644$Qv5.5768@.newssvr33.news.prodigy.c om...
> Thanks for that, David... Great idea!
> -Andy
> "David Rawheiser" <rawhide58@.hotmail.com> wrote in message
> news:4Cx6d.645799$Gx4.11917@.bgtnsc04-news.ops.worldnet.att.net...
>> While the front end is a very good place for presentation or value
>> decoding
>> ...
>> having a record transformation in the database allows the logic to be
>> centralized and recorded somewhere.
>>
>> what if in addition to WERT you need to ignore SPAM, just add a row to
>> the
>> table. no front end changes needed.
>>
>> create a decoder table and join to that returning --
>>
>> create view round_decoder
>> (
>> Round VARCHAR(50) NOT NULL,
>> DisplayName VARCHAR(50) NOT NULL
>> )
>>
>> insert into round_decoder ( 'WERT' , '--' )
>>
>> SELECT
>> Identity,
>> Company_ID ,
>> IsNull( d.DisplayName , x.Round ) as Round ,
>> Details
>> FROM MyTable x
>> LEFT JOIN round_decoder d on x.Round = d.Round
>>
>> you can query this decoder table for drop down lists or other data
>> selection / presentation stuff in the front end.
>> i would recommend a reusable object that get's this data and does the
>> decoding in the front end.
>>
>> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
>> news:pfd6d.1939$zc1.1654@.newssvr12.news.prodigy.co m...
>>> Although Andy has shown you how you can accomplish the task using
>>> Transact-SQL, formatting data for presentation purposes is generally
>>> best
>>> handled on the client side, IMHO. Most programming languages provide a
>>> number of methods to format data.
>>>
>>> --
>>> Hope this helps.
>>>
>>> Dan Guzman
>>> SQL Server MVP
>>>
>>> "Muhd" <eat@.joes.com> wrote in message
>> news:7MZ4d.91084$%S.84951@.pd7tw2no...
>>> > Hey all,
>>> > I have a basic table that looks something like this.
>>>>> > CREATE TABLE MyTable
>>> > (
>>> > ID INT IDENTITY PRIMARY KEY,
>>> > Company_ID INT NOT NULL,
>>> > Round VARCHAR(50) NOT NULL,
>>> > Details VARCHAR(250) NOT NULL
>>> > )
>>>>> > It has a few rows of data that look like this:
>>>>> > Identity Company_ID Round Details
>>> > --------------
>>> > 1 5 A Blah, blah.
>>> > 2 5 B Generic data, blah
>> blah.
>>> > 3 5 WERT More generic blah blah.
>>>>>>> > Now what i'm trying to do during my select statement is select all the
>>> > rows that belong to company_id 5 but if any of the rows round value
>>> > contains the text "WERT" convert that text into just a "--" for
>>> > presentation purposes, but still select that row. I can't seem to
>> figure
>>> > out how i would transform the text in the select statement? My
>> immediate
>>> > thought was substring / replace but i would need to combine it with an
>> if
>>> > else statement which i've no idea how to make work in a select
>> (sub-query
>>> > maybe?) statement. Is this possible? Perhaps i'm stuck iterating
>> through
>>> > the returned data within the client application before presenting?
>>>>> > Any help, as always, would be greatly appreciated.
>>>>> > Muhd
>>>>>
>>>
>>
>>

Tuesday, March 27, 2012

Changing the owner of a table

Changing the owner of a table

Changing the owner of a table

Sunday, March 25, 2012

Changing the file name stored in database properties

Thursday, March 22, 2012

Changing the columns of a SELECT statement.

Changing the columns of a SELECT statement.

Tuesday, March 20, 2012

changing SQL syntax

Sunday, March 11, 2012

changing servers dynamically via TSQL

Wednesday, March 7, 2012

Changing Parameters Dynamically

Changing output order for xml explicit

Saturday, February 25, 2012

Changing Number to Text in Query

Sunday, February 19, 2012

Changing from dropdowns to datepickers

Sunday, February 12, 2012

Changing date to string of numbers

changing date formats

Changing Database Context in T-SQL

Friday, February 10, 2012

Changing Data Format On Select

database collation

Blog Archive

About Me