8 Replies Latest reply: Feb 9, 2011 6:27 AM by jainave_08 RSS

    How to use group by clause in Load statement if Select statement has Inner join of different tables(more than 2)?

    jainave_08

      hey all,

      I am working on 3 different tables to form a temporary table and to load different fields to the table. When i am using the load statement with group by clause, it asks for aggregate function. i tried with aggregate functions of different fields and the error says invalid expression and the table is not loaded.

      for example

      tmptable:

      SQL select table1.id,table1.name,table2.tyid,table2.age,table3.location from table 1

      inner join table 2 on table1.id = table2.id

      inner join table3 on table2.tyid = table3.tyid

      where table1.name is not null

       

      load id as id

      count(typid) as numoftypeid¨

      age as age

      if(name ='abc',location) as currlocation

      resident tmptable

      group by id;

      drop tmptable;

      My problem is which field i should use the aggregate function . Because my data in the table comes with repetation of id with different field data.

       

      i need to combine all the fields in one row with the same id.

      can anyone help me with this?

       

      Thanks

      jai.