Generating records in ascending sequence

[kumarjit]

Hi.

I have a source file with a single input column in it.
A sample input might look like:

Code: Select all

Col1
1
8
4
5
7
3
10

My requirement is to get a single column output(say Col2), with record values ranging between min(col1) and max(col1), and in a ascending order

Code: Select all

Col2
1
2
3
4
5
6
7
8
9
10

One approach that I implemented was:
Step 1: Get the max and min of col1
Step 2: Do a sparse lookup on an Oracle table with following query to generate the ascending record values:
SELECT R FROM ( SELECT LEVEL AS R FROM DUAL CONNECT BY LEVEL<=Orchestrate.max(col1)) WHERE R>=Orchestrate.min(col1)

But, I am seeking an alternative approach, something that does not employ a database interaction at all.

Can I be achieved?
Please suggest.

Warm Regards,
Kumarjit.

[chulett]

Transformer looping.

[ray.wurlod]

Am I missing something, or is this just about sorting?

[chulett]

It needs to be sorted and then the gaps filled in.

[kumarjit]

@Ray: As Craig has mentioned, the output would be a sorted column(col2), values of which will be continuous and range from min(col1) to max(col1).

@Craig: Looping does work good in this case where the input is already sorted in ascending order, but what if the input column values are staggered(like below), and I do not want to use any sort stage/inline sort feature to sort the column.

Code: Select all

Col1 
8
10
1
5
3
4
10
7

Any help?

Regards.

[ray.wurlod]

You will need to employ some kind of sorting, even if you are maintaining the sorted list in the Transformer loop.

It would be much easier to sort the source data so that your job can know the start and end points of the values that need to be interpolated.

[anbu]

Input --> Aggregator --> Transformer --> Output

- Get Min and Max from aggregator
- Use Max in Transformer looping and generate all the values

[chulett]

I do not want to use any sort stage/inline sort feature to sort the column.

Why the heck not?

As noted, you can ignore what was actually sent and just generate a sequence from min to max.

[ShaneMuir]

My requirement is to get a single column output(say Col2), with record values ranging between min(col1) and max(col1), and in a ascending order

I do not want to use any sort stage/inline sort feature to sort the column.

How would you ever hope to output values in ascending order on the output without using some form of sort?
If you input order is important to you in any way - just add a column which maintains the input order (eg use @INROWNUM) and then sort it again after you've calculated the missing numbers - though considering your output is just a sequence of numbers this is all quite a ridiculous requirement?

Or you've left a lot of requirements out.

[kumarjit]

Yes, anbu's solution worked, though the requirement was indeed ridiculous.

Regards,
Kumarjit.

[ray.wurlod]

<rant>Resist stupid requirements!</rant>