×
The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.
On 04 Sep 2013 14:37, Alan Shore wrote:
<<SNIP>> Still not too sure about the numbers table and creating one
<<SNIP>>
A recursive query [as shown in an earlier reply by Tom] is probably
the best way to generate the data in a derived table, but on an older
release like v5r3, I would likely use the OS database to generate the
table with defaults for the minimal number of required rows, and then
generate the data from that file using the SQL and finally select the
specific data of interest [much like the query in the earlier reply by
Charles]:
crtpf qtemp/numtbl rcdlen(1)
inzpfm qtemp/numtbl totrcds(99)
with
nt (num) as (select rrn(numtbl) from numtbl)
select cast('P' as char( 1 )) as FieldA
, cast(b.num as dec(2, 0)) as FieldB
, cast(c.num as dec(1, 0)) as FieldC
, cast(d.num as dec(3, 0)) as FieldD
from nt b cross join nt c cross join nt d
where b.num between 1 and 99
and c.num between 1 and 07
and d.num between 1 and 07
order by fieldb, fieldc, fieldd /* to match OP sample */
Note: the specific numeric data type was not shown in the OP, so
DECIMAL was chosen in each CAST to the noted precision\scale for the query.
midrange.com
