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.
