# E10 BAQ to SQL **URL:** \ **Category:** Epicor ERP 10\ **Tags:** baq\ **Created:** [February 11, 2022, 9:02pm UTC](https://www.epiusers.help/t/e10-baq-to-sql/87423 "2022-02-11T21:02:58Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![lairdl](https://www.epiusers.help/letter_avatar_proxy/v4/letter/l/ecb155/32.png) [@lairdl](https://www.epiusers.help/u/lairdl)\ **Post date:** [February 11, 2022, 9:02pm UTC](https://www.epiusers.help/t/e10-baq-to-sql/87423/1 "2022-02-11T21:02:58Z")
We have EDA, and we have some BAQs that we will drop in the script to SQL and see if they run before we sync in EDA, only because I do not know how to write in SQL. We have a LaborHed.CheckBox01, that is a UD field added in V9. We we try to run the BAQ in SQL we get this message. Msg 207, Level 16, State 1, Line 18 Invalid column name ‘CheckBox01’. So in E10 the UD fields show in a table LaborHed\_UD, so we tried changing the SQL where (LaborHed.PayrollDate \>= ‘01/01/2020’ and LaborHed.ActiveTrans = 0 and LaborHed\_UD.CheckBox01 = 0) and get this message Msg 4104, Level 16, State 1, Line 18 The multi-part identifier “LaborHed\_UD.CheckBox01” could not be bound. Any suggestions? Thanks ---
**Author:** ![Vinaykamboj](https://www.epiusers.help/user_avatar/www.epiusers.help/vinaykamboj/32/14848_2.png) [@Vinaykamboj](https://www.epiusers.help/u/Vinaykamboj)\ **Post date:** [February 11, 2022, 9:29pm UTC](https://www.epiusers.help/t/e10-baq-to-sql/87423/2 "2022-02-11T21:29:02Z")
You will probably have to do a join between LaborHed and LaborHed\_UD. In BAQ Epimagic takes care of it. ---
**Author:** ![gpayne](https://www.epiusers.help/user_avatar/www.epiusers.help/gpayne/32/55751_2.png) [@gpayne](https://www.epiusers.help/u/gpayne)\ **Post date:** [February 11, 2022, 9:35pm UTC](https://www.epiusers.help/t/e10-baq-to-sql/87423/3 "2022-02-11T21:35:56Z")
In SQL change the from table of any unknown columns to dbo from erp and that will give you the view which is the join of the system table and the \_UD table. ---
**Author:** ![lairdl](https://www.epiusers.help/letter_avatar_proxy/v4/letter/l/ecb155/32.png) [@lairdl](https://www.epiusers.help/u/lairdl)\ **Post date:** [February 14, 2022, 12:29pm UTC](https://www.epiusers.help/t/e10-baq-to-sql/87423/4 "2022-02-14T12:29:53Z")
Thank you! ![image515438.jpg](https://www.epiusers.help/uploads/default/original/3X/7/d/7dcf3a1c98a313d7f5397eb172bde9d51e24fd0d.jpeg) ![image558500.jpg](https://www.epiusers.help/uploads/default/original/3X/5/8/588a0035f111be6cf5f94e6258c825c5bb67d16f.jpeg) ![image980016.png](https://www.epiusers.help/uploads/default/original/3X/b/0/b0da6b12f1fa854583b54f43314d9a5b7c08e6ff.png) ![image171660.png](https://www.epiusers.help/uploads/default/original/3X/d/a/daae00eb044411927f617ee076ddfda41b28f5cd.png) ![image875788.png](https://www.epiusers.help/uploads/default/original/3X/d/b/db5036a5f774a742ad73c7c2ee3b7e0f5ad2c09a.png) ![image529390.png](https://www.epiusers.help/uploads/default/original/3X/9/f/9fa030d48160aa275905de9e115f131f684a4966.png) ---
**Author:** ![A.Baeisa](https://www.epiusers.help/user_avatar/www.epiusers.help/a.baeisa/32/8988_2.png) [@A.Baeisa](https://www.epiusers.help/u/A.Baeisa)\ **Post date:** [February 14, 2022, 1:09pm UTC](https://www.epiusers.help/t/e10-baq-to-sql/87423/5 "2022-02-14T13:09:24Z")
can you paste the SQL code in here ? ---
**Author:** ![lairdl](https://www.epiusers.help/letter_avatar_proxy/v4/letter/l/ecb155/32.png) [@lairdl](https://www.epiusers.help/u/lairdl)\ **Post date:** [February 14, 2022, 1:15pm UTC](https://www.epiusers.help/t/e10-baq-to-sql/87423/6 "2022-02-14T13:15:31Z")
Here is the code, with the addition of joining the LaborHed\_UD, and we are going to try it this morning. What do you think? select [EmpBasic1].[Company] as [EmpBasic1\_Company], [EmpBasic1].[JCDept] as [EmpBasic1\_JCDept], [EmpBasic1].[EmpStatus] as [EmpBasic1\_EmpStatus], [EmpBasic1].[EmpID] as [EmpBasic1\_EmpID], (EmpBasic1.LastName + ', ’ + EmpBasic1.FirstName) as [Calculated\_Employee], [DirLbrErnHrs].[Calculated\_DirLbrHrs] as [Calculated\_DirLbrHrs], [DirLbrErnHrs].[Calculated\_ErnHrs] as [Calculated\_ErnHrs], [LbrHead].[Calculated\_PayHours] as [Calculated\_PayHours], [LbrHead].[LaborHed\_PayrollDate] as [LaborHed\_PayrollDate] from Erp.EmpBasic as EmpBasic1 inner join (select [LaborHed].[Company] as [LaborHed\_Company], [LaborHed].[PayrollDate] as [LaborHed\_PayrollDate], [LaborHed].[EmployeeNum] as [LaborHed\_EmployeeNum], (sum(LaborHed.PayHours)) as [Calculated\_PayHours] from Erp.LaborHed as LaborHed inner join erp.LaborHed\_UD as LaborHed\_UD on LaborHed.SysRowID = LaborHed\_UD.ForeignSysRowID where (LaborHed.PayrollDate \>= ‘01/01/2020’ and LaborHed.ActiveTrans = 0 and LaborHed\_UD.CheckBox01 = 0) group by [LaborHed].[Company], [LaborHed].[PayrollDate], [LaborHed].[EmployeeNum]) as LbrHead on EmpBasic1.Company = LbrHead.LaborHed\_Company and EmpBasic1.EmpID = LbrHead.LaborHed\_EmployeeNum left outer join (select [LaborDtl].[Company] as [LaborDtl\_Company], [LaborDtl].[PayrollDate] as [LaborDtl\_PayrollDate], [EmpBasic].[JCDept] as [EmpBasic\_JCDept], [LaborDtl].[EmployeeNum] as [LaborDtl\_EmployeeNum], (sum(LaborDtl.LaborHrs)) as [Calculated\_DirLbrHrs], (sum(LaborDtl.EarnedHrs)) as [Calculated\_ErnHrs] from Erp.LaborDtl as LaborDtl inner join Erp.EmpBasic as EmpBasic on LaborDtl.Company = EmpBasic.Company and LaborDtl.EmployeeNum = EmpBasic.EmpID where (LaborDtl.LaborType \<\> ‘I’ and LaborDtl.ActiveTrans = 0) group by [LaborDtl].[Company], [LaborDtl].[PayrollDate], [EmpBasic].[JCDept], [LaborDtl].[EmployeeNum]) as DirLbrErnHrs on LbrHead.LaborHed\_Company = DirLbrErnHrs.LaborDtl\_Company and LbrHead.LaborHed\_EmployeeNum = DirLbrErnHrs.LaborDtl\_EmployeeNum and LbrHead.LaborHed\_PayrollDate = DirLbrErnHrs.LaborDtl\_PayrollDate ![image515438.jpg](https://www.epiusers.help/uploads/default/original/3X/7/d/7dcf3a1c98a313d7f5397eb172bde9d51e24fd0d.jpeg) ![image558500.jpg](https://www.epiusers.help/uploads/default/original/3X/5/8/588a0035f111be6cf5f94e6258c825c5bb67d16f.jpeg) ![image980016.png](https://www.epiusers.help/uploads/default/original/3X/b/0/b0da6b12f1fa854583b54f43314d9a5b7c08e6ff.png) ![image171660.png](https://www.epiusers.help/uploads/default/original/3X/d/a/daae00eb044411927f617ee076ddfda41b28f5cd.png) ![image875788.png](https://www.epiusers.help/uploads/default/original/3X/d/b/db5036a5f774a742ad73c7c2ee3b7e0f5ad2c09a.png) ![image529390.png](https://www.epiusers.help/uploads/default/original/3X/9/f/9fa030d48160aa275905de9e115f131f684a4966.png) ---
**Author:** ![A.Baeisa](https://www.epiusers.help/user_avatar/www.epiusers.help/a.baeisa/32/8988_2.png) [@A.Baeisa](https://www.epiusers.help/u/A.Baeisa)\ **Post date:** [February 14, 2022, 1:36pm UTC](https://www.epiusers.help/t/e10-baq-to-sql/87423/7 "2022-02-14T13:36:54Z")
try this ```auto select EmpBasic1.Company as EmpBasic1_Company, EmpBasic1.JCDept as EmpBasic1_JCDept, EmpBasic1.EmpStatus as EmpBasic1_EmpStatus, EmpBasic1.EmpID as EmpBasic1_EmpID, (EmpBasic1.LastName + ', ' + EmpBasic1.FirstName) as Calculated_Employee, DirLbrErnHrs.Calculated_DirLbrHrs as Calculated_DirLbrHrs, DirLbrErnHrs.Calculated_ErnHrs as Calculated_ErnHrs, LbrHead.Calculated_PayHours as Calculated_PayHours, LbrHead.LaborHed_PayrollDate as LaborHed_PayrollDate from Erp.EmpBasic as EmpBasic1 inner join (select LaborHed.Company as LaborHed_Company, LaborHed.PayrollDate as LaborHed_PayrollDate, LaborHed.EmployeeNum as LaborHed_EmployeeNum, (sum(LaborHed.PayHours)) as Calculated_PayHours from LaborHed as LaborHed where (LaborHed.PayrollDate >= '01/01/2020' and LaborHed.ActiveTrans = 0 and LaborHed.CheckBox01 = 0) group by LaborHed.Company, LaborHed.PayrollDate, LaborHed.EmployeeNum) as LbrHead on EmpBasic1.Company = LbrHead.LaborHed_Company and EmpBasic1.EmpID = LbrHead.LaborHed_EmployeeNum left outer join (select LaborDtl.Company as LaborDtl_Company, LaborDtl.PayrollDate as LaborDtl_PayrollDate, EmpBasic.JCDept as EmpBasic_JCDept, LaborDtl.EmployeeNum as LaborDtl_EmployeeNum, (sum(LaborDtl.LaborHrs)) as Calculated_DirLbrHrs, (sum(LaborDtl.EarnedHrs)) as Calculated_ErnHrs from Erp.LaborDtl as LaborDtl inner join Erp.EmpBasic as EmpBasic on LaborDtl.Company = EmpBasic.Company and LaborDtl.EmployeeNum = EmpBasic.EmpID where (LaborDtl.LaborType <> 'I' and LaborDtl.ActiveTrans = 0) group by LaborDtl.Company, LaborDtl.PayrollDate, EmpBasic.JCDept, LaborDtl.EmployeeNum) as DirLbrErnHrs on LbrHead.LaborHed_Company = DirLbrErnHrs.LaborDtl_Company and LbrHead.LaborHed_EmployeeNum = DirLbrErnHrs.LaborDtl_EmployeeNum and LbrHead.LaborHed_PayrollDate = DirLbrErnHrs.LaborDtl_PayrollDate ``` ---
**Author:** ![A.Baeisa](https://www.epiusers.help/user_avatar/www.epiusers.help/a.baeisa/32/8988_2.png) [@A.Baeisa](https://www.epiusers.help/u/A.Baeisa)\ **Post date:** [February 14, 2022, 2:10pm UTC](https://www.epiusers.help/t/e10-baq-to-sql/87423/8 "2022-02-14T14:10:33Z")
once first UD field created on any Epicor table, the view table that you should use is without Erp. nor odb. just the name of the table ![image](https://www.epiusers.help/uploads/default/original/3X/9/2/926cfb87f3f790ff7a72b3a0932635fdc6af60d9.png)