3 Replies Latest reply: Feb 1, 2013 8:05 AM by Donald Hutchins RSS

    LOAD DISTINCT and Preceding Loads

      It seems that when LOAD DISTINCT is used in a preceding load statement, the subsequent load (above) sees all records, not just the distinct result set.  Do I misunderstand or am I missing something?


      For example, consider a very simple Caledar table like the following:


      LET vMinDate = Num(MakeDate(2012,01,01));
      LET vMaxDate = Num(MakeDate(2012,01,31));
         Year(Temp_Date) As Year,
         Month(Temp_Date) As Month,
         Year(Temp_Date) * 100 + Month(Temp_Date) As [Period]
         MonthStart($(vMinDate) + IterNo() - 1) As Temp_Date
      WHILE $(vMinDate) + IterNo() - 1 <= $(vMaxDate);


      So the LOAD DISTINCT at the bottom indeed results in just one date (Jan 1, 2012) despite that it iterated through 31 days to get that.


      But if I do not also include the DISTINCT predicate in the load on the top (highligted), I'll get 31 records in the calendar.


      Is this just something obvious that I've missed?  Seems once I LOAD DISTINCT, that resultset in the preceding load should be just the distinct records.  After all, if I were to create the first load, then load RESIDENT from that, I would not need to use DISTINCT for the second load.