Details
-
Bug
-
Status: Resolved
-
Not specified
-
Resolution: Fixed
-
6.2.x
-
None
-
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"
****************************************************/