The following X++ code example shows a join of three tables on an insert_recordset statement that has a sub-select.
Also, a while select statement with a similar join is shown.
A variable is used to supply the inserted value for one column. The str variable must be declared with a length that is less than or equal to the maximum length of the corresponding database field.
static void InsertJoin42Job(Args _args)
{
GmTabDepartment tabDept2;
GmTabEmployee tabEmpl3;
GmTabProject tabProj4;
GmTabEmployeeProject tabEmplProj5;
str 64 sDescriptionVariable = "From variable.";
;
DELETE_FROM tabEmplProj5;
INSERT_RECORDSET tabEmplProj5
(
Description
, EmployeeRecId
, ProjectRecId
)
Select
sDescriptionVariable
, RecId
from
tabEmpl3
join
tabDept2
where tabEmpl3 .DepartmentGuid == tabDept2 .DepartmentGuid
join RecId
from tabProj4
where tabDept2 .DepartmentGuid == tabProj4 .DepartmentGuid
;
info(int642str(tabEmplProj5 .rowCount())
+ " ==Number of rows inserted.");
WHILE SELECT *
from
tabEmplProj5
join tabEmpl3
where tabEmplProj5 .EmployeeRecId == tabEmpl3 .RecId
join tabProj4
where tabEmplProj5 .ProjectRecId == tabProj4 .RecId
{
info(
tabEmpl3 .EmployeeName
+ " --works on-- "
+ tabProj4 .ProjectName
+ " (" + tabEmplProj5 .Description + ")."
);
}
/***************** Actual Infolog output
Message (01:05:41 pm)
4 ==Number of rows inserted.
Alice --works on-- Project ZZZ (From variable.).
Alice --works on-- Project YY (From variable.).
Beth --works on-- Project ZZZ (From variable.).
Beth --works on-- Project YY (From variable.).
*****************/
}