10 Replies Latest reply: Nov 30, 2012 10:26 AM by Erik Furlanis RSS

    Trying to Concatenate Different WHERE statements from one table

      Hi everybody,

       

      I have a table which is my one and only source, let's  call it Table1.

       

      I need to load just certain values, and I figured out that, in order to do this, I have to use a concatenate join, as the inclusion criteria are too complex to be summarised in just one WHERE statement. A small sample below

       

      DataCountryOrderTypeChannel..
      ..DEReturnA
      ..DEOrderZ
      ..DEReturnA
      ..NLOrderZ
      ..NLOrderA
      ..NLReturnZ





       

      If I want, let say, a selection which includes the RETURN orders of DE customers Through Channel A and the Normal Orders of NL customers through Channel Z...

       

      This is impossible to resolve in one single WHERE statement, as some selections are excluding the remaining data.

       

      Thus I was thinking of doing a CONCATENATE LOAD, in this fashion:

       

      Temp:
      LOAD *;
      SQL SELECT * FROM SERVER.TABLE1;
      
      Temp1:
      LOAD *
      RESIDENT TEMP
      WHERE Country=DE AND OrderType=Return AND Channel=A;
      
      Temp2:
      CONCATENATE (Temp1)
      LOAD *
      RESIDENT TEMP
      WHERE Country=NL AND OrderType=Order AND Channel=Z;
      

       

      For some reasons, when reloaded it does not find the table right when concatenating.

       

      The error goes:

       

      Table not found
      concatenate (Temp1)
      LOAD *
      RESIDENT Temp
      

      It seems to block when it reaches table Temp2.

       

      Can please someone shed some light on this?

      Am I asking the moon?

      Thank you!