Andrew Jensen ha scritto:
Do you mean something similar to the ROWNUM column in Oracle?
There is nothing analogous pseudo column in HSQLDB or MySQL either, I
don't know about PostgreSQL.
"NEXT VALUE FOR" that you point out is used to retrieve the next value
from a sequence generator. One could use it to sequentially number the
rows in a result set.
The question would be, do you need the numbers to start over each time
the result set is fetched? If the answer is yes, then under HSQLDB I
would say you are out of luck. Under say MySQL 5.x you could do this
using a stored procedure, and you can then call this procedure from a
Base query.
Well, before I write off HSQLDB. You could use a OOo macro to do this,
*if* the result set is going to be used in code, or displayed in a
form. There would be no way to do it, that I can think of, so that you
could just open a Query definition from the Base window. Not to say
that you could not use NEXT VALUE FOR, you could but you would not
trigger the sequence to start over at 1. That is what the macro
wrapper would be used for.
I can show you easily, in an interactive mode.
Open the SQL window under Base and enter this command
CREATE SEQUENCE "sqRowNum" START WITH 1
Execute the command and close the window
Now open a new Query definition window - in SQL view. Then click on
the 'Run SQL Direct' toolbutton to turn off escape processing.
Enter a simple select statement for any table you have. My example
here is
SELECT "StudentID", NEXT VALUE FOR "sqRowNum" as "RowNum" FROM
"STUDENT" AS "STUDENT"
Execute the query.
Re-execute the query. See the numbers didn't change.
Now, re-open the SQL window and enter this command
ALTER SEQUENCE "sqRowNum" RESTART WITH 1
Execute this and close the window.
Go back to your query and run it again.
So, the macro would need do nothing more then issue the ALTER command,
followed by executing the query.
HTH
Drew
Franco Fornari wrote:
Hello,
is it possible to add a field to a select query, to number retrieved
rows in sequential order? I found an SQL command like "NEXT VALUE
FOR", that should be able to do this, but the explanation regarding
SELECT statements isn't clear for me.
Can someone help me, please?
Thanks in advance,
Franco
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
------------------------------------------------------------------------
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.405 / Virus Database: 268.11.7/438 - Release Date: 05/09/2006
Hi,
I'll do some trials to verify if I could use your examples for my needs.
However, I was just referring to HSQL, when mentioning NEXT VALUE FOR.
In HSQLDB User Guide, Chapter 2, under "Sequences", it's written that
NEXT VALUE FOR "...can be used for inserting and updating table rows.
*You can also use it in select statements*." There is also an example
but, as I wrote, it's not clear for me.
Many thanks and best regards,
Franco
