left join inside a for..loop statement problem.

[#1]

Hi people,

I have a table and I am doing a left join to this table using a composite key.

The second table is in a loop which loops through each month of the year.  The problem is that when i perform the left join it only joins the 1st month to the table I want to join to.  eg the table ranges from Jan-10 to Dec-10 but only Jan-10 data gets added to the table previous table.

To summarise i load all data from Table A, I then have a load statement within a loop.  The load statement left joins to table A.  But the join only works for the first month in the loop.  Anyone have any ideas why this would not work for all the months?

Thanks in advance.

[jonathandienst]

Hi

You cannot do the join in this way. What you need to do is build the right hand table of the join in the loop, and then perform the join on the complete right hand table after the loop.

The reason that it does not work is that the fields that you are adding with the join already exist in the table on the second and subsequent passes through the loop, which means that QV is including the new fields in your composite key.

Regards

Jonathan