Numeric Representation of Data

In MS Access, for numeric fields, the decimal places shown can be defined as "Auto" meaning that the database will determine the number of decimal places to show based on the content of the field (i.e. 1.0, 0.75, 1.125).

In SQL Server for the same field, it appears that decimal precision is hard coded resulting in a fixed representation (i.e. 1.000, 0.750, 1.125)

Is there a way to make the decimal representation in SQL Server more like Access where trailing zeros are truncated?

See SQL Server 2005 Books Online topic:

float and real (Transact-SQL)

http://msdn2.microsoft.com/en-us/library/ms173773.aspx

|||

Perfect.

thanks for your help.

Numeric Format Using Sql Server 2000 Query

I need to Format the number 123.745817 to 123.75 by using Sql Server 2000 Query

Quote:

Originally Posted by hisham123

I need to Format the number 123.745817 to 123.75 by using Sql Server 2000 Query

select convert(decimal(5,2),123.745817 ,0)|||

Quote:

Originally Posted by Vidhura

select convert(decimal(5,2),123.745817 ,0)

For those who don't understand the syntax, read below:

decimal[(p[, s])] and numeric[(p[, s])]

Fixed precision and scale numbers. When maximum precision is used, valid values are from - 10^38 +1 through 10^38 - 1. The SQL-92 synonyms for decimal are dec and dec(p, s).

p (precision)

Specifies the maximum total number of decimal digits that can be stored, both to the left and to the right of the decimal point. The precision must be a value from 1 through the maximum precision. The maximum precision is 38. The default precision is 18.

s (scale)

Specifies the maximum number of decimal digits that can be stored to the right of the decimal point. Scale must be a value from 0 through p. Scale can be specified only if precision is specified. The default scale is 0; therefore, 0 <= s <= p. Maximum storage sizes vary, based on the precision.

taken from here:

http://msdn2.microsoft.com/en-us/li...32(SQL.80).aspx

numerate group

I would want to number the groups of reportviewer

es.

1) Articles

2) categories

-

etc you can help me

Have a global count variable in your report code and write a function to increment that variable and return the same variable. Call the function from your textbox expression. A sample looks like this:

Public GroupNumber As Integer = 0

Public Function IncrementCount() As String

GroupNumber = GroupNumber + 1

IncrementCount = CStr(GroupNumber)

End Function

And in any of your textbox in your grroup header, use an expression something like this:

=Code.IncrementCount()

Shyam

|||Thanks for help|||Can you please mark the post as answer?

Numer formatting question

Hi,
I have a number formating question.
If the value is less than 0, I would like the formatting to look like
(12,345).
If the value is greater than 0, I would like the formatting to look like
12,345.
If the value is equal to 0, I would like a - to be displayed.
I tried =iif( Fields!SPECIALTY_LEASING.Value=0,"-",iif(
Fields!SPECIALTY_LEASING.Value <0,"(0,0)","N0")) but I get a - every time.
Thanks,
MelissaMelissa,
Try the format string #,##0;(#,##0);\-
You must have the backslash character '\' before the dash '-' character. If
you want more than one dash you would have to have \-\-\- in the third part
of the format string. Or, if you want the word Zero you'd have to use
\Z\e\r\o.
HTH,
magendo_man
Stirling, Scotland
"Me" wrote:
> Hi,
> I have a number formating question.
> If the value is less than 0, I would like the formatting to look like
> (12,345).
> If the value is greater than 0, I would like the formatting to look like
> 12,345.
> If the value is equal to 0, I would like a - to be displayed.
> I tried =iif( Fields!SPECIALTY_LEASING.Value=0,"-",iif(
> Fields!SPECIALTY_LEASING.Value <0,"(0,0)","N0")) but I get a - every time.
> Thanks,
> Melissa
>|||That worked!! Thank you so much!
Melissa
"magendo_man" wrote:
> Melissa,
> Try the format string #,##0;(#,##0);\-
> You must have the backslash character '\' before the dash '-' character. If
> you want more than one dash you would have to have \-\-\- in the third part
> of the format string. Or, if you want the word Zero you'd have to use
> \Z\e\r\o.
> HTH,
> magendo_man
> Stirling, Scotland
>
> "Me" wrote:
> > Hi,
> >
> > I have a number formating question.
> >
> > If the value is less than 0, I would like the formatting to look like
> > (12,345).
> > If the value is greater than 0, I would like the formatting to look like
> > 12,345.
> > If the value is equal to 0, I would like a - to be displayed.
> >
> > I tried =iif( Fields!SPECIALTY_LEASING.Value=0,"-",iif(
> > Fields!SPECIALTY_LEASING.Value <0,"(0,0)","N0")) but I get a - every time.
> >
> > Thanks,
> > Melissa
> >

