I need help with BAQ

Epicor ERP 10
21

select distinct
[LaborDtl].[JobNum] as [LaborDtl_JobNum],
[SNTran].[PartNum] as [SNTran_PartNum],
[SNTran].[SerialNumber] as [SNTran_SerialNumber],
[LaborDtl].[OprSeq] as [LaborDtl_OprSeq],
[LaborDtl].[EmployeeNum] as [LaborDtl_EmployeeNum],
[LaborDtl].[ClockInDate] as [LaborDtl_ClockInDate],
[LaborDtl].[ClockinTime] as [LaborDtl_ClockinTime],
[LaborDtl].[ClockOutTime] as [LaborDtl_ClockOutTime]
from Erp.LaborDtl as LaborDtl
inner join Erp.SNTran as SNTran on
LaborDtl.Company = SNTran.Company
and LaborDtl.JobNum = SNTran.JobNum
and LaborDtl.OprSeq = SNTran.OprSeq
and LaborDtl.AssemblySeq = SNTran.AssemblySeq
and ( SNTran.TranType = ‘OPR-CMP’ or SNTran.TranType = ‘OPR-RWK’ )

where (LaborDtl.JobNum = ‘CT481-JIS-001’ and LaborDtl.EmployeeNum = ‘740’)

22

for got to remove to the distinct but the result is still the same.

select
[LaborDtl].[JobNum] as [LaborDtl_JobNum],
[SNTran].[PartNum] as [SNTran_PartNum],
[SNTran].[SerialNumber] as [SNTran_SerialNumber],
[LaborDtl].[OprSeq] as [LaborDtl_OprSeq],
[LaborDtl].[EmployeeNum] as [LaborDtl_EmployeeNum],
[LaborDtl].[ClockInDate] as [LaborDtl_ClockInDate],
[LaborDtl].[ClockinTime] as [LaborDtl_ClockinTime],
[LaborDtl].[ClockOutTime] as [LaborDtl_ClockOutTime]
from Erp.LaborDtl as LaborDtl
inner join Erp.SNTran as SNTran on
LaborDtl.Company = SNTran.Company
and LaborDtl.JobNum = SNTran.JobNum
and LaborDtl.OprSeq = SNTran.OprSeq
and LaborDtl.AssemblySeq = SNTran.AssemblySeq
and ( SNTran.TranType = ‘OPR-CMP’ or SNTran.TranType = ‘OPR-RWK’ )

where (LaborDtl.JobNum = ‘CT481-JIS-001’ and LaborDtl.EmployeeNum = ‘740’)

23

Can you remove the filters from the SN table?

24

Just did, and result still the same

25

How about the rest of the filters?

26

Agree with @Banderson the order does not matter with inner joins. Have you queried separately yet to see the records not connected that are you trying to connect?

1 Like
27

Yes, when I query separately It’s fine. Its only when I join them that the problem happens

28

Does your expected join fields match each other when queried separately? I know simple question, but not seeing the data is kind of hard to step through it.

29

So with no filters you get 0 records now?

Can you remove the opr join and just display the fields?

30

hmm OK, so in the database under SNTran table it seems that the OprSeq column doesn’t have any data, only the LastLbrOprSeq column has it

1 Like
31

Yes when I remove the join the fields display

32

OprSeq is blank for all records or just the ones you are filtering for?

33

So the problem was that the SNtran table didn’t have an op seq. So you should be able to just use the assembly seq. Does that show you what you need?

34

for all records

35

Now that you know the join was what was messing you up, you can add back the employee number and opcode filters.

36

OK calling it a night, but have you tried the subquery option. Change this part of your query, Erp.SNTran as SNTran, to something like this… I have not done this in the BAQ builder so I am not sure the steps.

select Company, SerailNo, TranType, JobNum, AssemblySeq, max(trandate) as TranDate (if trandate is in the table)
from SNTran
where (TranType = ‘OPR-CMP’ or TranType = ‘OPR-RWK’)
Group by Company, SerialNo, Trantype, JobNum, AssemblySeq

This should give you a single record per day for each of those fields. If you want just one record drop the trandate field.

This becomes a subset of group data to link in as your table. It will cut down on the multiple records you are picking up when adding in SNTran.

37

I think your original query was just fine. All you needed was the assembly sequence, you were getting multiples because there were multiple assembly sequences on the job with the same part number. Here’s the query that I think should work, minus the filters. You will probably want to leave those off your BAQ as you will add trackers in you dashboard anyways.

select 
	[LaborDtl].[JobNum] as [LaborDtl_JobNum],
	[JobOper].[PartNum] as [JobOper_PartNum],
	[SNTran].[SerialNumber] as [SNTran_SerialNumber],
	[LaborDtl].[EmployeeNum] as [LaborDtl_EmployeeNum],
	[LaborDtl].[ClockInDate] as [LaborDtl_ClockInDate],
	[LaborDtl].[ClockinTime] as [LaborDtl_ClockinTime],
	[LaborDtl].[Company] as [LaborDtl_Company]
from Erp.LaborDtl as LaborDtl
inner join Erp.JobOper as JobOper on 
	LaborDtl.Company = JobOper.Company
	and LaborDtl.JobNum = JobOper.JobNum
	and LaborDtl.AssemblySeq = JobOper.AssemblySeq
	and LaborDtl.OprSeq = JobOper.OprSeq
inner join Erp.SNTran as SNTran on 
	JobOper.Company = SNTran.Company
	and JobOper.JobNum = SNTran.JobNum
	and JobOper.AssemblySeq = SNTran.AssemblySeq
	and JobOper.PartNum = SNTran.PartNum