Brandon, Do you want to see the BAQ Export or the Query Phrase?
This will fail if something is tripping it up.
Can you try tryparse()
CASE WHEN TRY_PARSE(LastTask.Key1 AS int USING 'en-US') IS NULL
THEN '0'
ELSE convert(int,LastTask.Key1 )
https://learn.microsoft.com/en-us/sql/t-sql/functions/try-parse-transact-sql?view=sql-server-ver16
1 Like
Query phrase is fine.
/*
* 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
[QuoteHed].[QuoteNum] as [QuoteHed_QuoteNum],
[QuoteHed].[EntryDate] as [QuoteHed_EntryDate],
[QuoteHed].[Reference] as [QuoteHed_Reference],
[Customer].[CustID] as [Customer_CustID],
[Customer].[Name] as [Customer_Name],
(iif(QuoteHed.UseOTS = 'true', 'OTS End User', EndUserCust.CustID)) as [Calculated_EndUserID],
(iif(QuoteHed.UseOTS = 'true', QuoteHed.OTSName, EndUserCust.Name)) as [Calculated_EndUserName],
(iif(QuoteHed.UseOTS = 'true', QuoteHed.OTSState, ShipTo.State)) as [Calculated_EndUserState],
(iif(QuoteHed.UseOTS = 'true', OTS_EndUserCounty.Description, Country.Description)) as [Calculated_EndUserCountry],
[QuoteDtl].[PartNum] as [QuoteDtl_PartNum],
[QuoteDtl].[LineDesc] as [QuoteDtl_LineDesc],
[QSalesRP].[Name] as [QSalesRP_Name],
[Task_Last].[TaskDescription] as [Task_Last_TaskDescription],
(QuoteHed.DocTotalPotential + QuoteHed.DocTotalMiscAmt + QuoteHed.DocTax) as [Calculated_TotalQuote_calc],
[QuoteDtl].[Quoted] as [QuoteDtl_Quoted],
[SalesCat].[Description] as [SalesCat_Description],
[QuoteDtl].[Industry_c] as [QuoteDtl_Industry_c],
[Task_Last].[Conclusion] as [Task_Last_Conclusion],
[OrderDtl].[OrderNum] as [OrderDtl_OrderNum],
(iif(Task_Last.Conclusion = 'WIN', Reason1.Description, iif(Task_Last.Conclusion = 'LOSE', Reason.Description, ''))) as [Calculated_WinLoseReason],
[MktgCamp].[CampDescription] as [MktgCamp_CampDescription],
[MktgEvnt].[EvntDescription] as [MktgEvnt_EvntDescription],
[QuoteDtl].[XPartNum] as [QuoteDtl_XPartNum],
[ProdGrup].[Description] as [ProdGrup_Description],
[QSalesRPAgent].[Name] as [QSalesRPAgent_Name],
[QuoteHed].[ExpectedClose] as [QuoteHed_ExpectedClose],
[QuoteCnt].[Name] as [QuoteCnt_Name],
[Task_Last].[DueDate] as [Task_Last_DueDate],
[QuoteHed].[CurrentStage] as [QuoteHed_CurrentStage],
(iif(QuotesWithLeads.Calculated_LastLeadTaskSeqNum > 0, 1, 0)) as [Calculated_StartedAsLead],
[LeadConversionDate].[CreateDate] as [LeadConversionDate_CreateDate],
[QuoteHed].[QuoteRisk_c] as [QuoteHed_QuoteRisk_c],
[QuoteHed].[QuoteRiskNotes_c] as [QuoteHed_QuoteRiskNotes_c],
[Customer].[CustNum] as [Customer_CustNum]
from Erp.QuoteHed as QuoteHed
inner join Erp.QuoteDtl as QuoteDtl on
QuoteHed.Company = QuoteDtl.Company
and QuoteHed.QuoteNum = QuoteDtl.QuoteNum
and ( QuoteDtl.QuoteLine = 1 and not QuoteDtl.SalesCatID = 'PART' and QuoteDtl.SalesCatID = @SalesCategory )
left outer join Erp.SalesCat as SalesCat on
QuoteDtl.Company = SalesCat.Company
and QuoteDtl.SalesCatID = SalesCat.SalesCatID
left outer join Erp.OrderDtl as OrderDtl on
QuoteDtl.Company = OrderDtl.Company
and QuoteDtl.QuoteNum = OrderDtl.QuoteNum
and ( OrderDtl.OrderLine = 1 )
inner join Erp.ProdGrup as ProdGrup on
QuoteDtl.Company = ProdGrup.Company
and QuoteDtl.ProdCode = ProdGrup.ProdCode
inner join Erp.Customer as Customer on
QuoteHed.Company = Customer.Company
and QuoteHed.CustNum = Customer.CustNum
inner join Erp.QSalesRP as QSalesRP on
QuoteHed.Company = QSalesRP.Company
and QuoteHed.QuoteNum = QSalesRP.QuoteNum
and ( QSalesRP.PrimeRep = TRUE and QSalesRP.SalesRepCode = @PrimarySales )
left outer join Erp.Customer as EndUserCust on
QuoteHed.Company = EndUserCust.Company
and QuoteHed.ShipToCustNum = EndUserCust.CustNum
left outer join Erp.ShipTo as ShipTo on
QuoteHed.Company = ShipTo.Company
and QuoteHed.ShipToCustNum = ShipTo.CustNum
and QuoteHed.ShipToNum = ShipTo.ShipToNum
left outer join Erp.Country as Country on
ShipTo.Company = Country.Company
and ShipTo.CountryNum = Country.CountryNum
left outer join Erp.Country as OTS_EndUserCounty on
OTS_EndUserCounty.Company = QuoteHed.Company
and OTS_EndUserCounty.CountryNum = QuoteHed.OTSCountryNum
left outer join Erp.MktgCamp as MktgCamp on
QuoteHed.Company = MktgCamp.Company
and QuoteHed.MktgCampaignID = MktgCamp.MktgCampaignID
left outer join Erp.MktgEvnt as MktgEvnt on
QuoteHed.Company = MktgEvnt.Company
and QuoteHed.MktgCampaignID = MktgEvnt.MktgCampaignID
and QuoteHed.MktgEvntSeq = MktgEvnt.MktgEvntSeq
inner join (select
[LastTask].[Company] as [LastTask_Company],
[LastTask].[Key1] as [LastTask_Key1],
(MAX(LastTask.TaskSeqNum)) as [Calculated_LTaskSeq]
from Erp.Task as LastTask
group by [LastTask].[Company],
[LastTask].[Key1]) as LastTask1 on
QuoteHed.Company = LastTask1.LastTask_Company
and QuoteHed.QuoteNum = LastTask1.LastTask_Key1
inner join Erp.Task as Task_Last on
LastTask1.LastTask_Company = Task_Last.Company
and LastTask1.LastTask_Key1 = Task_Last.Key1
and LastTask1.Calculated_LTaskSeq = Task_Last.TaskSeqNum
and ( Task_Last.Conclusion = @WinLostOpen and Task_Last.TaskID = @CurrentTask )
left outer join Erp.Reason as Reason on
Task_Last.Company = Reason.Company
and Task_Last.ReasonCode = Reason.ReasonCode
and ( Reason.ReasonType = 'L' )
left outer join Erp.Reason as Reason1 on
Task_Last.Company = Reason1.Company
and Task_Last.ReasonCode = Reason1.ReasonCode
and ( Reason1.ReasonType = 'W' )
left outer join Erp.QSalesRP as QSalesRPAgent on
QuoteHed.Company = QSalesRPAgent.Company
and QuoteHed.QuoteNum = QSalesRPAgent.QuoteNum
and ( QSalesRPAgent.RoleCode = 'AGENT' )
left outer join Erp.QuoteCnt as QuoteCnt on
QuoteHed.Company = QuoteCnt.Company
and QuoteHed.QuoteNum = QuoteCnt.QuoteNum
and ( QuoteCnt.PrimeContact = 1 )
left outer join (select
[Task].[Company] as [Task_Company],
[Task].[TaskQuoteNum] as [Task_TaskQuoteNum],
(max(Task.TaskSeqNum) + 10) as [Calculated_LastLeadTaskSeqNum]
from Erp.TaskSDtl as TaskSDtl
inner join Erp.Task as Task on
TaskSDtl.Company = Task.Company
and TaskSDtl.TaskSetID = Task.TaskSetID
and TaskSDtl.TaskSetSeq = Task.TaskSetSeq
where (TaskSDtl.CurrentStage = 'LEAD')
group by [Task].[Company],
[Task].[TaskQuoteNum]) as QuotesWithLeads on
QuoteHed.Company = QuotesWithLeads.Task_Company
and QuoteHed.QuoteNum = QuotesWithLeads.Task_TaskQuoteNum
left outer join Erp.Task as LeadConversionDate on
QuotesWithLeads.Task_Company = LeadConversionDate.Company
and QuotesWithLeads.Task_TaskQuoteNum = LeadConversionDate.TaskQuoteNum
and QuotesWithLeads.Calculated_LastLeadTaskSeqNum = LeadConversionDate.TaskSeqNum
where (QuoteHed.CustNum = @QuotedToCustomer and QuoteHed.ShipToCustNum = @EndUserCustomer and QuoteHed.EntryDate >= @QuoteCreateAfter and QuoteHed.EntryDate <= @QuoteCreateBefore and QuoteHed.QuoteRisk_c >= @RiskGreaterOREqual)
on this join add in a conversion to the quote num to make it a character field.
This is obviously a silly example, but you can do they joins like this for the quotenum to key 1, and you should stop getting the error. (you only need the conversion on the quote num, but you get the point)
Not sure, just grasping at straws here, maybe try extending to nvarchar(30) rather than just 8. I have no clue here.
I actually tried it at 50 with the same error.
I used 50 because the LastTask_Key1 is formatted as x(50):
Can you remove the company table temporarily out of the query? Since casting the join didnât fix it, Iâm guessing it has to do with security, and thatâs the table that triggers security.
Also, just to cover the bases here, this is NOT an updated BAQ, correct?
Hereâs another thing to try. (no idea if it will have an effect) but this is an option to use a different type of security. (grasping at straws here).
Task should have a TaskQuoteNum field in it⌠that if it were exposed in the subquery instead of Key1, that might help?
Dr. Dan nailed it!
So I changed the references to Key1 on Task in my subquery and top query and rewrote one formula.
Here is what I changed:
Originally I had this:
I changed it to this:
The first time I tried this I still got an error, so I DELETED the calculated field and recreated it with the same EXACT formula:
Then I changed the table links in the Top Query.
Originally the first link looked like this:
And I changed the first link to this:
Then I changed the second link from this:
To this:
And my BAD SQL statement error went away.
So now the question isâŚWhy does this work in Pilot but fails in LIVE?
Itâs a data issue, which can be intermittent dependent on the SQL plan. Depending on what order things are queried and pared down, it may or may not run into the bad data. A SQL can have a mind of itâs own when it makes execution plans. So since they are 2 different databases, with slightly different data, it can make a vastly different plan. Then on top of that, in the name of efficiency, it will many times hold onto the old plan and not make the new one even with vastly different parameters. Thatâs called parameter sniffing, which can be a problem.
But even if itâs not specifically parameter sniffing, the states of the indexes will cause a difference in plans as well.
For grins and giggles, can you create a new query just on key1 that tries to parse to int? (like I showed earlier in this thread). I bet you find a field in there that doesnât parse.
Do a calculated field with this.
CASE WHEN TRY_PARSE(LastTask.Key1 AS decimal USING 'en-US') IS NULL
THEN 'True'
ELSE 'False'
END
then another with
count(1)
Then group by the calculated field (so youâll only have 2 fields for the whole BAQ) and see if you get and Trues? And how many you get?
Because I think this is interesting⌠Iâm going to explain farther how a data issue could be intermittent with different SQL plans.
So letâs take this simple table as an example. Key1 is a character field but it houses numbers, usually. However notice the first record for C2 has a âAâ in the field. So that wonât translate to an integer.
| Company | Key1 |
|---|---|
| C1 | 1 |
| C1 | 5 |
| C1 | 345 |
| C1 | 345 |
| C1 | 32 |
| C1 | 6634 |
| C1 | 663 |
| C2 | A19685 |
| C2 | 1 |
| C2 | 51 |
| C2 | 635 |
| C2 | 185 |
| C2 | 156 |
| C2 | 23465 |
| C2 | 634 |
So say Iâm making a query, where Iâm only looking at Company C1 data. I see there are no Characters in the fields for C1 so I should be able to translate to ints no problem.
select convert(key1 as int) from myTable
where Company = "C1"
This will pare down the table to just the C1 records.
| Company | Key1 |
|---|---|
| C1 | 1 |
| C1 | 5 |
| C1 | 345 |
| C1 | 345 |
| C1 | 32 |
| C1 | 6634 |
| C1 | 663 |
Now lets say I want only the key1.
select convert(key1 as int) from myTable
where Company = "C1" and Key1 = 1
This is what we expect in the end, but it doesnât get there in one step.
| Company | Key1 |
|---|---|
| C1 | 1 |
If I do company, then key
step 1
| Company | Key1 |
|---|---|
| C1 | 1 |
| C1 | 5 |
| C1 | 345 |
| C1 | 345 |
| C1 | 32 |
| C1 | 6634 |
| C1 | 663 |
| step 2 |
| Company | Key1 |
|---|---|
| C1 | 1 |
We are ok when the program tries to do it in this order.
But, while most programming languages will do the thing in the order they are typed, SQL tries to optimize and thinks itâs smarter than you, so it might not do it in that order. It might try to filter based on the key1 value before it limits my Company.
step 1.
| Company | Key1 |
|---|---|
| C1 | 1 |
| C2 | 1 |
To do that, it will read all of the key1 values and try to compare it to an integer, and the first record in C2 will puke, because it canât convert that to an integer to compare the 2.
In this simple example, you would simply put single quotes around the 1, but if you are doing the limiting by joins, then it canât do that.
Why does it do what it does? Iâm not educated enough to definitively say, but itâs affected by index statistics, and prior queries run. And the things that affect them are very touchy and can give you wildly varying plans with seemingly inconsequential changes. So itâs quite likely that youâll get different results with different databases.
3 Likes
Brandon,
I got Zero True.
I love to learn something new, so thank you for your input on this.
Damn, I was sure it was that.
I still think itâs a data issue somewhereâŚ
If we go back to the very first post where this is the error, I still kind of think that it has to do with security.
And this âFixâ could simply be a new plan since you changed enough that SQL is doing it differently and doesnât run into the problem.
Frustrating though when we canât find the murder weapon.
WAITâŚI think I did something wrongâŚIt may have one True.
1 Like
See if you can find it. Let me know if you need help making the query to find it.
I need help finding itâŚI am not good with the Group ByâŚ
Remove the group by and the count field. Bring in your key field, and whatever fields you need to identify the record. Then make that an inner sub query, make a new top level, bring in the subquery and filter the âTableâ but the calculated field to only show âTrueâ.
Edit: or even easier, just replace âTrueâ with the key field in your calculated field. Then you donât have to change anything else.