Numbering the SQL Query statements

Using the TOAD application for Oracle, I am able to number the SQL query lines for readability. Can the same be done for SQL Server 2005?

Hi,

I think you mean in SQL Server Management Studio.
This is possible by performing the following steps:

In SQL Server Management Studio go to Tools\Options|||

Yes... In Server Management Studio, choose Options from the menu bar.

Expand "Text Editor", Expand "All Languages" and click on General

Under "Display", check the "Line Numbers" box...

sql

numbering rows

How do i number the rows in the table? My data does not have any unique
values if that helpsThere is a RowNumber(<scope>) function, where <scope> is a string that
identifies a data region, dataset or grouping if you need that context. For
just a simple running row total, use RowNumber(Nothing). You can also use
it for visual effects, and the most frequent example of this is to create
"green bar" reports by setting the background colour of a table row with the
expression:
=iif(RowNumber(Nothing) Mod 2, "Green", "White")
Cheers, Mark
"Marvin" <Marvin@.discussions.microsoft.com> wrote in message
news:25E67CAB-76C0-450A-B4DE-87788DD19E80@.microsoft.com...
> How do i number the rows in the table? My data does not have any unique
> values if that helps

Numbering of records in a Group

I just started learnig CR.
1) I want to know how to serially number records which form part of a particular group. "Group Number" gives the group a num and "Record Number" gives a global num. I want each list in a group should have a distinct set of numbers.
Sorry if the question is very silly, I enquired couple of people but no help!

2) I also want to know how do we display a CR from VB and print it to deskjet printer.

Version info:
CR 9 & CR 4.2
VB6Write your own formulas to count them.
e.g. in group header
whileprintingrecords;
numbervar x := 0;

in detail:
whileprintingrecords;
numbervar x := x + 1;|||Like I said in the first instance Iam a novice in CR (that explains my username). Though you have stated to add some variables in group header and increment in detail section, I donot see any "code window" as in VB where I can add the code suggested. I would be happy if you could take some pains to explain me that as well.

Also let me know how to connect, open & print CR9 report through VB6, the database being Access 2003. The connection needs to be dynamic i.e. I should be able to manipulate the connxn programatically through VB6.|||Nobody has responded. Can moderators help me.|||right click on formula fields in field explorer, select new then code window for the formula will be opened. u need to do this in crystal desinger not in vb6 form designer.

Numbering Lines (Ranking)

I have a report that show all of are salesmen sorted by the number of sales they have. What I have been asked to do is to give them an actual ranking (Number 1, Number 2 etc...). Being fairly new to SQL I am not sure how to do this so here I am.

Thank you for any help that anyone can give me.

You can use RowNumber(Nothing). Check out the docs for more info.

|||Thank you for the help.

numbering in group

I want to add numbering in group in the reports.(number each row in the detail)

when I worked with access it was possible.

can I do it in the report services?

thanks!Ok I find it:

=rownumber("MyGropName")

thankssql

Numbering each row in my resultset

In my query I want to succesively number each record I retrieve from a table.
Example: select <...>, state from tbl_states (<...> is what I am asking for)
Col1 Col2
1 Ohio
2 New York
3 California
4 Texas
I have seen some solutions using count(*), group by and joins to achieve
this. However I have images that I retrieve in columns 2 (instead of the
states) and group by is not compliant with image datatype.
I think Oracle has something called rownum that does what I want, but I
don't know how to do this with SQL Server.
Anybody knows?
Why not add the row number client-side? That can work out much more
efficient than any of the potential SQL solutions. Here's one SQL
alternative:
SELECT
(SELECT COUNT(*)
FROM YourTable
WHERE key_col <= T.key_col) AS col1,
col2
FROM YourTable AS T
David Portas
SQL Server MVP
|||Why make the query treat each row individually? The client has to do that
anyway, so it is a much more appropriate place to keep a running count.
http://www.aspfaq.com/
(Reverse address to reply.)
"Billy" <Billy@.discussions.microsoft.com> wrote in message
news:D2BEB8E4-C710-4027-A1EF-1D8166A6DB10@.microsoft.com...
> In my query I want to succesively number each record I retrieve from a
table.
> Example: select <...>, state from tbl_states (<...> is what I am asking
for)
> Col1 Col2
> --
> 1 Ohio
> 2 New York
> 3 California
> 4 Texas
> I have seen some solutions using count(*), group by and joins to achieve
> this. However I have images that I retrieve in columns 2 (instead of the
> states) and group by is not compliant with image datatype.
> I think Oracle has something called rownum that does what I want, but I
> don't know how to do this with SQL Server.
> Anybody knows?
>

