Decimal Acting Like a String

NateS · December 1, 2022, 2:35pm

Good morning,
I have a BAQ that parses out the hours from the tilde (~) delimited list in the ResourceTimeUsed table. I think I got the base for this from someone on here. The formula goes like this:

[Ice].entry(RowCount.Calculated_RowNum, RTU.ResourceTimeUsed_LoadHours,'~')

This is assigned in the BAQ Calculated Field SQL Editor as DayLoadHours, which is a decimal formatted as: ->>,>>9.99
As long as that RowCount table is in place, this works pretty well. it parses out all the individual load hours into their own row. And the decimal shows correctly.

Now I am trying to utilize that calculated value farther down the BAQ line. I am just trying to get a sum of the load hours, but it thinks my decimal is a string, returning this compile error: Operand data type nvarchar is invalid for sum operator.

I can make this error go away by altering this formula that is supposed to take the sum of the load hours.

//from this:
sum(SubQuery4.Calculated_LoadHours)

//to this:
sum(convert(decimal,SubQuery4.Calculated_LoadHours))

However, when I do this, there seems to be something that is rounding the decimal up to the whole number. I know I didn’t do any rounding anywhere in my expressions. If I look at the values that are being summed, there are digits in the decimal places. However, after I sum it, the calculated value only returns whole numbers with .00 at the end. Still formatting them as if they were decimals but losing the precision.

Do you know of any issue with using convert to decimal like this? I have gone around and around trying to get this to sum correctly. I made more levels of subqueries to try to abstract that value as far away from the original string as I can. That didn’t help.

I have attached the BAQ if you’re feeling ambitious. Just give it a date range of the next month or so. Also, there is a resource group filter on the subquery4 in the subquery criteria. You will have to remove it or pick a few resource groups you want to review.

If you run it, you should see the jobs (parts and assembly parts) that are applying load in that date range. You should also be able to see the estimated earned hours, and the number of load hours applied by each operation. If the operation spans more than one day, you will see multiple entries for that operation and the load from each day.
RTU_LoadTotalsWithEstEH.baq (94.9 KB)
Thank you for your time!
Nate

Banderson · December 1, 2022, 2:53pm

There are a couple of things you could try, the first is to specify the decimal places.

sum(convert(decimal(5,2),SubQuery4.Calculated_LoadHours))

Alternatively, Instead of using convert, try casting it. Sometimes it works better.

So

sum( cast(SubQuery4.Calculated_LoadHours as decimal (5,2)))

https://database.guide/4-functions-to-format-a-number-to-2-decimal-places-in-sql-server/

NateS · December 1, 2022, 3:52pm

The cast did the trick! Thank you!!!

CHatzis · February 24, 2023, 10:34am

I arrived at this post with the exact same issue as you, trying to sum the extrated LoadHours per day. So that question is already answered. But I was wondering what method you are using in the SubQuery RowCount to extract the individual LoadHours per day? I get an error trying to import you BAQ.

I use a Subquery for each day and use a Union to gather the LoadHours for each day and do the sum. But that means I have to create a lot of SubQuery’s to be sure I do not miss a day of Load, with the probability of having SubQuery’s with a lot of lines all with no value in the calculated LoadHours.

day 1: [ice].entry(1, ResourceTimeUsed.LoadHours, ‘~’)
day 2: [ice].entry(2, ResourceTimeUsed.LoadHours, ‘~’)
etc.

Christian

NateS · February 24, 2023, 1:34pm

I didn’t build the BAQ. But, it uses the ZDataField table in Epicor as a way to count, I think.
The RowCount subquery just uses that table, and this calculated field:
RowNum (int)

ROW_NUMBER() OVER(ORDER BY ZDataField.SysRevID ASC)

This gets joined to the RTU subquery at the top level with the relation:

Calculated_RowNum <= [Ice].num_entries(RTU.ResourceTimeUsed_LoadDays,'~')

I think this is where the magic happens. You want every single value parsed out of the list of values separated by the tilde. But you don’t know how long that list might be. I think the rowcount in the ZDataField table just allows you to count up very high. We have nearly 150k rows in that table.
Honestly I don’t fully understand it either, but I know that it works in this case. Often my query times-out. I just choose a smaller date range for the parameters.

The RTU subquery return a ton of fields from the ResourceTimeUsed table, with a criteria on the table to filter the load date by start and end date parameters. At the top level the RTU and Rowcount are joined, along with the other relevant tables in this fashion:

My final, working BAQ has a few more levels on top of the subquery1, which should work fine as a top level. I added two more layers to filter only the resource groups I care about. You have to be really careful about filtering out enough data to allow the BAQ designer to show it without timing out. Thats why I filter for resource groups and use the date range parameters on the load dates.

Here’s the SQL if you know how to build it from that.

