help me with this issue

[ragu]

Hello Everybody,

I have to do this job on a daily basis

I have Table A which is loaded with new data daily.

This job needs to check if there is data in Table A. If so then it needs to truncate Table B and load the new data from Table A.

If Table A is empty then nothing should be done to Table B

I tried using @INROWNUM and @OUTROWNUM and check if its greater then 0 as a constraint in the transformer and truncate then insert option.

If the Table is not empty then the truncate and loading is done properly. But if the Table is empty, it still truncates table B.

Is there any other way to make this work?

Thanks

Regards,
Ragunath

[ray.wurlod]

Use a job sequence.

Create a small job to SELECT COUNT(*) FROM TableA into a hashed file with a constant key value (say "TableA_Count"). You can use VOCLIB as the hashed file or create your own, but it should be created within the project or have a VOC pointer.

Create one job that truncates TableB and one that does not.

In the job sequence use a Nested Condition activity to determine which of these job to run. The decision is based on whether the count is 0 or non-zero. Use a TRANS() function to read the hashed file (that's why the hashed file must have a VOC pointer). An expression such as

Code: Select all

Trans("hashedfile", "TableA_Count", 1, "X") <> 0

will indicate that there are rows in TableA.