Numbering each row in my resultset

In my query I want to succesively number each record I retrieve from a table.
Example: select <...>, state from tbl_states (<...> is what I am asking for)
Col1 Col2
--
1 Ohio
2 New York
3 California
4 Texas
I have seen some solutions using count(*), group by and joins to achieve
this. However I have images that I retrieve in columns 2 (instead of the
states) and group by is not compliant with image datatype.
I think Oracle has something called rownum that does what I want, but I
don't know how to do this with SQL Server.
Anybody knows?Why not add the row number client-side? That can work out much more
efficient than any of the potential SQL solutions. Here's one SQL
alternative:
SELECT
(SELECT COUNT(*)
FROM YourTable
WHERE key_col <= T.key_col) AS col1,
col2
FROM YourTable AS T
--
David Portas
SQL Server MVP
--|||Why make the query treat each row individually? The client has to do that
anyway, so it is a much more appropriate place to keep a running count.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Billy" <Billy@.discussions.microsoft.com> wrote in message
news:D2BEB8E4-C710-4027-A1EF-1D8166A6DB10@.microsoft.com...
> In my query I want to succesively number each record I retrieve from a
table.
> Example: select <...>, state from tbl_states (<...> is what I am asking
for)
> Col1 Col2
> --
> 1 Ohio
> 2 New York
> 3 California
> 4 Texas
> I have seen some solutions using count(*), group by and joins to achieve
> this. However I have images that I retrieve in columns 2 (instead of the
> states) and group by is not compliant with image datatype.
> I think Oracle has something called rownum that does what I want, but I
> don't know how to do this with SQL Server.
> Anybody knows?
>

Numbering each row in my resultset

In my query I want to succesively number each record I retrieve from a table
.
Example: select <...>, state from tbl_states (<...> is what I am asking for
)
Col1 Col2
--
1 Ohio
2 New York
3 California
4 Texas
I have seen some solutions using count(*), group by and joins to achieve
this. However I have images that I retrieve in columns 2 (instead of the
states) and group by is not compliant with image datatype.
I think Oracle has something called rownum that does what I want, but I
don't know how to do this with SQL Server.
Anybody knows?Why not add the row number client-side? That can work out much more
efficient than any of the potential SQL solutions. Here's one SQL
alternative:
SELECT
(SELECT COUNT(*)
FROM YourTable
WHERE key_col <= T.key_col) AS col1,
col2
FROM YourTable AS T
David Portas
SQL Server MVP
--|||Why make the query treat each row individually? The client has to do that
anyway, so it is a much more appropriate place to keep a running count.
http://www.aspfaq.com/
(Reverse address to reply.)
"Billy" <Billy@.discussions.microsoft.com> wrote in message
news:D2BEB8E4-C710-4027-A1EF-1D8166A6DB10@.microsoft.com...
> In my query I want to succesively number each record I retrieve from a
table.
> Example: select <...>, state from tbl_states (<...> is what I am asking
for)
> Col1 Col2
> --
> 1 Ohio
> 2 New York
> 3 California
> 4 Texas
> I have seen some solutions using count(*), group by and joins to achieve
> this. However I have images that I retrieve in columns 2 (instead of the
> states) and group by is not compliant with image datatype.
> I think Oracle has something called rownum that does what I want, but I
> don't know how to do this with SQL Server.
> Anybody knows?
>

Numbering column with a start number of 109

