I have a BAQ, it finds customers with no orders, then for these customers it finds their quotes, and then gives the win/lose reason code from the task tree. If I delete block 4 (the task/reason) it works. Or if I change the join from 3 to 4 to in inner join it works, but then quotes without a task completed do not appear in the results.
Analyze finds no problems, it only gives a “Bad SQL” error when you actually run it. I have attached the actual BAQ and screenshots.
/*
* 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
[Customer].[CustID] as [Customer_CustID],
[Customer].[CustNum] as [Customer_CustNum],
[Customer].[Name] as [Customer_Name],
[Customer].[EstDate] as [Customer_EstDate],
[QuoteDtl].[LastUpdate] as [QuoteDtl_LastUpdate],
[QuoteDtl].[QuoteNum] as [QuoteDtl_QuoteNum],
[QuoteDtl].[ProdCode] as [QuoteDtl_ProdCode],
[QuoteDtl].[PartNum] as [QuoteDtl_PartNum],
[QuoteDtl].[SellingExpectedQty] as [QuoteDtl_SellingExpectedQty],
[QuoteDtl].[ExtPriceDtl] as [QuoteDtl_ExtPriceDtl],
[QuoteDtl].[Discount] as [QuoteDtl_Discount],
[QuoteDtl].[LineDesc] as [QuoteDtl_LineDesc]
from Erp.Customer as Customer
left outer join (select
[OrderHed1].[CustNum] as [OrderHed1_CustNum],
[OrderHed1].[OrderNum] as [OrderHed1_OrderNum],
(count(OrderHed1.OrderNum)) as [Calculated_OrderCount]
from Erp.OrderHed as OrderHed1
group by [OrderHed1].[CustNum],
[OrderHed1].[OrderNum]) as SubQuery2 on
Customer.CustNum = SubQuery2.OrderHed1_CustNum
left outer join Erp.QuoteDtl as QuoteDtl on
Customer.Company = QuoteDtl.Company
and Customer.CustNum = QuoteDtl.CustNum
left outer join (select
[Task1].[Conclusion] as [Task1_Conclusion],
[Task1].[SalesRepCode] as [Task1_SalesRepCode],
[Reason1].[Description] as [Reason1_Description],
[Task1].[Key1] as [Task1_Key1]
from Erp.Task as Task1
inner join Erp.Reason as Reason1 on
Task1.Company = Reason1.Company
and Task1.ReasonCode = Reason1.ReasonCode
where (Task1.Conclusion <> '')) as SubQuery3 on
QuoteDtl.QuoteNum = SubQuery3.Task1_Key1
where (Customer.EstDate >= @StartDate and Customer.EstDate <= @EndDate)
and (SubQuery2.OrderHed1_CustNum is null)
@josecgomez hey look same problem we just had! But wait… the customer table is involved in this one too, coincidence? Perhaps not!
We just troubleshot a query where if you joined InvcHead to Customer via Company and CustNum the query would time out and say “Bad SQL” remove the customer table all good. Funny thing that’s a native index so nothing weird there. Not sure if Jose ever found out exactly what was going wrong, but running the query from the Execution Plan or the Designer window it was fine, as the BAQ not fine.
Bah, why didn’t I think of that. The last 2 queries I was stuck on for a long time were also crashing because of Company. Deleted all company from the joins and it works fine now.
geez I wonder how many of my BAQs would break on 10.2.500.2 if I had this bug I thought Epicor used ATE and Unit Tests to test each build, but we often find non-trivial bugs that could have been avoided (yeah I know im blaming .2 it could also be Data specific).
I think I might have found another problem? This super simple one also gives a bad SQL error and it has no join with customer…
select
[QuoteHed].[QuoteNum] as [QuoteHed_QuoteNum],
[Task].[TaskID] as [Task_TaskID],
[Task].[TaskSeqNum] as [Task_TaskSeqNum],
[Task].[TaskDescription] as [Task_TaskDescription],
[Task].[Conclusion] as [Task_Conclusion],
[Task].[ReasonCode] as [Task_ReasonCode]
from Erp.QuoteHed as QuoteHed
inner join Erp.Task as Task on
QuoteHed.QuoteNum = Task.Key1
Query returned 1235 row(s).
Query has no more records to return.
Query execution total time: 287.0886 ms.
select
[QuoteHed].[QuoteNum] as [QuoteHed_QuoteNum],
[Task].[TaskID] as [Task_TaskID],
[Task].[TaskSeqNum] as [Task_TaskSeqNum],
[Task].[TaskDescription] as [Task_TaskDescription],
[Task].[Conclusion] as [Task_Conclusion],
[Task].[ReasonCode] as [Task_ReasonCode]
from Erp.QuoteHed as QuoteHed
inner join Erp.Task as Task on
QuoteHed.QuoteNum = Task.Key1
select
[Task].[TaskID] as [Task_TaskID],
[Task].[TaskSeqNum] as [Task_TaskSeqNum],
[Task].[TaskDescription] as [Task_TaskDescription],
[Task].[Conclusion] as [Task_Conclusion],
[Task].[ReasonCode] as [Task_ReasonCode]
from Erp.Task as Task
inner join (select
(convert(varchar, QuoteHed.QuoteNum)) as [Calculated_calcq],
[QuoteHed].[QuoteNum] as [QuoteHed_QuoteNum]
from Erp.QuoteHed as QuoteHed
group by [QuoteHed].[QuoteNum]) as SubQuery2 on
Task.Key1 = SubQuery2.Calculated_calcq
Is that how you normally cast it?
But the BAQ I had in OP works without the casting. Very strange… so sometimes you have to cast and other times you do not.
I get that part, but if Key1 is actually a string why would it ever work without casting (not counting string ↔ string joins)? For example in my original BAQ I had:
And it works, but the second BAQ I posted, I used the same fields and it did not work, until casting was added. That was the cherry on top as far as confusion goes.
Task.Key1 is NVarchar(50) and QuoteHed.QuoteNum is Numeric.
So you need to cast the QuoteNum as NVarchar(50) to make it similar to Task.Key1
On your BAQ you should have something like this:
select
[QuoteHed].[QuoteNum] as [QuoteHed_QuoteNum],
[Task].[TaskID] as [Task_TaskID],
[Task].[TaskSeqNum] as [Task_TaskSeqNum],
[Task].[TaskDescription] as [Task_TaskDescription],
[Task].[Conclusion] as [Task_Conclusion],
[Task].[ReasonCode] as [Task_ReasonCode]
from Erp.QuoteHed as QuoteHed
inner join Erp.Task as Task on
Task.Key1 = Convert(NVARCHAR(50), QuoteNum)
So on the inner join definition, add the following expression: Convert(NVARCHAR(50), QuoteNum)
I thought Epicor used ATE and Unit Tests to test each build, but we often find non-trivial bugs that could have been avoided (yeah I know im blaming .2 it could also be Data specific).
