Using Outer Joins

The following PeopleCode query uses outer joins:

   Local ApiObject &aQuery, &aQrySelCol;
   Local ApiObject &aQryRcd, &aQryRcd2;
   Local ApiObject &aQryFld, &aQryFld2;
   Local ApiObject &aQrySel, &Criteria1;
   Local number &Rslt;
   &aQuery = %Session.GetQuery();
   &aQuery.Create("TEST1", False, %Query_Query, "PIA Test 1", ⇒
"Creating Test Query1 from PIA Page");
   &aQrySel = &aQuery.AddQuerySelect();
   &aQryRcd = &aQrySel.AddQueryRecord("JOB");
   &aQryRcd.RecordAlias = "A";
   &aQryRcd2 = &aQrySel.AddQueryRecord("PERSONAL_DATA");
   &aQryRcd2.RecordAlias = "B";
   &aQryRcd2.JoinType = %Query_JoinLeftOuter;
   &aQryRcd2.JoinAlias = "A";
   &aQryFld = &aQrySel.AddQuerySelectedField("JOB", "A", "EMPLID", "EMPLID");
   &aQryFld.ColumnNumber = 1;
   &aQryFld.HeadingType = %Query_HdgRftShort;
   &aQryFld2 = &aQrySel.AddQuerySelectedField("PERSONAL_DATA", "B", ⇒
"NAME", "NAME");
   &aQryFld2.ColumnNumber = 2;
   &aQryFld.HeadingType = %Query_HdgRftShort;
   &Criteria1 = &aQrySel.AddCriteria("JoinCriteria");
   &Criteria1.Logical = %Query_CombNotUsed;
   &Criteria1.Expr1Type = %Query_ExprField;
   &Criteria1.AddExpr1Field("A", "EMPLID");
   &Criteria1.Operator = %Query_CondEqual;
   &Criteria1.Expr2Type = %Query_ExprField;
   &Criteria1.AddExpr2Field1("B", "EMPLID");
   &Criteria1.OJAlias = "B";
   &Rslt = &aQuery.Save();

The above PeopleCode program produces the following SQL:

SELECT A.EMPLID, B.NAME 
FROM (PS_JOB A LEFT OUTER JOIN PS_PERSONAL_DATA B ON A.EMPLID=B.EMPLID)