3 Replies Latest reply: Apr 15, 2016 9:36 AM by Luca V RSS

    JOIN in temporary table

    Luca V

      Hello,

      i have a problem with my script.

      I create the table "LAST_TIME" that contains 3 keys (IDDEVICE, IDPARK and IDFIELD) and extract the max DATETIME from the table GENERAL_INPUTS_DATA

       

      LAST_TIME:

      LOAD

      IDDEVICE,   IDPARK,   IDFIELD,

        MAX([DATETIME]) AS CURR_TIME

      RESIDENT GENERAL_INPUTS_DATA

      GROUP BY IDDEVICE, IDPARK, IDFIELD;


      Example of result


        

      IDPARKIDDEVICEIDFIELDCUR_TIME
      1a115/04/2016 12:00
      1b115/04/2016 11:00
      2c113/04/2016 06:00
      2d115/04/2016 12:00


       

      The table GENERAL_INPUT_DATA contains also data about ENERGY YELDED  and i would obtain the ENERGY producted today.

       

      LOAD   Max([ENERGY YELDED]) - Min([ENERGY YELDED]) as CURR_ENERGY_TODAY
      RESIDENT       GENERAL_INPUTS_DATA

      WHERE Year(DATETIME) = Year(Today(1))

      AND Month(DATETIME) = Month(Today(1))

      AND Day(DATETIME) = Day(Today(1))

      GROUP BY IDDEVICE, IDPARK, IDFIELD;

       

      Example of result

       

        

      IDPARKIDDEVICEIDFIELDENERGY YELDED
      1a150,00
      1b120,00
      2c10,00
      2d113,00

       

      How can i join the 2 table to obtain the next result ?

       

         

      IDPARKIDDEVICEIDFIELDCUR_TIMEENERGY YELDED
      1a115/04/2016 12:0050,00
      1b115/04/2016 11:0020,00
      2c113/04/2016 06:000,00
      2d115/04/2016 12:0013,00

       

       

      In MySql i would use a query like this but with "RESIDENT" i can't use ALIAS (in this example, calling GENERAL_INPUTS_DATA   G1  or G2  )

       

      select g1.IDDEVICE,   g1.IDPARK,

        MAX(g1.`DATETIME`) AS CURR_TIME,

      (

      select Max(g2.`ENERGY YELDED`) - Min(g2.`ENERGY YELDED`) as CURR_ENERGY_TODAY

      from `GENERAL INPUTS DATA` g2

      WHERE

      Year(g2.DATETIME) = Year(now(1)) AND Month(g2.DATETIME) = Month(now(1)) AND Day(g2.DATETIME) = Day(now(1))

      and g2.IDPARK = g1.IDPARK  and g1.IDDEVICE = g2.IDDEIVCE

      GROUP BY g2.IDDEVICE, g2.IDPARK

      )

       

       

      from `GENERAL INPUTS DATA` g1

      GROUP BY g1.IDDEVICE, g1.IDPARK;

        • Re: JOIN in temporary table
          Stefan Wühl

          Have you tried just joining your tables?

           

          LAST_TIME:

          LOAD

          IDDEVICE,   IDPARK,   IDFIELD,

            MAX([DATETIME]) AS CURR_TIME

          RESIDENT GENERAL_INPUTS_DATA

          GROUP BY IDDEVICE, IDPARK, IDFIELD;


          JOIN (LAST_TIME)

          LOAD   Max([ENERGY YELDED]) - Min([ENERGY YELDED]) as CURR_ENERGY_TODAY

          ,IDDEVICE, IDPARK, IDFIELD

          RESIDENT       GENERAL_INPUTS_DATA

          WHERE Year(DATETIME) = Year(Today(1))

          AND Month(DATETIME) = Month(Today(1))

          AND Day(DATETIME) = Day(Today(1))

          GROUP BY IDDEVICE, IDPARK, IDFIELD;

          • Re: JOIN in temporary table
            kushal chawda

            try this

             

            LAST_TIME:

            LOAD

            IDDEVICE,  IDPARK,  IDFIELD,

              MAX([DATETIME]) AS CURR_TIME

            RESIDENT GENERAL_INPUTS_DATA

            GROUP BY IDDEVICE, IDPARK, IDFIELD;

             

            join(LAST_TIME)

            LOAD  IDDEVICE, IDPARK, IDFIELD,

                        Max([ENERGY YELDED]) - Min([ENERGY YELDED]) as CURR_ENERGY_TODAY

            RESIDENT  GENERAL_INPUTS_DATA

            WHERE floor(DATETIME) = floor(today())

            GROUP BY IDDEVICE, IDPARK, IDFIELD;