Hi,
I have a table as follows:
StatId AgencyID Value
1 10
2 47
3 38
4 59
5 60
.. ..
All the fields in the StatId field is blank. However,
I have to fill up the field in statId starting from
109 with increment of 1 for each row. Altogether I have
about 10,000 row in the above agency table.
Any help as to how to proceed programmatically is highly
appreciated. Thanks in advance.
How will you determine what the order should be? That is, should the row
with AgencyID = 10 have a StatId of 109?
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:F4B0FBC9-1CFF-4E72-A572-1AA9273C5D29@.microsoft.com...
> Hi,
> I have a table as follows:
> StatId AgencyID Value
> 1 10
> 2 47
> 3 38
> 4 59
> 5 60
> .. ..
> All the fields in the StatId field is blank. However,
> I have to fill up the field in statId starting from
> 109 with increment of 1 for each row. Altogether I have
> about 10,000 row in the above agency table.
> Any help as to how to proceed programmatically is highly
> appreciated. Thanks in advance.
|||That's correct. Row with Agencyid = 10 will have a StatID of 109. Thanks.
"Adam Machanic" wrote:

> How will you determine what the order should be? That is, should the row
> with AgencyID = 10 have a StatId of 109?
>
> "Jack" <Jack@.discussions.microsoft.com> wrote in message
> news:F4B0FBC9-1CFF-4E72-A572-1AA9273C5D29@.microsoft.com...
>
>
|||What's the logic then? How will you programatically number these rows?
You'll probably have to write a loop to manually update the rows, one by
one, based on whatever logic you're ordering them by.
Or, you could try creating a new table with StatId INT IDENTITY(109, 1),
then insert the entire batch at once using INSERT SELECT, with an ORDER BY,
but there is no guarantee that the rows will show up in the right order. So
although you could try that, it may not work the way you want.
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:65435A6C-DB6B-4828-A4F4-754005BB31E7@.microsoft.com...[vbcol=seagreen]
> That's correct. Row with Agencyid = 10 will have a StatID of 109. Thanks.
> "Adam Machanic" wrote:
row[vbcol=seagreen]

Numbering column with a start number of 109

Hi,
I have a table as follows:
StatId AgencyID Value
1 10
2 47
3 38
4 59
5 60
.. ..
All the fields in the StatId field is blank. However,
I have to fill up the field in statId starting from
109 with increment of 1 for each row. Altogether I have
about 10,000 row in the above agency table.
Any help as to how to proceed programmatically is highly
appreciated. Thanks in advance.How will you determine what the order should be? That is, should the row
with AgencyID = 10 have a StatId of 109?
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:F4B0FBC9-1CFF-4E72-A572-1AA9273C5D29@.microsoft.com...
> Hi,
> I have a table as follows:
> StatId AgencyID Value
> 1 10
> 2 47
> 3 38
> 4 59
> 5 60
> .. ..
> All the fields in the StatId field is blank. However,
> I have to fill up the field in statId starting from
> 109 with increment of 1 for each row. Altogether I have
> about 10,000 row in the above agency table.
> Any help as to how to proceed programmatically is highly
> appreciated. Thanks in advance.|||What's the logic then? How will you programatically number these rows?
You'll probably have to write a loop to manually update the rows, one by
one, based on whatever logic you're ordering them by.
Or, you could try creating a new table with StatId INT IDENTITY(109, 1),
then insert the entire batch at once using INSERT SELECT, with an ORDER BY,
but there is no guarantee that the rows will show up in the right order. So
although you could try that, it may not work the way you want.
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:65435A6C-DB6B-4828-A4F4-754005BB31E7@.microsoft.com...
> That's correct. Row with Agencyid = 10 will have a StatID of 109. Thanks.
> "Adam Machanic" wrote:
> > How will you determine what the order should be? That is, should the
row
> > with AgencyID = 10 have a StatId of 109?
> >
> >
> > "Jack" <Jack@.discussions.microsoft.com> wrote in message
> > news:F4B0FBC9-1CFF-4E72-A572-1AA9273C5D29@.microsoft.com...
> > > Hi,
> > > I have a table as follows:
> > >
> > > StatId AgencyID Value
> > > 1 10
> > > 2 47
> > > 3 38
> > > 4 59
> > > 5 60
> > > .. ..
> > > All the fields in the StatId field is blank. However,
> > > I have to fill up the field in statId starting from
> > > 109 with increment of 1 for each row. Altogether I have
> > > about 10,000 row in the above agency table.
> > >
> > > Any help as to how to proceed programmatically is highly
> > > appreciated. Thanks in advance.
> >
> >
> >

