Uploaded image for project: 'WsSQL'
  1. WsSQL
  2. WSSQL-155

ECL generation error while joining more than 2 tables

    XMLWordPrintable

Details

    • Bug
    • Status: Resolved
    • Not specified
    • Resolution: Fixed
    • 6.2.x
    • None
    • ECLEngine
    • None

    Description

      Error while joining more than two tables.

      Error:
      --------
      W20170131-132839bsampathmyhthorWsSQL Job4failed0000eclccError2167 Unknown identifier "JndDS1JndDS2"stdin:2925eclccError3002 syntax error near "." : expected ENDstdin:3142eclccError3002 syntax error near "." : expected ENDstdin:3249eclccError3002 syntax error near "JndDS1JndDS2Filtered" : expected RANGE, ROWSET, SELF, SUCCESS, datarow, dataset, dictionary, module-name, identifier, identifier, function-name, identifier, macro-name, '', '(', '['stdin:3437eclccError3002 syntax error near "JndDS1JndDS2FilteredTable" : expected RANGE, ROWSET, SELF, SUCCESS, datarow, dataset, dictionary, module-name, identifier, identifier, function-name, identifier, macro-name, '', '(', '['stdin:3541eclccError3001 Too many errors (max = 5); Aborting...stdin:356660000000

      SQL:
      -------
      select a.*
      from
      thor::dept as a,
      thor::employee as b,
      thor::person as c
      where
      a.deptid=b.deptid and
      a.deptid=c.personid

      ECL:
      -------
      import std;

      TblDS0RecDef := RECORD
      unsigned1 deptid;
      string15 departmentname;
      END;
      TblDS0 := DATASET('~thor::dept', TblDS0RecDef, FLAT);

      TblDS1RecDef := RECORD
      unsigned1 empid;
      string15 firstname;
      string25 lastname;
      unsigned1 deptid;
      END;
      TblDS1 := DATASET('~thor::employee', TblDS1RecDef, FLAT);

      JndDS1 := JOIN( TblDS0, TblDS1, LEFT.deptid = RIGHT.deptid AND TRUE, INNER );

      TblDS2RecDef := RECORD
      unsigned1 personid;
      string15 firstname;
      string25 lastname;
      unsigned1 age;
      unsigned1 height;
      END;
      TblDS2 := DATASET('~thor::person', TblDS2RecDef, FLAT);

      JndDS2 := JOIN( JndDS1, TblDS2, LEFT.deptid = LEFT.deptid AND LEFT.deptid = RIGHT.personid, INNER );
      JndDS1JndDS2Filtered := JndDS1JndDS2( deptid = deptid AND deptid = personid );
      SelectStruct := RECORD
      unsigned1 deptid := JndDS1JndDS2Filtered.deptid;
      string15 departmentname := JndDS1JndDS2Filtered.departmentname;
      END;
      JndDS1JndDS2FilteredTable := TABLE( JndDS1JndDS2Filtered, SelectStruct );
      JndDS1JndDS2FilteredTableOut := CHOOSEN(JndDS1JndDS2FilteredTable,ALL);
      OUTPUT(JndDS1JndDS2FilteredTableOut, NAMED('WSSQLSelectQueryResult'), THOR);
      OUTPUT( COUNT(JndDS1JndDS2FilteredTableOut), NAMED('WSSQLSelectQueryResultCount'));

      /****************************************************
      Original SQL: "select a.* from thor::dept a, thor::employee b, thor::person c where a.deptid=b.deptid and a.deptid=c.personid"
      Normalized SQL: "SELECT thor::dept.deptid, thor::dept.departmentname FROM thor::dept, thor::employee INNER JOIN , thor::person INNER JOIN WHERE thor::dept.deptid = thor::employee.deptid AND thor::dept.deptid = thor::person.personid | -TC=myhthorUSER=bsampath-WUOWN=bsampath"
      ****************************************************/

      Attachments

        Activity

          People

            rpastrana Rodrigo Pastrana
            sampba01 Balaji
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: