Monday, March 19, 2012
Displaying the row number in a query
My query is retrieving rows from a table.
All what I need is to display the row number which is simply a consecutive number.
Can any one advise me if there is a direct thing to do this in the SELECT stmt?
I can do it through creating a temp table then add IDENTITY column pla pla pla ..I need a direct way through the query itself.
In Oracle I can use RowNum in the query.
Thanks in advance for all.Is it not doable?
Originally posted by RaedT
Hi,
My query is retrieving rows from a table.
All what I need is to display the row number which is simply a consecutive number.
Can any one advise me if there is a direct thing to do this in the SELECT stmt?
I can do it through creating a temp table then add IDENTITY column pla pla pla ..I need a direct way through the query itself.
In Oracle I can use RowNum in the query.
Thanks in advance for all.|||The short answer is NO. TSQL operations are set based, and no ordering is guaranted by the server unless the developer or programmer specifies it, thus row numbering is pointless because the same query run on two different occasions could result in the same record being assigned different row numbers.
The long answer is YES, if your result set is sorted by a unique key or combination of columns, then you can write a Select statement that loops back on itself and counts the number of records less than each record. This is an expensive query to run and can be difficult to debug, so my recommendation to you would be to use a temporary table (actually, a table variable is more efficient) as long as it suits your needs.
Why do you need the results numbered? While there are some circumstances where this is beneficial, it is often a sign of problems with the database schema or the application design concept.
blindman|||If you don't have already a client, I may consider to put your SELECT statement in the software of a (ADO) client, where you can make use of the AbsolutePosition property of a recordset.
Friday, February 17, 2012
Display Money Type using + and -
hour and experimenting without results.
I simply want the results of my query to display a specific field that
is typed "money" using + and -
The program that consumes the data expects + on positive numbers and -
on negative. I was hoping to do it in the view instead of processing
the results with the VB application that interogates the DB.
ThanksIt should be done in VB
but you can do it like this
select case field when < 0 then '-' + convert(varchar,field) else '+' +
convert(varchar,field) end MoneyField from TableA
I don't know whatt you want to display for 0.00
http://sqlservercode.blogspot.com/|||Why would you do this in the database? And why are you using a MONEY
column in your table?
--
David Portas
SQL Server MVP
--|||Yeah... I guess you're right - it should be done in VB.
I was trying to massage the output from the server rather than expect
each developer (who may be using different tool) from "rolling their
own" routines.
Thanks|||Hi,
MONEY is the type that was used in the database table - I have no
choice with that. I was attempting to do this in the database because
I wanted to store the methods used in "massaging" this data in a
central location. It's just in a view now and maybe I would have moved
it to a parameterized stored procedure later.
So now I'll query from VB, massage the data in VB. Then in 2 years
when somebody re-writes the application using C# for an internet
application they will have to re-write the code instead of just calling
a nicely centralized routine within the database.
I honestly don't mean to offend you but why would you respond to a post
with questions like that?|||No offence. I was trying to help but first I needed more information.
There could have been more than one reason why you'd want to do this -
for example you could have had a requirement to integrate the data with
an external application.
The reason I ask about MONEY in particular is that the problems with
using that datatype in calculations may sometimes be overlooked. Take a
look at the following example. Be aware of the rounding issue when you
develop calculations against the data and think carefully about the
implications before you use MONEY.
DECLA RE
@.mon1 MONEY,
@.mon2 MONEY,
@.mon3 MONEY,
@.mon4 MONEY,
@.num1 DECIMAL(19,4),
@.num2 DECIMAL(19,4),
@.num3 DECIMAL(19,4),
@.num4 DECIMAL(19,4)
SELECT
@.mon1 = 100, @.mon2 = 339, @.mon3 = 10000,
@.num1 = 100, @.num2 = 339, @.num3 = 10000
SET @.mon4 = @.mon1/@.mon2*@.mon3
SET @.num4 = @.num1/@.num2*@.num3
SELECT @.mon4 AS money_result,
@.num4 AS numeric_result
Result:
money_result numeric_result
------- -------
2949.0000 2949.8525
(1 row(s) affected)
--
David Portas
SQL Server MVP
--|||Thanks - that is good information. I would have never guessed that -
however I may be stuck with this data type since I haven't investigate
why it's being used or where I would break something if I changed the
type to decimal.
You've convinced me though - I will never use Money for a datatype.
Cheers|||Why is this the case? From the definition of MONEY in BOL I would expect
the same result.
Mike Reigler
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1128520608.921999.41930@.z14g2000cwz.googlegro ups.com...
> No offence. I was trying to help but first I needed more information.
> There could have been more than one reason why you'd want to do this -
> for example you could have had a requirement to integrate the data with
> an external application.
> The reason I ask about MONEY in particular is that the problems with
> using that datatype in calculations may sometimes be overlooked. Take a
> look at the following example. Be aware of the rounding issue when you
> develop calculations against the data and think carefully about the
> implications before you use MONEY.
> DECLA RE
> @.mon1 MONEY,
> @.mon2 MONEY,
> @.mon3 MONEY,
> @.mon4 MONEY,
> @.num1 DECIMAL(19,4),
> @.num2 DECIMAL(19,4),
> @.num3 DECIMAL(19,4),
> @.num4 DECIMAL(19,4)
> SELECT
> @.mon1 = 100, @.mon2 = 339, @.mon3 = 10000,
> @.num1 = 100, @.num2 = 339, @.num3 = 10000
> SET @.mon4 = @.mon1/@.mon2*@.mon3
> SET @.num4 = @.num1/@.num2*@.num3
> SELECT @.mon4 AS money_result,
> @.num4 AS numeric_result
> Result:
> money_result numeric_result
> ------- -------
> 2949.0000 2949.8525
> (1 row(s) affected)
> --
> David Portas
> SQL Server MVP
> --|||Nevermind. After some more digging I realized what was happening when MONEY
/ MONEY. Thanks.
Mike Reigler
"Mike Reigler" <mreigler@.melange-inc.com> wrote in message
news:11k800hit25ci21@.corp.supernews.com...
> Why is this the case? From the definition of MONEY in BOL I would expect
> the same result.
> Mike Reigler
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1128520608.921999.41930@.z14g2000cwz.googlegro ups.com...
>> No offence. I was trying to help but first I needed more information.
>> There could have been more than one reason why you'd want to do this -
>> for example you could have had a requirement to integrate the data with
>> an external application.
>>
>> The reason I ask about MONEY in particular is that the problems with
>> using that datatype in calculations may sometimes be overlooked. Take a
>> look at the following example. Be aware of the rounding issue when you
>> develop calculations against the data and think carefully about the
>> implications before you use MONEY.
>>
>> DECLA RE
>> @.mon1 MONEY,
>> @.mon2 MONEY,
>> @.mon3 MONEY,
>> @.mon4 MONEY,
>> @.num1 DECIMAL(19,4),
>> @.num2 DECIMAL(19,4),
>> @.num3 DECIMAL(19,4),
>> @.num4 DECIMAL(19,4)
>>
>> SELECT
>> @.mon1 = 100, @.mon2 = 339, @.mon3 = 10000,
>> @.num1 = 100, @.num2 = 339, @.num3 = 10000
>>
>> SET @.mon4 = @.mon1/@.mon2*@.mon3
>> SET @.num4 = @.num1/@.num2*@.num3
>>
>> SELECT @.mon4 AS money_result,
>> @.num4 AS numeric_result
>>
>> Result:
>>
>> money_result numeric_result
>> ------- -------
>> 2949.0000 2949.8525
>>
>> (1 row(s) affected)
>>
>> --
>> David Portas
>> SQL Server MVP
>> --
>>|||Hi:
As others have replied, the formatting should probably stay on the
front end.
If you are looking at centralizing some of the process/format, look
into an object oriented solution. This is offtopic for this newsgroup,
but you could create a money class with a formatting function that can
do the + and - formatting for you. All the developers can then use this
class instead of having to roll out their own routines. This solution
will still encapsulate the logic in one place and you don't have to
polute the database with formatting.
HTH,
BZ|||1) Do not use the proprietary MONEY data type and its weird math in
your schema.;You do Google ALL proprietary data types before you decide
to destroy data integrity, portability, etc. in your schem?
2) Then you did wake up in the middle of a freshman class, so you woudl
know that display is NEVER done in the database? You do know that in a
tiered archtecture requires that display is done in the front end? Do
this in the front end, where it is supposed to be!!!
3) Let's get back to the basics of an RDBMS. Rows are not records;
fields are not columns; tables are not files; there is no sequential
access or ordering in an RDBMS, so "first", "next" and "last" are
totally meaningless. YOu have not learend the most basic concepts of
RDBMS.
Your whole mental/logical model is **totally wrong** and you need to
stop programming because you are dangerously ignorant. My opinion is
based on 20+ years of teaching SQL, writing standards, six books, and
being paid to fix thing like this.|||CELKO, you need to save yourself some time and just setup an auto
routine that emails new users as they come into the SQL group and tells
them they are all retards.
--CELKO-- wrote:
> 1) Do not use the proprietary MONEY data type and its weird math in
> your schema.;You do Google ALL proprietary data types before you decide
> to destroy data integrity, portability, etc. in your schem?
> 2) Then you did wake up in the middle of a freshman class, so you woudl
> know that display is NEVER done in the database? You do know that in a
> tiered archtecture requires that display is done in the front end? Do
> this in the front end, where it is supposed to be!!!
> 3) Let's get back to the basics of an RDBMS. Rows are not records;
> fields are not columns; tables are not files; there is no sequential
> access or ordering in an RDBMS, so "first", "next" and "last" are
> totally meaningless. YOu have not learend the most basic concepts of
> RDBMS.
> Your whole mental/logical model is **totally wrong** and you need to
> stop programming because you are dangerously ignorant. My opinion is
> based on 20+ years of teaching SQL, writing standards, six books, and
> being paid to fix thing like this.
Tuesday, February 14, 2012
display data using mxdatagrid or datagrid in webmatrix
NAME ID
name1 1
name2 2
name3 3
what i did is connect to the database, click and drag it over to the canvas and codes were generated. if i run it, by default it should show all the contents of the table, correct? well, it isn't. i've tried it using a datagrid and it still doesn't work.
is there an access problem with the database? or, what settings to have to make/change in my sql server? by the way, i have MSDE.
What error are you getting? Do you get the same error when using a DataGrid?|||i'm getting the following error message:
<%@. Page Language="VB" %>
<%@. Register TagPrefix="wmx" Namespace="Microsoft.Matrix.Framework.Web.UI" Assembly="Microsoft.Matrix.Framework, Version=0.6.0.0, Culture=neutral, PublicKeyToken=6f763c9966660626" %>
<script runat="server"
' Insert page code here
'
Function test() As System.Data.IDataReader
Dim connectionString As String = "server='(local)'; trusted_connection=true; database='test'"
Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)Dim queryString As String = "SELECT [list].* FROM [list]"
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnectiondbConnection.Open
Dim dataReader As System.Data.IDataReader = dbCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection)Return dataReader
End Function</script>
<html>
<head>
</head>
<body>
<form runat="server">
<wmx:SqlDataSourceControl id="SqlDataSourceControl1" runat="server" DeleteCommand="" ConnectionString="server='(local)'; trusted_connection=true; database='Northwind'" SelectCommand="SELECT * FROM [Employees]" UpdateCommand=""></wmx:SqlDataSourceControl>
<wmx:MxDataGrid id="MxDataGrid1" runat="server" OnLoad="MxDataGrid1_Load" DataSource="<%# SqlDataSourceControl1 %>" BorderStyle="None" BorderWidth="1px" DataKeyField="EmployeeID" CellPadding="3" BackColor="White" AllowPaging="True" DataMember="Employees" AllowSorting="True" BorderColor="#CCCCCC" DataSourceControlID="SqlDataSourceControl1">
<PagerStyle horizontalalign="Center" forecolor="#000066" backcolor="White" mode="NumericPages"></PagerStyle>
<FooterStyle forecolor="#000066" backcolor="White"></FooterStyle>
<SelectedItemStyle font-bold="True" forecolor="White" backcolor="#669999"></SelectedItemStyle>
<ItemStyle forecolor="#000066"></ItemStyle>
<HeaderStyle font-bold="True" forecolor="White" backcolor="#006699"></HeaderStyle>
</wmx:MxDataGrid>
<!-- Insert content here -->
</form>
</body>
</html>
**********************************************
Server Error in '/' Application.
Runtime Error
Description: An application error occurred on the server. The current custom error settings for this application prevent the details of the application error from being viewed remotely (for security reasons). It could, however, be viewed by browsers running on the local server machine.
Details: To enable the details of this specific error message to be viewable on remote machines, please create a <customErrors> tag within a "web.config" configuration file located in the root directory of the current web application. This <customErrors> tag should then have its "mode" attribute set to "Off".
<!-- Web.Config Configuration File --
<configuration>
<system.web>
<customErrors mode="Off"/>
</system.web>
</configuration
Notes: The current error page you are seeing can be replaced by a custom error page by modifying the "defaultRedirect" attribute of the application's <customErrors> configuration tag to point to a custom error page URL.
<!-- Web.Config Configuration File --
<configuration>
<system.web>
<customErrors mode="RemoteOnly" defaultRedirect="mycustompage.htm"/>
</system.web>
</configuration>
****************************************
the sql server path is not in the same folder as the default webpage folder. does that matter? and how do i change the sql server path.|||and is there good textbook that cover ASP.NET using MS WebMatrix?|||This is a good, free online book:Inside ASP.NET WebMatrix by Alex Homer and Dave Sussman.
Terri