Numbering column with a start number of 109

Hi,
I have a table as follows:
StatId AgencyID Value
1 10
2 47
3 38
4 59
5 60
. ..
All the fields in the StatId field is blank. However,
I have to fill up the field in statId starting from
109 with increment of 1 for each row. Altogether I have
about 10,000 row in the above agency table.
Any help as to how to proceed programmatically is highly
appreciated. Thanks in advance.How will you determine what the order should be? That is, should the row
with AgencyID = 10 have a StatId of 109?
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:F4B0FBC9-1CFF-4E72-A572-1AA9273C5D29@.microsoft.com...
> Hi,
> I have a table as follows:
> StatId AgencyID Value
> 1 10
> 2 47
> 3 38
> 4 59
> 5 60
> .. ..
> All the fields in the StatId field is blank. However,
> I have to fill up the field in statId starting from
> 109 with increment of 1 for each row. Altogether I have
> about 10,000 row in the above agency table.
> Any help as to how to proceed programmatically is highly
> appreciated. Thanks in advance.|||That's correct. Row with Agencyid = 10 will have a StatID of 109. Thanks.
"Adam Machanic" wrote:

> How will you determine what the order should be? That is, should the row
> with AgencyID = 10 have a StatId of 109?
>
> "Jack" <Jack@.discussions.microsoft.com> wrote in message
> news:F4B0FBC9-1CFF-4E72-A572-1AA9273C5D29@.microsoft.com...
>
>|||What's the logic then? How will you programatically number these rows?
You'll probably have to write a loop to manually update the rows, one by
one, based on whatever logic you're ordering them by.
Or, you could try creating a new table with StatId INT IDENTITY(109, 1),
then insert the entire batch at once using INSERT SELECT, with an ORDER BY,
but there is no guarantee that the rows will show up in the right order. So
although you could try that, it may not work the way you want.
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:65435A6C-DB6B-4828-A4F4-754005BB31E7@.microsoft.com...[vbcol=seagreen]
> That's correct. Row with Agencyid = 10 will have a StatID of 109. Thanks.
> "Adam Machanic" wrote:
>
row[vbcol=seagreen]

numbering a query

Is there a quick way to number a query?

ie I want to number the records returned by a recordset consecutively.

This seems like it should be simple but I haven't figured out how to do it
yet.

Any help is appreciated!

TIA

Carter"me" <me@.work.com> wrote in message
news:10hn03h10dtja5a@.corp.supernews.com...
> Is there a quick way to number a query?
> ie I want to number the records returned by a recordset consecutively.
> This seems like it should be simple but I haven't figured out how to do it
> yet.
> Any help is appreciated!
> TIA
> Carter

http://www.aspfaq.com/show.asp?id=2427

Simon|||Thanks much I'll take a look

"Simon Hayes" <sql@.hayes.ch> wrote in message
news:411b9a40$1_3@.news.bluewin.ch...
> "me" <me@.work.com> wrote in message
> news:10hn03h10dtja5a@.corp.supernews.com...
> > Is there a quick way to number a query?
> > ie I want to number the records returned by a recordset consecutively.
> > This seems like it should be simple but I haven't figured out how to do
it
> > yet.
> > Any help is appreciated!
> > TIA
> > Carter
> http://www.aspfaq.com/show.asp?id=2427
> Simon

Numbering

I have a table with Employees, and the month and year they started. I need to number them in order of the month and year they started. Example: July 2001 is employee 1 and August 2001 is employee 2. However there may be multiple employees in a given month and year. It seems like this should be a simple task but I can not figure it out. I have a query that puts them in the right order but not sure how to update the ID with the correct number. I'm using Microsoft Access 2002.

Thanks

TheAceManPlease post that query. Also, what are the data types for month and year?sql

Number/size of packets at a network level for a MSSQL query Vs oracle vs DB2.

