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
help me with this issue
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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 will indicate that there are rows in TableA.
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
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.