Row_Number syntax error

Kinetic ERP
,
1

I’m working on a BAQ that’s kind of a modified Shop Tracker view for a dashboard. I want to use a row number count to help ID multiple rows of the same employee ID. I have a calculated filed with my Row_Number equation:
ROW_NUMBER() OVER(PARTITION BY LaborDtl.EmployeeNum ORDER BY LaborDtl.LaborDtlSeq asc)
But I keep getting a error "Incorrect syntax near the keyword ‘By’. I’ve used this code a dozen times before without any problems. I can’t figure out why this isn’t working in this BAQ.
Here’s my BAQ:

 * Disclaimer!!!
 * This is not a real query being executed, but a simplified version for general vision.
 * Executing it with any other tool may produce a different result.
 */
 
select 
	[LaborDtl].[EmployeeNum] as [LaborDtl_EmployeeNum],
	[EmpBasic].[Name] as [EmpBasic_Name],
	[LaborDtl].[JCDept] as [LaborDtl_JCDept],
	[LaborDtl].[DspClockInTime] as [LaborDtl_DspClockInTime],
	((case 
   when  LaborDtl.LaborType = 'I'
   then  'Indirect Labor'
   else  'Production'
 end)) as [Calculated_LbrType],
	((case 
   when  LaborDtl.LaborType = 'I'
   then  Indirect.Description
   else  LaborDtl.JobNum
 end)) as [Calculated_Work],
	[LaborDtl].[OprSeq] as [LaborDtl_OprSeq],
	[LaborDtl].[OpCode] as [LaborDtl_OpCode],
	[JobHead].[PartNum] as [JobHead_PartNum],
	[JobHead].[PartDescription] as [JobHead_PartDescription],
	[JobHead].[ProdQty] as [JobHead_ProdQty],
	[JobHead].[DueDate] as [JobHead_DueDate],
	((case when JobHead.JobCode = '99999' then 'STOCK' else JobHead.JobCode end)) as [Calculated_Order],
	[Customer].[Name] as [Customer_Name],
	(ROW_NUMBER() OVER(PARTITION BY LaborDtl.EmployeeNum ORDER BY LaborDtl.LaborDtlSeq asc)) as [Calculated_RowCount],
	[LaborDtl].[LaborDtlSeq] as [LaborDtl_LaborDtlSeq]
from Erp.LaborDtl as LaborDtl
left outer join Erp.JobHead as JobHead on 
	LaborDtl.Company = JobHead.Company
	and LaborDtl.JobNum = JobHead.JobNum
left outer join Erp.OrderHed as OrderHed on 
	JobHead.Company = OrderHed.Company
	and JobHead.JobCode = OrderHed.OrderNum
left outer join Erp.Customer as Customer on 
	OrderHed.Company = Customer.Company
	and OrderHed.CustNum = Customer.CustNum
left outer join Erp.EmpBasic as EmpBasic on 
	LaborDtl.Company = EmpBasic.Company
	and LaborDtl.EmployeeNum = EmpBasic.EmpID
left outer join Erp.Indirect as Indirect on 
	LaborDtl.Company = Indirect.Company
	and LaborDtl.IndirectCode = Indirect.IndirectCode
where (LaborDtl.ActiveTrans = true)
order by LbrType Desc, LaborDtl.JCDept, LaborDtl.EmployeeNum
2

I think I see it? Between here

as [Calculated_LbrType]

and here

order by LbrType Desc
1 Like
3

So I removed my 3 condition statements, Calc_LbrType, Calc_Work, Calc_Order and the Row_Number calculated field worked correctly. I then added each condition field back in, testing after each one, and the BAQ still worked.
Maybe I just need my row count before any conditions are added. I’ve never seen that before.

2 Likes
4

Out of curiosity, can you post the “after” SQL. Is it any different?

5

Sure.

 * Disclaimer!!!
 * This is not a real query being executed, but a simplified version for general vision.
 * Executing it with any other tool may produce a different result.
 */
 
select 
	[LaborDtl].[EmployeeNum] as [LaborDtl_EmployeeNum],
	[EmpBasic].[Name] as [EmpBasic_Name],
	[LaborDtl].[JCDept] as [LaborDtl_JCDept],
	[LaborDtl].[DspClockInTime] as [LaborDtl_DspClockInTime],
	((case when LaborDtl.LaborType = 'I' then 'Indirect Labor' else 'Production' end)) as [Calculated_LbrType],
	((case when LaborDtl.LaborType = 'P' then LaborDtl.JobNum else Indirect.Description end)) as [Calculated_Work],
	[LaborDtl].[OprSeq] as [LaborDtl_OprSeq],
	[LaborDtl].[OpCode] as [LaborDtl_OpCode],
	[JobHead].[PartNum] as [JobHead_PartNum],
	[JobHead].[PartDescription] as [JobHead_PartDescription],
	[JobHead].[ProdQty] as [JobHead_ProdQty],
	[JobHead].[DueDate] as [JobHead_DueDate],
	((case when JobHead.JobCode = '99999' then 'STOCK' else JobHead.JobCode end)) as [Calculated_SOrder],
	[Customer].[Name] as [Customer_Name],
	(ROW_NUMBER() OVER(PARTITION BY LaborDtl.EmployeeNum ORDER BY LaborDtl.LaborDtlSeq asc)) as [Calculated_RowCount],
	[LaborDtl].[LaborDtlSeq] as [LaborDtl_LaborDtlSeq]
from Erp.LaborDtl as LaborDtl
left outer join Erp.JobHead as JobHead on 
	LaborDtl.Company = JobHead.Company
	and LaborDtl.JobNum = JobHead.JobNum
left outer join Erp.OrderHed as OrderHed on 
	JobHead.Company = OrderHed.Company
	and JobHead.JobCode = OrderHed.OrderNum
left outer join Erp.Customer as Customer on 
	OrderHed.Company = Customer.Company
	and OrderHed.CustNum = Customer.CustNum
left outer join Erp.EmpBasic as EmpBasic on 
	LaborDtl.Company = EmpBasic.Company
	and LaborDtl.EmployeeNum = EmpBasic.EmpID
left outer join Erp.Indirect as Indirect on 
	LaborDtl.Company = Indirect.Company
	and LaborDtl.IndirectCode = Indirect.IndirectCode
where (LaborDtl.ActiveTrans = true)
order by LaborDtl.LaborType Desc, LaborDtl.JCDept, LaborDtl.EmployeeNum
6

Yeah, your “order by” at the end is different. I believe it was because you were trying to order by a calculated field. Sometimes that can get wonky.

2 Likes
7

Thanks, I see it now too. It might have been the order in which I made the calculated fields that messed it up.
Now I’m going to try to project this into a web browser for the shop monitors. :hand_with_index_finger_and_thumb_crossed:

1 Like
8

No, you literally picked a different thing to order by.

This is what you had, you chose the calculated field.

image
image785×179 11.1 KB

This is the new one, you chose the actual field.

image
image772×181 11.2 KB

3 Likes
9

Yes, I see it. I don’t believe that was intentional. I must have picked it because I didn’t have the calc field available then. Then I added the calculated fields.
So to be clear I don’t want to Order by a calc field, I should use an actual field.

1 Like
10

Yup. I just didn’t want you thinking that it was just a random glitch that was solved by “Turning it off then on again”. lol

3 Likes