I am using a calculated field of ‘sum(OrderRel.OurReqQty)’ and if there is no value, the BAQ currently leaves the field as blank but we want it to be defaulted to a value of zero so that it can be used in another calculated field such as ‘0 + SubQuery.CalculatedOrderQty’ correctly. Any way to default a calculated field as zero instead of blank if there is no sum value?
You can use
ISNULL(sum(OrderRel.OurReqQty),0)
I tried that but it is still leaving the field as blank
@cchang
Do the ISNULL first, then take the sum, like
sum(ISNULL(OrderRel.OurReqQty, 0))
Order of operations matters.
Hope this helps.
Matthew Morgan
1 Like
Tried the order of operations as you stated but still is not working.
Maybe wrap it in another query. Meaning,
- The current top-level becomes a subquery
- Make a new top-level
- Create calculated field using isnull() as described (or case when, etc.)
Yes, it is originally set up as a subquery as you indicated which I thought might be the issue with the calculated field in a subquery but is still not working.
It would probably help if you post your query SQL or at least a BAQ of a simplified version of what you are trying to do. There is no reason that @Matthew_Morgan 's suggestion should not be working, so there must be something else in there causing a problem, which we can’t see.
The calculated field is a subquery from the top level query with the query phrase below.
select
[JobHead].[JobClosed] as [JobHead_JobClosed],
[JobHead].[PersonID] as [JobHead_PersonID],
[JobHead].[PartNum] as [JobHead_PartNum],
[JobHead].[JobNum] as [JobHead_JobNum],
[JobHead].[JobEngineered] as [JobHead_JobEngineered],
[JobHead].[JobReleased] as [JobHead_JobReleased],
[JobHead].[JobFirm] as [JobHead_JobFirm],
[JobHead].[StartDate] as [JobHead_StartDate],
[SubQuery3].[Calculated_OrderQty] as [Calculated_OrderQty],
[JobHead].[ProdQty] as [JobHead_ProdQty],
[JobHead].[Company] as [JobHead_Company]
from Erp.JobHead as JobHead
inner join Erp.Part as Part on
JobHead.Company = Part.Company
and JobHead.PartNum = Part.PartNum
left outer join (select
[OrderRel].[Company] as [OrderRel_Company],
[OrderRel].[PartNum] as [OrderRel_PartNum],
(sum(ISNULL(OrderRel.OurReqQty, 0))) as [Calculated_OrderQty],
[OrderRel].[OpenRelease] as [OrderRel_OpenRelease]
from Erp.OrderRel as OrderRel
inner join Erp.OrderHed as OrderHed on
OrderRel.Company = OrderHed.Company
and OrderRel.OrderNum = OrderHed.OrderNum
and ( OrderHed.OrderHeld = False )
where (OrderRel.OpenRelease = true and OrderRel.ReqDate <= dateadd (week, 4, Constants.Today))
group by [OrderRel].[Company],
[OrderRel].[PartNum],
[OrderRel].[OpenRelease]) as SubQuery3 on
SubQuery3.OrderRel_Company = Part.Company
and SubQuery3.OrderRel_PartNum = Part.PartNum
where (JobHead.JobClosed = False)
order by JobHead.StartDate, JobHead.JobNum, JobHead.PartNum
ok, so, it’s that you are using a left join, and there is literally no row. (it’s not that the field is blank). So do what @JasonMcD suggested. You’ll have do do the left join in a subquery, this will give you the empty field, then in a level up, use the calculated field so that there is a row that’s populated.
Does that make sense?
Right, my knee-jerk reaction to this problem is always to do a “wrapper” subquery.
I don’t know if the inline strategy works - I feel like it has for me before. But if not, make a wrapper and call it a day.
I think that’s a more SQL-ese way to do it anyway (by wrapping).
Like, if you ever have to convert the BAQ to true SQL (i.e. copy and paste into SSMS), you’ll often have to avoid these shortcuts anyway (like nested calculated fields in a single subquery). Also, true = 1 and false = 0.
I’m thinking you can use a simple sum in SubQuery3 and use the ISNULL in the the TopLevel query?
i.e. thinking something like this (except the subquery name is “SORelQtys” in this example)
1 Like
I use the following in my BAQ calculated fields:
case when OrderRel.OurReqQty IS NULL then 0 else OrderRel.OurReqQty end