We have a relatively complex UBAQ that we’ve been running daily for almost 2 years, first in 10.2.100 and then in 10.2.700. It has a long list of inner subqueries and in isolation, each seems to work.
Mostly, everything else in the upgrade works, with a few bugs I’ve had to solve but nothing serious.
Anyone come across this before?
Its “PseudoSQL” pasted into SSMS runs flawlessly in half a second or so.
Syntax checker returns happiness
It has two directives, both calling a code widget. BOth have been disabled for testing and that makes no change.
There are some UD fields with permission settings, so a warning about those fields is normal and unchanged
Running “Get List” causes the buttons to grey out; the status bar proclaims “Executing Query” for an instant, and then goes blank; the field permission warning opens; upon closing the warning, nothing else changes until eventually the query times out.
If I unset “Updatable” and run “Test” instead, the only difference appears to be that upon clearing the field permission warning the status bar keeps showing “Executing Query” until the query times out.
I’ve tried increasing the time limit very high; nothing changes except the dely before failing.
Did you try the QueryOldCompanySecurity setting? I had some BAQs with similar problems after upgrading to 2021.2 and that fixed it.
In the BAQ, Actions > Execution Settings, then Add New
Under Setting Value, type True
Paste QueryOldCompanySecurity into Setting Name
Change setting value to True again
Check “Persist on Query” and save
No its a setting in the BAQ - you have to add it under Execution settings, I put the step by step in the previous reply because its kind of quirky to get it to stick properly.
I wouldn’t start from scratch. Copy the query and delete one table at a time until it responds appropriately then start focusing in on that specifically.
You can write final SQL statement into the server log and go from there
Also QueryOldCompanySecurity now have different name, as they show better result some time.
Need to search for name, but something “alternate” company security…
So, interesting. I can isolate the subqueries by setting the “TopLevel” query to “InnerSubQuery” and then setting the subquery I want to “TopLevel”.
All of them work. I’m documenting them here in the hopes someone will spot something that’s known to not work, and because I’ll know where to find it later…
I rebuilt the TopLevel query with no fields and started adding them one at a time, and found one that fails. It’s a member of a subquery “PartList” though, and works fine in there. In “PartList”, it looks like this:
case
when TimePhase.Calculated_MinOnHandBeforeNextTO < 0.0 then
-1.0 * TimePhase.Calculated_MinOnHandBeforeNextTO
else 0.0
end
And “TimePhase” also runs fine in isolation. The field it’s providing looks like this in its native habitat:
min(DailyTimePhase.Calculated_OnHandAtDate)
So again, not rock surgery.
That one looks like this:
OnHand + SUM(Supply - Demand) OVER (PARTITION BY PartDtl1.Plant, PartDtl1.PartNum ORDER BY PartDtl1.Plant, PartDtl1.PartNum, PartDtl1.DueDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
And “Supply” and “Demand” respectively:
SUM(
CASE
WHEN PartDtl1.RequirementFlag = 0 THEN PartDtl1.Quantity
ELSE 0
END
)
and
SUM(
CASE
WHEN PartDtl1.RequirementFlag = 1 THEN PartDtl1.Quantity
ELSE 0
END
)
I’m trying to figure out if any of the calculations we used are deprecated.
Ah yes I noticed another one in there, but didn’t try it in case it was touchy. Many thanks!
Sorry for my ignorance - how do you do that? I’m not getting anything in the server log until I kill the execution, and then just a “it’s dead, Jim” error, or something like that.