/*
 * 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 
	[SubQuery4].[ResourceTimeUsed_WhatIf] as [ResourceTimeUsed_WhatIf],
	[SubQuery4].[Resource_ResourceGrpID] as [Resource_ResourceGrpID],
	[SubQuery4].[Calculated_LoadMonth] as [Calculated_LoadMonth],
	[SubQuery4].[JobHead_JobNum] as [JobHead_JobNum],
	[SubQuery4].[JobHead_PartNum] as [JobHead_PartNum],
	[SubQuery4].[JobHead_ProdQty] as [JobHead_ProdQty],
	[SubQuery4].[JobAsmbl_PartNum] as [JobAsmbl_PartNum],
	[SubQuery4].[JobOper_OprSeq] as [JobOper_OprSeq],
	[SubQuery4].[JobOper_RunQty] as [JobOper_RunQty],
	[SubQuery4].[JobOper_ProdStandard] as [JobOper_ProdStandard],
	[SubQuery4].[Calculated_EarnedHours] as [Calculated_EarnedHours],
	(sum(cast(SubQuery4.Calculated_LoadHours as decimal (5,2)))) as [Calculated_TotalLoad]
from  (select 
	[SubQuery1].[ResourceTimeUsed_WhatIf] as [ResourceTimeUsed_WhatIf],
	[SubQuery1].[Resource_ResourceGrpID] as [Resource_ResourceGrpID],
	(datepart(month, SubQuery1.ResourceTimeUsed_LoadDate)) as [Calculated_LoadMonth],
	[SubQuery1].[JobHead_JobNum] as [JobHead_JobNum],
	[SubQuery1].[JobHead_PartNum] as [JobHead_PartNum],
	[SubQuery1].[JobHead_ProdQty] as [JobHead_ProdQty],
	[SubQuery1].[JobAsmbl_PartNum] as [JobAsmbl_PartNum],
	[SubQuery1].[JobOper_OprSeq] as [JobOper_OprSeq],
	[SubQuery1].[JobOper_RunQty] as [JobOper_RunQty],
	[SubQuery1].[JobOper_ProdStandard] as [JobOper_ProdStandard],
	((SubQuery1.JobOper_RunQty * SubQuery1.JobOper_ProdStandard)/60) as [Calculated_EarnedHours],
	(SubQuery1.Calculated_DayLoadHours) as [Calculated_LoadHours]
from  (select distinct
	[Resource].[ResourceGrpID] as [Resource_ResourceGrpID],
	[ResourceGroup].[Description] as [ResourceGroup_Description],
	[JobHead].[PartNum] as [JobHead_PartNum],
	[JobAsmbl].[PartNum] as [JobAsmbl_PartNum],
	([Ice].entry(RowCount.Calculated_RowNum, RTU.ResourceTimeUsed_LoadHours,'~')) as [Calculated_DayLoadHours],
	[RowCount].[Calculated_RowNum] as [Calculated_RowNum],
	[JobHead].[JobNum] as [JobHead_JobNum],
	[RTU].[ResourceTimeUsed_LoadDate] as [ResourceTimeUsed_LoadDate],
	[RTU].[ResourceTimeUsed_WhatIf] as [ResourceTimeUsed_WhatIf],
	[JobOper].[RunQty] as [JobOper_RunQty],
	[JobOper].[ProdStandard] as [JobOper_ProdStandard],
	[JobHead].[ProdQty] as [JobHead_ProdQty],
	[JobOper].[OprSeq] as [JobOper_OprSeq]
from  (select 
	[ResourceTimeUsed].[Company] as [ResourceTimeUsed_Company],
	[ResourceTimeUsed].[JobNum] as [ResourceTimeUsed_JobNum],
	[ResourceTimeUsed].[AssemblySeq] as [ResourceTimeUsed_AssemblySeq],
	[ResourceTimeUsed].[OprSeq] as [ResourceTimeUsed_OprSeq],
	[ResourceTimeUsed].[OpDtlSeq] as [ResourceTimeUsed_OpDtlSeq],
	[ResourceTimeUsed].[WhatIf] as [ResourceTimeUsed_WhatIf],
	[ResourceTimeUsed].[AllocNum] as [ResourceTimeUsed_AllocNum],
	[ResourceTimeUsed].[ResourceGrpID] as [ResourceTimeUsed_ResourceGrpID],
	[ResourceTimeUsed].[ResourceID] as [ResourceTimeUsed_ResourceID],
	[ResourceTimeUsed].[StartDate] as [ResourceTimeUsed_StartDate],
	[ResourceTimeUsed].[StartTime] as [ResourceTimeUsed_StartTime],
	[ResourceTimeUsed].[EndDate] as [ResourceTimeUsed_EndDate],
	[ResourceTimeUsed].[EndTime] as [ResourceTimeUsed_EndTime],
	[ResourceTimeUsed].[Lock] as [ResourceTimeUsed_Lock],
	[ResourceTimeUsed].[LoadDate] as [ResourceTimeUsed_LoadDate],
	[ResourceTimeUsed].[LoadHour] as [ResourceTimeUsed_LoadHour],
	[ResourceTimeUsed].[EstHours] as [ResourceTimeUsed_EstHours],
	[ResourceTimeUsed].[ActualHours] as [ResourceTimeUsed_ActualHours],
	[ResourceTimeUsed].[LoadDays] as [ResourceTimeUsed_LoadDays],
	[ResourceTimeUsed].[LoadHours] as [ResourceTimeUsed_LoadHours],
	[ResourceTimeUsed].[RestoreFlag] as [ResourceTimeUsed_RestoreFlag],
	[ResourceTimeUsed].[SysDate] as [ResourceTimeUsed_SysDate],
	[ResourceTimeUsed].[SysTime] as [ResourceTimeUsed_SysTime],
	[ResourceTimeUsed].[JobEngineered] as [ResourceTimeUsed_JobEngineered],
	[ResourceTimeUsed].[DailyProdQty] as [ResourceTimeUsed_DailyProdQty],
	[ResourceTimeUsed].[SysUser] as [ResourceTimeUsed_SysUser],
	[ResourceTimeUsed].[SysRowID] as [ResourceTimeUsed_SysRowID]
from Erp.ResourceTimeUsed as ResourceTimeUsed
where (ResourceTimeUsed.LoadDate >= @StartDate  and ResourceTimeUsed.LoadDate <= @EndDate))  as RTU
inner join Erp.Resource as Resource on 
	Resource.Company = RTU.ResourceTimeUsed_Company
	and Resource.ResourceID = RTU.ResourceTimeUsed_ResourceID
inner join Erp.ResourceGroup as ResourceGroup on 
	Resource.Company = ResourceGroup.Company
	and Resource.ResourceGrpID = ResourceGroup.ResourceGrpID
inner join  (select 
	(ROW_NUMBER() OVER(ORDER BY ZDataField.SysRevID ASC)) as [Calculated_RowNum]
from Ice.ZDataField as ZDataField)  as RowCount on 
	RowCount.Calculated_RowNum <= [Ice].num_entries(RTU.ResourceTimeUsed_LoadDays,'~')
inner join Erp.JobOper as JobOper on 
	JobOper.Company = RTU.ResourceTimeUsed_Company
	and JobOper.JobNum = RTU.ResourceTimeUsed_JobNum
	and JobOper.AssemblySeq = RTU.ResourceTimeUsed_AssemblySeq
	and JobOper.OprSeq = RTU.ResourceTimeUsed_OprSeq
inner join Erp.JobAsmbl as JobAsmbl on 
	JobAsmbl.Company = JobOper.Company
	and JobAsmbl.JobNum = JobOper.JobNum
	and JobAsmbl.AssemblySeq = JobOper.AssemblySeq
inner join Erp.JobProd as JobProd on 
	JobProd.Company = JobAsmbl.Company
	and JobProd.JobNum = JobAsmbl.JobNum
inner join Erp.JobHead as JobHead on 
	JobHead.Company = JobProd.Company
	and JobHead.JobNum = JobProd.JobNum
	and ( JobHead.JobClosed = false  ))  as SubQuery1
where (SubQuery1.Resource_ResourceGrpID in ('2B', '4A', 'MCU')))  as SubQuery4
group by [SubQuery4].[ResourceTimeUsed_WhatIf],
	[SubQuery4].[Resource_ResourceGrpID],
	[SubQuery4].[Calculated_LoadMonth],
	[SubQuery4].[JobHead_JobNum],
	[SubQuery4].[JobHead_PartNum],
	[SubQuery4].[JobHead_ProdQty],
	[SubQuery4].[JobAsmbl_PartNum],
	[SubQuery4].[JobOper_OprSeq],
	[SubQuery4].[JobOper_RunQty],
	[SubQuery4].[JobOper_ProdStandard],
	[SubQuery4].[Calculated_EarnedHours]
order by SubQuery4.ResourceTimeUsed_WhatIf, SubQuery4.JobHead_PartNum, SubQuery4.JobAsmbl_PartNum, SubQuery4.JobOper_OprSeq

Good luck!

CHatzis · February 24, 2023, 3:41pm

That is where the magic happens indeed. I knew of the function [Ice].num_entries(), but did not know you can use it like that as a Table Relation to get as many individual rows as there are values seperated by the delimiter. You just forgot to mention to create the calculated field below to actually get the individual values from LoadHours field, but I found it in the SQL text.

[Ice].entry(RowCount.Calculated_RowNum, RTU.ResourceTimeUsed_LoadHours,'~')

Thanks a lot for your time. This creates a lot of opportunities for future BAQ’s.
Christian

P.S.: I have used the ROW_NUMBER()-function for creating simple number lists using any long table with the following settings in the SubQuery. This might help speeding up your BAQ so it won’t time out. In my case the maximum days a resource is scheduled on an operation is 4, so RowCount top 10 is more than enough.