Hi,
I have a table like this:
field1  val
1       A
3       B
4       C
I need a select to retun all consecutive rows (ordered by field1) from 1 to 5 also if does not exist in the table.
Result
field1  val
1       A
2       -   (this is the miss value)
3       B
4       C
5       -
The field1 column have no duplicate.
What the faster way?

Thanks!

Mirco,

<< field1  val
1       A
3       B
4       C
I need a select to retun all consecutive rows (ordered by field1) from 1 to 5 also if does not exist in the table.
Result
field1  val
1       A
2       -   (this is the miss value)
3       B
4       C
5       -
The field1 column have no duplicate. >>

Hmm, that's a tough one.  There's really no way to produce rows that don't already exist without using INSERT statements (or recursive SQL, which DBISAM doesn't support).  You might need to drop down to navigational mode in order to create a "Numbers" table that you can join against (LOJ) to produce the result that you want.

Tim Young
Elevate Software
www.elevatesoft.com