Left Join from different connections

JohnMcPhee — Wed, 13 May 2020 13:03:57 GMT

Hello.

I am having a strange(at least to me) issue with my recent application.

I have Table 1: (From Connection 1)

[RSC_MASTER]

LOAD RscMaster_No_In as rscmaster_no_in,
RscMaster_Name_Ch,
RscMaster_FName_Ch,
RscMaster_LName_Ch,
RscMaster_Address1_Vc,
RscMaster_City_Vc,
RscMaster_State_Ch,
RscMaster_Zip_Ch,
RscMaster_Login_Name_Ch;

[Resource_Master_Tbl]:
SELECT "RscMaster_No_In",
"RscMaster_Name_Ch",
"RscMaster_FName_Ch",
"RscMaster_LName_Ch",
"RscMaster_Address1_Vc",
"RscMaster_City_Vc",
"RscMaster_State_Ch",
"RscMaster_Zip_Ch",
"RscMaster_Login_Name_Ch"
FROM Resource_Master_Tbl"
where RscMaster_Name_Ch is not NULL;

Then I have Table 2 and 3: (From Connection 2)

[ACCRUALEDIT]:
SELECT
EFFECTIVEDATE
,ACCRUALCODEID
,[AMOUNT]/3600 as AMOUNT
,a.PERSONID
,ROW_NUMBER() OVER (PARTITION BY a.personId ORDER BY effectivedate DESC) AS rn
,PERSONNUM
FROM [WFC_PD].[dbo].[ACCRUALEDIT] a left join "WFC_PD".dbo.PERSON p on a.PERSONID=p.PERSONID
where EFFECTIVEDATE >'2020-01-01'
and ACCRUALCODEID = 13;

Set vlast_import=LEFFECTIVEDATE;

[LACCRUALEDIT]:
NoConcatenate
Load PERSONNUM AS RscMaster_Login_Name_Ch,
ACCRUALCODEID AS LACCRUALCODEID,
date(EFFECTIVEDATE) AS LEFFECTIVEDATE,
AMOUNT AS LAMOUNT,
PERSONID AS LPERSONID,
rn AS Lrn

Resident ACCRUALEDIT
WHERE rn = 1;

So basically I am connectING table 3 (LACCRUALEDIT) TO table 1(Resource_Master_Tbl") by aliasing PERSONNM in Table 3 to its matching value in RSC_Master which is RscMaster_Login_Name_Ch,

Now this does seem to work well when looking at the information from table 3. Meaning employee names returns all the appropriate LAMOUNT. The issue is I can a table with this information and a calculated field. This calculated field is summing up totals from Connection 1. If I select an individual employee all the information is accurate. If I select multiple the calculated fields return all 0.

Re: Left Join from different connections

dplr-rn — Wed, 13 May 2020 14:13:39 GMT

please share the expressions that you are using and maybe screenshots of the results and expected results

topic Re: Left Join from different connections in App Development

Left Join from different connections

Re: Left Join from different connections