generating auto sequential numbers

I am using sybase aSE12.5. I have a table with only one column with
datatype char(2). when i query this table to select all the records, i
should get these records in the ascending order and they should be numbered
, i.e, the o/p should look something like this
column_name
-- ---
1 AB
2 AC
3 AD
and so on.

I cannot add an extra column and i need this to be done in a single query.

--
Message posted via http://www.sqlmonster.comPraveen D'Souza via SQLMonster.com (forum@.nospam.SQLMonster.com) writes:
> I am using sybase aSE12.5. I have a table with only one column with
> datatype char(2). when i query this table to select all the records, i
> should get these records in the ascending order and they should be
> numbered , i.e, the o/p should look something like this
> column_name
> -- ---
> 1 AB
> 2 AC
> 3 AD
> and so on.
> I cannot add an extra column and i need this to be done in a single query.

If you are using Sybase, you should be posting to
comp.databases.sybase, or whatever that groups goes by at SQLMonster.

However, this solution should work on Sybase as well:

SELECT col, (SELECT COUNT(*) FROM tbl b WHERE b.col >= a.col)
FROM tbl a
ORDER BY col

For large amount of data this may not be very performant, though.
SQL 2005, currently in beta, offers a ROW_NUMBER() function to
handle this a little more effeciently. I don't know if Sybase offers
something similar.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||In a tiered architecture display is done in the front end and not the
database.

Generate BCP data file with money columns using C#?

I need to use C# to generate the data files for BCP. Which C# data type I
should use to generate the data columns for SQL money money?
' money;
FileStream fs = new FileStream("test.txt", FileMode.CreateNew);
BinaryWriter bw = new BinaryWriter(fs);
....
bw.Write(money);
....
bw.Close();
fs.Close();nick (nick@.discussions.microsoft.com) writes:
> I need to use C# to generate the data files for BCP. Which C# data type I
> should use to generate the data columns for SQL money money?
> ' money;
> FileStream fs = new FileStream("test.txt", >
FileMode.CreateNew);
> BinaryWriter bw = new BinaryWriter(fs);
> .....
> bw.Write(money);
> .....
> bw.Close();
> fs.Close();
That depends on how you intend to run BCP. BCP can handle both text
formats and binary formats.
But assuming that you intend to use text file - that is after all the
most common - you should write the value as in -1212.2345. That is, you
should not use any currency character or thousands separator, and you should
force the decimal delimiter to be point, and not let regional settings
affect the output.
As for which data type you should use in C#, this may not be the best
newsgroup to ask in. Again, it depends on your exact needs, but you
probably should use an exact data type, that is not double.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||How about binary format? I think it may use less space and thus faster for
very big file? I know float works but it may not be good for money type...
"Erland Sommarskog" wrote:

> nick (nick@.discussions.microsoft.com) writes:
> FileMode.CreateNew);
> That depends on how you intend to run BCP. BCP can handle both text
> formats and binary formats.
> But assuming that you intend to use text file - that is after all the
> most common - you should write the value as in -1212.2345. That is, you
> should not use any currency character or thousands separator, and you shou
ld
> force the decimal delimiter to be point, and not let regional settings
> affect the output.
> As for which data type you should use in C#, this may not be the best
> newsgroup to ask in. Again, it depends on your exact needs, but you
> probably should use an exact data type, that is not double.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||nick (nick@.discussions.microsoft.com) writes:
> How about binary format? I think it may use less space and thus faster
> for very big file? I know float works but it may not be good for money
> type...
But binary format is more difficult to work with, as these are essentially
the native formats of SQL Server. Maybe money is a simple as a 64-bit
integer value with an implied decimal point. That's the type used in the
OLE DB interface, but whether that actually represents the actual storage
in SQL Server I don't know.
Of course, you compose an example table, and then bulk out that data in
native format and study it in a hex editor.
I strongly recommend that you start with a text file. If you really
need to trim performance later on, then you could look into to using
a binary format.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx