Left outer join in ReportSmith 4.0

[RptSm]

I am trying to join the Employees tbl and the Title tbl. I need to include all from Emp tbl and only where the values equal or are null. Any tips are greatly appreciated.

 

[gmireles]

Hey there,
you can try this.
In the Link the tables box, where you link the employee file#'s for both tables there is a checkbox in the bottom where you can select "Include unmatched records". Did this before and it worked, I just had to play around with it a little. Hope it helps.

 

[RptSm]

Thanks....I just the true problem is the employee file # is not in the Title table. I will play around....

 

[gmireles]

I created the same report a while back. SInce I cant attach the rpt. file I will paste the SQL. Looks Messy but it works. Copy and paste this code into a new report that you create so that you can see how it works.


SELECT
"REPORTS"."V_EMPLOYEE"."FIRSTNAME" || ' ' || "REPORTS"."V_EMPLOYEE"."LASTNAME", "HRREPORT"."V_APPT_BASIC"."EMP_FILE_NB", "HRREPORT"."V_APPT_BASIC"."JOB_TITLE", "HRREPORT"."V_APPT_BASIC"."LOC_NAME", "REPORTS"."V_DEPARTMENT"."DESCRIPTION"
FROM
"REPORTS"."V_EMPLOYEE", "HRREPORT"."V_EMPL_CURRENT", "HRREPORT"."V_APPT_BASIC", "REPORTS"."V_DEPARTMENT"
WHERE
((("REPORTS"."V_EMPLOYEE"."STATUS" = 'A')))
AND
("REPORTS"."V_EMPLOYEE"."FILE#" = "HRREPORT"."V_APPT_BASIC"."EMP_FILE_NB" ) AND ("REPORTS"."V_EMPLOYEE"."HOMEDEPARTMENT" = "REPORTS"."V_DEPARTMENT"."DEPARTMENT" ) AND ("HRREPORT"."V_EMPL_CURRENT"."PERSON_ID" = "HRREPORT"."V_APPT_BASIC"."PERSON_ID" ) AND ("REPORTS"."V_DEPARTMENT"."COMPANYCODE" = "REPORTS"."V_EMPLOYEE"."COMPANYCODE" )
ORDER BY
"REPORTS"."V_EMPLOYEE"."FILE#

 

[RptSm]

Thanks I'll try it...

 

[CharlesCook]

I thought this info was in the JOBCODE_TBL table as Descr or DescrShort or DescrLong?

CharlesCook.com
ADP - PeopleSoft - SAP
ReportSmith - Crystal Reports - SQR - Query - Access
Reporting - Interfaces - Data Mining