Hello all,
I will try to supply all of the information available to me to help generate some answers to the question I have. I am doing some benchmark testing of an application of how it deals with various database queries, for example oracle 10g, DB2 and MSSQL 2005.
I have a very simple database called "student" that has various tables, with various columns, all as mentioned very simplistic. Each table has a few hundred rows of data, the most being one table with 2000 rows. The same database (structure and dataset), is replicated across all 3 DB's.
In order to do the testing, I have been recording the same queries (select * from table_a, select * from table_b etc etc) for each database at an interface which monitors traffic between the client and db server, the problem starts here; The size of the tcpdumps are vastly greater for MSSQL vs oracle or db2. Some examples:

100 query or transaction dump:
mssql=3.7mb
oracl10g=133kb

50,000 query or transaction dump:
mssql=1.8gb
oracl10g=0.6gb
db2=0.6gb

as mentioned each database's tables and data are identical to my knowledge and all queries are the same. The bloated dumps are making mssql performance numbers look bad.

So my question is: What could be the reason for MSSQLs client/server queries to contain so much information, has anyone else come across this? Is there any setting or something I could try to minimize it?

Thank you for your time.

Hello,

I'd be interested to know how you are issuing the statements. Are you issuing them in a uniform manner (i.e - all via ADO .net using OLEDB), or are you just executing them from the DB's respective client tool apps?

Cheers,

Rob

|||I have tried two methods for executing the queries:

1) In house java app that just uses a mssql driver to connect to the db, retrieves queries from a text file and executes them, can make setting adjustments like, ms between queries, re-connect after each query etc etc, I have made sure we do not reconnect and have everything as minimal as possible.

2) Using widely available management consoles, for example MSSQL server management studio express and oracle enterprise management console.

So I have used at least two different methods to execute the query, both methods have the same results, the mssql dump is larger.

Thanks for your reply so far.

Number/enumerate rows in a table from scratch?

I have a table with 10 rows - one int column and 3 varchar cols. There is n
o
unique data. How can I number/enumerate the rows from say 1 to 10 with Tsq
l?
create table tbl1(
RowNum int,
fld1 varchar(5),
fld2 varchar(5),
fld3 varchar(5))
Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
Thanks,
RichConsider making the RowNum column an identity.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:DAD8E08B-207D-4EBF-BF4F-A8273B1D3536@.microsoft.com...
I have a table with 10 rows - one int column and 3 varchar cols. There is
no
unique data. How can I number/enumerate the rows from say 1 to 10 with
Tsql?
create table tbl1(
RowNum int,
fld1 varchar(5),
fld2 varchar(5),
fld3 varchar(5))
Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
Thanks,
Rich|||Thanks. I thought about that. But I was just wondering - based on my
criteria, if it would be possible to enumerate a table with Tsql - like mayb
e
using a cursor? For example, in VBA you could use DAO code to enumerate a
table:
Set RS = DB.OpenRecordset("tbl1")
Do While Not RS.EOF
RS.Edit
RS!RowNum = i
RS.Update
i = i + 1
RS.MoveNext
Loop
This is kind of like a cursor except that a cursor seems to require
something unique. I was thinking in pseudocode Update tbl1 set top 1 Rownum
= 1. Then use a self join and set next row to max(Rownum) + 1. But how do
I
determine the next row with Tsql in my scenario?
"Tom Moreau" wrote:

