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));
       
      Calendar:
      LOAD DISTINCT
         Year(Temp_Date) As Year,
         Month(Temp_Date) As Month,
         Year(Temp_Date) * 100 + Month(Temp_Date) As [Period]
      ; 
      LOAD DISTINCT
         MonthStart($(vMinDate) + IterNo() - 1) As Temp_Date
      AUTOGENERATE (1)
      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.

       

      Thoughts?