Help with SQL Command

Help with SQL Command

Post by Jeremy Par » Sat, 14 Mar 1998 04:00:00



Hi All.
I need to run a query that generates a result set with consecutive numbers
between x and y.
eg.
FieldName
-200
-199
-198
...
+200
+201
etc etc.

X will usually be a negative number. eg  -200 to + 200 steping by 1.
I will then use this as a reference list to total up all matching items in a
second table, linked by the value. So it's similar to a crosstab query, but
I need to have records for every number in the range, not just the matching
numbers.
I thought of manually populating a table with say -1000 to + 1000 but my
ranges could be huge and I don't want to have thousands of records sitting
in a table permanently, just to be used as a reference list.
There must be a way.

 
 
 

Help with SQL Command

Post by MIA » Sat, 14 Mar 1998 04:00:00


You will probably need to generate a table with the values in, although you
could make this a temporary table if required.

You could also half the amount of records needed using:

SELECT FieldName AS Value FROM MyTable
UNION
SELECT -1*FieldName AS Value FROM MyTable

This would mean that you only need the positive numbers (or just the
negative numbers - it doesn't make any difference).

If you also need a zero, you would have to say:

SELECT FieldName AS Value FROM MyTable
UNION
( SELECT 0 AS Value FROM MyTable
UNION
SELECT -1*FieldName AS Value FROM MyTable)

Hope this helps,

Matthew Campbell



Quote:> Hi All.
> I need to run a query that generates a result set with consecutive
numbers
> between x and y.
> eg.
> FieldName
> -200
> -199
> -198
> ...
> +200
> +201
> etc etc.