> Consider making the RowNum column an identity.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:DAD8E08B-207D-4EBF-BF4F-A8273B1D3536@.microsoft.com...
> I have a table with 10 rows - one int column and 3 varchar cols. There is
> no
> unique data. How can I number/enumerate the rows from say 1 to 10 with
> Tsql?
> create table tbl1(
> RowNum int,
> fld1 varchar(5),
> fld2 varchar(5),
> fld3 varchar(5))
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Thanks,
> Rich
>|||If this is a useful table in your system, you must remove the duplicates and
explicitly assign a primary key for data integrity purposes. For details
refer to:
http://support.microsoft.com/defaul...b;EN-US;q139444
Once you have unique rows, ranking becomes much simpler. For instance see:
http://support.microsoft.com/defaul...b;EN-US;q186133
Though it makes little sense, with you current table schema with no keys,
you can simply add the "pseudo rank" using an identity column.
Alternatively, you can use a rank like:
ALTER TABLE tbl1 ADD idCol INT NOT NULL IDENTITY
GO
SELECT ( SELECT COUNT( * )
FROM tbl1 t2
WHERE t2.fld1 = t1.fld1
AND t2.fld2 = t1.fld2
AND t2.fld3 = t1.fld3
AND t2.idCol <= t1.idCol ),
t1.fld1, t1.fld2, t1.fld3
FROM tbl1 t1 ;
GO
ALTER TABLE tbl1 DROP COLUMN idCol
GO
SELECT * FROM tbl1
Another approach is to use a table of sequentially incrementing numbers. You
can create one like :
SELECT IDENTITY( INT ) "n" INTO Nbrs FROM sysobjects s1, sysobjects s2 ;
Now you can do:
SELECT Nbrs.n, fld1, fld2, fld3
FROM ( SELECT fld1, fld2, fld3, COUNT( * )
FROM tbl1
GROUP BY fld1, fld2, fld3 ) D ( fld1, fld2, fld3, n )
INNER JOIN Nbrs
ON D.n >= Nbrs.n ;
Another way of doing this would be like:
SELECT n, fld1, fld2, fld3
FROM tbl1, Nbrs
GROUP BY fld1, fld2, fld3, n
HAVING n <= COUNT(*) ;
Anith|||Without something to provide uniqueness, you're stuck.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:57F251E0-A033-4047-9502-A9A5415814B2@.microsoft.com...
Thanks. I thought about that. But I was just wondering - based on my
criteria, if it would be possible to enumerate a table with Tsql - like
maybe
using a cursor? For example, in VBA you could use DAO code to enumerate a
table:
Set RS = DB.OpenRecordset("tbl1")
Do While Not RS.EOF
RS.Edit
RS!RowNum = i
RS.Update
i = i + 1
RS.MoveNext
Loop
This is kind of like a cursor except that a cursor seems to require
something unique. I was thinking in pseudocode Update tbl1 set top 1 Rownum
= 1. Then use a self join and set next row to max(Rownum) + 1. But how do
I
determine the next row with Tsql in my scenario?
"Tom Moreau" wrote:

> Consider making the RowNum column an identity.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:DAD8E08B-207D-4EBF-BF4F-A8273B1D3536@.microsoft.com...
> I have a table with 10 rows - one int column and 3 varchar cols. There is
> no
> unique data. How can I number/enumerate the rows from say 1 to 10 with
> Tsql?
> create table tbl1(
> RowNum int,
> fld1 varchar(5),
> fld2 varchar(5),
> fld3 varchar(5))
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Thanks,
> Rich
>|||Thank you all for your replies and suggestions. These have really helped me
to understand about uniqueness and numbering. Actually, I sort of lost sigh
t
of why I was pursuing this, but I realized that with the VBA DAO each row in
an MS Access table, for example has a unique binary row identifier which is
now exposed for usage. But DAO uses it to movenext. So I can see that ther
e
is no way to movenext without some unique Identifier in a Sql Table.
Thanks all for your help.
Rich
"Rich" wrote:

> I have a table with 10 rows - one int column and 3 varchar cols. There is
no
> unique data. How can I number/enumerate the rows from say 1 to 10 with T
sql?
> create table tbl1(
> RowNum int,
> fld1 varchar(5),
> fld2 varchar(5),
> fld3 varchar(5))
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Thanks,
> Rich

Number to words

hi
I want to know how convert number to words or words to number
for example. if i give input 100, i have to get the output "one hundred"
is there any built in function available. need solution immediately.
regards
nlakkaRun the attached script in PUBS database and see if that's what you want. I didn't have enough time to debug it, so if you find anything fishy, - just let me know.|||OK, it has bugs, so hang on...|||Alright, now it's good to go (I think). A little roughy, but good enough for check generation and similar stuff.|||I have tried running the script but it should even translate the cents and not
e.g. 50/100.

Good luck|||I have tried running the script but it should even translate the cents and not
e.g. 50/100.

Good luck
Hey, Why don't you try this link (http://sqlkit.com/blogs/rudra/archive/2006/04/10/104.aspx) ? Just make two part according to the position of the '.' decimal ,and add regional customization according to your choice.
I will add that functionality very soon ...
And use this logic to make it ...

set @.s='2344.34'
set @.part1=substring(@.s,1,(len(@.s)-3))
set @.part2=substring(@.s,(len(@.s)-1),2)

and pass it to the function. Hope u will do that easily ;)