Inserting Dummy Row at the end

siddharthkaushik · Post by **siddharthkaushik** » Mon Apr 05, 2004 3:10 pm

Hello All,

I have a situation where in I need a dummy row in my data set at the very end. I am getting this data set after a number of lookups, both database and hash.

The actual problem as I have already highlighted in the forum is to transpose rows into columns and in order to successfully do that I need a dummy row at the end of my data set.

The constraints I have is that I cannot make a union at the initial step and obtain this dummy row from the database as all the joins are supposed to be inner joins.

So it really boils down to adding this dummy row once I have all the data I need to transpose, and for that I need to determine when my data set is ending in the transformer and as soon as it ends I add a row. Is there any way that I can force this dummy row in?

Thanks,
Siddharth

ray.wurlod · Post by **ray.wurlod** » Mon Apr 05, 2004 5:11 pm

Create another job that generates the row in a before-job subroutine using ExecSH echo ",,,,,," > dummyfile which then processes that row from dummyfile into your target table.
Use a job sequence or job control to run your main job ahead of this simple job.

vmcburney · Post by **vmcburney** » Mon Apr 05, 2004 10:08 pm

You may need to put dummy values into your dummy row so your transformer constraints don't spit the dummy.

You can still create the dummy row using a Union statement, remember the second half of your union does not need to perform any joins at all, you can send back any values you want.

SELECT EMPLOYEE_ID, DATE, AMOUNT, PRODUCT_CODE
FROM EMPLOYEE, CUSTOMER, PRODUCT
WHERE ...
UNION 9999999, "12-DEC-2003", 0, "SED"

You just need to select dummy values that ensure the row makes it through to the transpose transformer. If you are performing lookups prior to the transpose just choose dummy values that have a matching lookup.

siddharthkaushik · Post by **siddharthkaushik** » Tue Apr 06, 2004 2:43 pm

Vincent,

This is exactly what I have done now. Thanks for the advice.
I had posted this query out of curiosity, if there is any way of synthesizing the dummy row in a Transformer Stage.

Thanks,
Siddharth

vmcburney · Post by **vmcburney** » Tue Apr 06, 2004 10:36 pm

No there is not. The difficulty is that a transformer cannot tell when you have arrived at the last row. Nor is there a mechanism for adding a row that didn't come from an input link. You could do a row count as a pre-processing step and pass it into the job as a parameter, your transformer can then use the input row count to determine when the last row has been reach. (A risky approach on a database table that is constantly being updated). You could populate a field on your database table that identifies any row that will be the last row for your processing but this starts to get a bit messy.

I can't think of anything easier or more robust then your union statement.

chulett · Post by **chulett** » Wed Apr 07, 2004 6:04 am

Which is why we've been pushing it as "the answer", Siddharth. While it would be great if there was some sort of automagic end-of-data detector built into the Transformers, there isn't. And there insn't a way to slip an extra row under the rug from inside one either.

Union. Union. Union! Dang, starting to sound like an old Sally Fields movie in here.

crincoli · Post by **crincoli** » Wed Jun 16, 2004 9:55 am

I'm new to DataStage. I have the same problem with sequential file, where I have to create one record at the end. Is it possible to resolve it creating another link with constraint "ENDOFDATA IS TRUE", create two files and combine them via a link collector?
I hope I have been clear, bye
Piero

ray.wurlod · Post by **ray.wurlod** » Wed Jun 16, 2004 4:06 pm

Nice thought. Have you tried it?

Given the limited range of collection algorithms currently supported, I have some reservations over whether it could be done this way.

The mechanism that a Transformer stage uses to detect end of data is not available to us within the Transformer, because that test is used to exit from the "get - put" loop before any other processing is done.

chucksmith · Post by **chucksmith** » Wed Jun 16, 2004 4:28 pm

I think the easiest solution is to create a second job that appends a single row to your output file. This could be as simple as a transform outputting to a sequential file. Use a stage variable as your constraint, and allow it to be true only for one row.

Example:

Initialize the stage variable to 0.

Set its derivation to be stage variable + 1

Set the constraint to stage variable <= 1

This will get you a single output row.

Remember to set the output action of your sequential stage to "append".

Chuck