Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone,
I would like to create an Inner Join between table Fact_PickingAssignments: and table TimeRegistration:
The Inner Join is written in the table TimeRegistration and when I debug I get the Error: Ambiguous Column Name D_SK_Employee.
Can somenone see what I'm doing wrong here please? This is the first time I use a join and I have to say that table1(Fact_PickingAssignments) is a big table so I don't want to slow down performance.
Calendar:
Load *,
Date(DayStart([D_SK_Date_Start])) As [Date_Picking],
Year([D_SK_Date_Start]) As [Date_Year],
Month([D_SK_Date_Start]) As [Date_Month],
Week([D_SK_Date_Start]) As [Date_Week],
WeekDay([D_SK_Date_Start]) As [Week_Day],
'Q' & Ceil(Month(D_SK_Date_Start)/3,1,1) AS [Quarter],
Date(Monthstart([D_SK_Date_Start]),'MM-YYYY') As [Date_MonthYear];
//**************************************************************************************************************
// Table 1 *
//**************************************************************************************************************
Fact_PickingAssignments:
LOAD
[D_SK_PickingAssignment] AS [SK_PickingAss],
[BK_WarehouseCode] AS [Warehouse_Code],
[BK_PickingAssignmentID] AS [PA_Ass_ID],
Date(Date#([D_SK_Date_Start],'YYYYMMDD')) AS [D_SK_Date_Start],
Date(Date#([D_SK_Date_Start],'YYYYMMDD')) AS [PA_TR_StartDate],
Date(Date#([D_SK_Date_End],'YYYYMMDD')) AS [PA_EndDate],
Time#([D_SK_Time_Start],'hhmmss') AS [PA_StartTime],
Time#([D_SK_Time_End],'hhmmss') AS [PA_EndTime],
Interval([SecondsWorked] /86400) AS [PA_SecondsWorked],
[D_SK_Employee] AS [Emp_ID],
[FullPallets] AS [PA_FullPallets];
SQL SELECT
[D_SK_PickingAssignment],
[BK_WarehouseCode],
[BK_PickingAssignmentID],
[D_SK_Date_Start],
[D_SK_Date_End],
[D_SK_Time_Start],
[D_SK_Time_End],
[SecondsWorked],
[D_SK_Employee],
[FullPallets]
FROM NVL_DWH.dbo."DW_F_PickingAssignment";
//**************************************************************************************************************
// Table 2 *
//**************************************************************************************************************
TimeRegistration:
LOAD
[D_SK_TimeRegistration] as TR_ID,
[BK_DatabaseCodeGPS] as TR_BK_DatabaseCodeGPS,
[BK_EmployeeID] as TR_GPS_PersonnelID,
[BK_ClockIn] as TR_BK_ClokIn,
[BK_TimeRegistrationCode] as TR_CD_Register,
[TimeRegistrationSeconds] as TR_Num_Minutes,
[D_SK_Date_Planning] as TR_Date_PlanningGPS,
[D_SK_Date_In] as TR_DateClockIn,
[D_SK_Date_Out] as TR_DateClockOut,
[D_SK_Time_In] as TR_Clock_In,
[D_SK_Time_Out] as TR_Clock_Out,
[D_SK_Employee] as Emp_ID;
SQL SELECT
[D_SK_TimeRegistration],
[BK_DatabaseCodeGPS],
[BK_EmployeeID],
[BK_ClockIn],
[BK_TimeRegistrationCode],
[TimeRegistrationSeconds],
[D_SK_Date_Planning],
[D_SK_Date_In],
[D_SK_Date_Out],
[D_SK_Time_In],
[D_SK_Time_Out],
[D_SK_Employee]
FROM "NVL_DWH".dbo."DW_F_TimeRegistration" tr
Inner join "NVL_DWH".dbo.DW_F_PickingAssignment pa
on tr.D_SK_Employee = pa.D_SK_Employee
and pa.D_SK_Date_Start between tr.D_SK_Date_In and tr.D_SK_Date_Out
and pa.D_SK_Time_Start between tr.D_SK_Time_In and tr.D_SK_Time_Out
and tr.TimeRegistrationCode = 'Totaal';
//**************************************************************************************************************
// Dimension *
//**************************************************************************************************************
Employee:
LOAD
[D_SK_Employee] AS [Emp_ID],
Upper(Text([BK_UserName])) AS [Emp_Usys_UserName],
[GPS_UserName] AS [Emp_GPS_UserName],
[ID_Employee_GPS] AS [Emp_GPSPersonnelID],
[Active] AS [Emp_UsysActive];
SQL SELECT
[D_SK_Employee],
[BK_UserName],
[GPS_UserName],
[ID_Employee_GPS],
[Active]
FROM NVL_DWH.dbo."DW_D_Employee";
Kind regards,
Monique
May be you need to specify which table you are picking D_SK_EMPLOYEE from? In fact I would do this for all my fields just to be sure.
SQL SELECT
[D_SK_TimeRegistration],
[BK_DatabaseCodeGPS],
[BK_EmployeeID],
[BK_ClockIn],
[BK_TimeRegistrationCode],
[TimeRegistrationSeconds],
[D_SK_Date_Planning],
[D_SK_Date_In],
[D_SK_Date_Out],
[D_SK_Time_In],
[D_SK_Time_Out],
[pa.D_SK_Employee] or [tr.D_SK_Employee]
FROM "NVL_DWH".dbo."DW_F_TimeRegistration" tr
Inner join "NVL_DWH".dbo.DW_F_PickingAssignment pa
on tr.D_SK_Employee = pa.D_SK_Employee
and pa.D_SK_Date_Start between tr.D_SK_Date_In and tr.D_SK_Date_Out
and pa.D_SK_Time_Start between tr.D_SK_Time_In and tr.D_SK_Time_Out
and tr.TimeRegistrationCode = 'Totaal';
Try This
Fact_PickingAssignments:
Load * Table1;
TimeRegistration:
Load * Table2;
Left Join(TimeRegistration)
Employee:
LOAD
[D_SK_Employee] AS [Emp_ID],
Upper(Text([BK_UserName])) AS [Emp_Usys_UserName],
[GPS_UserName] AS [Emp_GPS_UserName],
[ID_Employee_GPS] AS [Emp_GPSPersonnelID],
[Active] AS [Emp_UsysActive];
SQL SELECT
[D_SK_Employee],
[BK_UserName],
[GPS_UserName],
[ID_Employee_GPS],
[Active]
FROM NVL_DWH.dbo."DW_D_Employee";
Where do I use the conditions?
Why a Left Join and not an Inner join? I want only those records who have a match in both tables.
May be you need to specify which table you are picking D_SK_EMPLOYEE from? In fact I would do this for all my fields just to be sure.
SQL SELECT
[D_SK_TimeRegistration],
[BK_DatabaseCodeGPS],
[BK_EmployeeID],
[BK_ClockIn],
[BK_TimeRegistrationCode],
[TimeRegistrationSeconds],
[D_SK_Date_Planning],
[D_SK_Date_In],
[D_SK_Date_Out],
[D_SK_Time_In],
[D_SK_Time_Out],
[pa.D_SK_Employee] or [tr.D_SK_Employee]
FROM "NVL_DWH".dbo."DW_F_TimeRegistration" tr
Inner join "NVL_DWH".dbo.DW_F_PickingAssignment pa
on tr.D_SK_Employee = pa.D_SK_Employee
and pa.D_SK_Date_Start between tr.D_SK_Date_In and tr.D_SK_Date_Out
and pa.D_SK_Time_Start between tr.D_SK_Time_In and tr.D_SK_Time_Out
and tr.TimeRegistrationCode = 'Totaal';
This is exactly what I wanted to do.
Thanks a million Sunny