4 Replies Latest reply: Aug 26, 2013 3:17 PM by Aji Paul RSS

    Load question - Where clause in resident table

      Hi All,

      I have a special requirement to identify the previous order date for a set of orders.

       

      Here is a simple load script in which I am trying to simulate the scenario.

      For some reason I am getting a field not found error. Would some one please help me?

       

      [OLD_DRD]:
      load * inline
      [ACCID,ORDDT_OLD
      A123,5/1/2012
      A123,2/1/2013
      A123,3/1/2013
      A123,5/1/2013
      A990,9/2/2012
      A990,1/1/2013
      A990,7/1/2013
      A990,8/15/2013]
      ;

      [CHK_ORD]:
      load * inline
      [ACC_ID_N, ORD_NEW
      A123,4/30/2013
      A990,8/1/2013
      A990,6/1/2013
      ]
      ;

      left join (CHK_ORD)  load
      ACCID as ACC_ID_N,
      max(ORDDT_OLD) as LAST_ORD_DT
      resident OLD_DRD where Exists(ACC_ID_N , ACCID) and ORDDT_OLD < ORD_NEW;
      //or something like this
      //               where ACCID = ACC_ID_N and ORDDT_OLD < ORD_NEW;

      // Final Table will look like this
      // ACC_ID_N,  ORD_NEW,    LAST_ORD_DT
      // A123,      4/30/2013,  3/1/2013
      // A990,      8/1/2013,   7/1/2013
      // A990,      6/1/2013,   1/1/2013


      drop table OLD_ORD;



      Thanks,

      Aji Paul.