Deleting records from a hashed file

[ASU_ETL_DEV]

Hello,

my requirement is to delete specific records from a hashed file. The file has been created in a project. The file's layout is as follows:

BUS_KEY
SRC_SYS_ID
CRC
CREATE_EW_DTTM

The first two columns are the key columns.
The first three columns are VARCHAR.
The fourth column is Timestamp.

I read some posts and I decided to go with the UV stage method. I created a job with a UV stage and a User-defined SQL:

Code: Select all

DELETE FROM #HASHED_FILE_NAME# WHERE CRC = 'D';

The job has the UV stage out link that goes to a Sequential File stage which would create a dummy file. When I run the job it executes the SQL statement correctly and it clears the targeted records from the file but then it aborts with the following message:

Sequential_File_77.IDENT1: |SQL statement has incorrect number of result columns.|


I then changed the design of the job, eliminated the Sequential File stage and brought in a Transformer stage. I linked the Transformer stage with an out link ot the UV stage and set up the Transformer as dummy stage.
I ran the job and the job did not abort but it apparently did not execute the SQL since the targeted records are still in the file.

What is it that I am doing wrong?
Thanks

[chulett]

Why does your job have any kind of "out link" on the UV stage? All you'd really need is something to feed the UV stage - typically a Transformer that's playing the role of Row Generator - and the UV stage with a single input link.

Me, I would pass a single row in with a single field having a value of 'D' and parameterize that in your sql.

Code: Select all

DELETE FROM #HASHED_FILE_NAME# WHERE CRC = ?;

[ASU_ETL_DEV]

I did not explain it correctly. I was referring to the 'out link' as the link that goes from the Row Generator Transformer to the UV stage. So, it would be the UV stage input link.

I will try the method you suggested, but can you see a reason why the SQL would not execute with my design?

[ASU_ETL_DEV]

Greg,
I coded it as you suggested and it worked.
Thank you for your help.

[chulett]

All columns defined in the stage must be bound into the sql, hence the methodology that I suggested.