- Home
- LLBLGen Pro
- LLBLGen Pro Runtime Framework
Overriding OpenConnection in adapter in v5.8
Joined: 25-Oct-2005
We override DataAccessAdapterCore.OpenConnection in our adapters but when I've attempted to upgrade from 5.7 to 5.8 that override sometimes doesn't get called (e.g on save) since OpenConnection is now delegated to TransactionBase. Is there any workaround to enable our code to always handle OpenConnection as per 5.7?
Joined: 28-Nov-2005
Hi Tom,
The connection is opened when needed, not in every call. So, maybe that's why you see it to happen sometimes. Are you sure this was different on v5.7?
Why are you overriding that method? What do you need to do?
Joined: 25-Oct-2005
daelmo wrote:
Are you sure this was different on v5.7?
Yes, I've looked at your source code. We have unit tests that fail for 5.8 but pass for 5.7
daelmo wrote:
Why are you overriding that method? What do you need to do?
We are setting DB credentials. Specifically we catch authentication exceptions and retry with generated credentials. Point is this has been working fine for years.
Joined: 17-Aug-2003
It's CreateConnection you're after, not OpenConnection? As OpenConnection is a virtual method in DataAccessAdapterCore that's always called. (also in the case of SaveEntity for instance, and it's the only method that calls transactionadapter.openconnection). CreateConnection is delegated to TransactionAdapter but it uses a lambda which calls adapter.CreateNewPhysicalConnection (which is the virtual method you have to override if you need custom code when creating a connection).
For OpenConnection, please provide a repro case that doesn't open the connection through adapter.OpenConnection. A piece of code that performs an action that will bypass adapter.openconnection is enough.
We refactored a lot of code in the adapter class into classes which are now shared among selfservicing and adapter to simplify code. We went to great lengths to make sure the protected/public API stayed as-is, but perhaps we missed a spot.
Joined: 17-Aug-2003
I think I've spotted it. If StartTransaction is required to be called (or you call it manually) it's bypassing OpenConnection as it directly calls StartTransaction on the TransactionAdapter which internally calls openconnection.
It will call OpenConnection later on again in the generic code for executing a query (but as the connection is already open it's a no-op), however for your use case that's too late, as the initial openconnection already crashed in your case).
Will see if I can create a test and will likely issue a hotfix for this as it's easy to fix and shouldn't happen indeed. Stay tuned
Joined: 25-Oct-2005
Otis wrote:
We released a hotfix build for 5.8.1, which should solve the problem. Please let us know if it doesn't.
Yep that fixed that, thanks. Though now some our unit tests which rollback using System.Transactions are failing. From ORM profiler it looks like not everything in a particular unit test is using the same System.Transactions anymore
Filename | File size | Added on | Approval |
---|---|---|---|
Snapshot_20210514090957.opsnapshot | 76,682 | 13-May-2021 23:20.03 | Approved |
Joined: 17-Aug-2003
Please provide a usable repro case. Btw, we only added an openconnection prior to starting the transaction, so these tests should fail with the older 5.8 runtime as well. Was that the case?
System.Transactions.TransactionScope use an ambient transaction, I don't know what you mean with ' the same system.transactions'
Joined: 25-Oct-2005
Otis wrote:
Please provide a usable repro case. Btw, we only added an openconnection prior to starting the transaction, so these tests should fail with the older 5.8 runtime as well. Was that the case?
System.Transactions.TransactionScope use an ambient transaction, I don't know what you mean with ' the same system.transactions'
I got that wrong, what is happening is the connection is closing and when it opens again it's not in the TransactionScope and hence things fail. I've attached ORM profiler snapshots to this (v5.8.1) and my previous post(v5.5) to show the differences between v5.8.1 and v5.5. v5.8.1 and v5.8.0 are the same.
Filename | File size | Added on | Approval |
---|---|---|---|
Snapshot_20210514091600.opsnapshot | 59,631 | 14-May-2021 00:46.16 | Approved |
Joined: 17-Aug-2003
TomDog wrote:
Otis wrote:
Please provide a usable repro case. Btw, we only added an openconnection prior to starting the transaction, so these tests should fail with the older 5.8 runtime as well. Was that the case?
System.Transactions.TransactionScope use an ambient transaction, I don't know what you mean with ' the same system.transactions'
I got that wrong, what is happening is the connection is closing and when it opens again it's not in the TransactionScope and hence things fail. I've attached ORM profiler snapshots to this (v5.8.1) and my previous post(v5.5) to show the differences between v5.8.1 and v5.5. v5.8.1 and v5.8.0 are the same.
Like I said, please provide a usable repro case or at least code that shows what you're doing. "when it is closed and when it is reopend" can be done in a lot of different ways. Which transaction scope a connection is enlisted is up to the ado.net provider, the only thing we do (since the beginning) with transaction scope is what we should do: when an ambient transaction is present (provided by the transaction scope) we enlist the transaction with it and the adapter has code to tell the scope when a transaction rolled back/committed and postpones the final commit to the point when the transaction scope signals all participants the transaction committed (or rolled back). Our tests with these work fine.
Keep in mind that debugging tehse things could alter the behavior you might expect. For instance, with sql server a single connection won't get enlisted in a transaction scope due to an optimization but there are debugging scenarios thinkable where a second connection is opened and both then get enlisted.
Joined: 25-Oct-2005
Walaa wrote:
I couldn't spot the differences between both snapshots shared. Both have the exact same connections (compared by the queries inside) enlisted in the same DTC.
The 3 statements in Connection #5 in the .57 snapshot after 'INSERT INTO [AQD].[qaoc_Finding_Log]' are in the a different connection in the .00 snapshot. I.e. in the .00 snapshot 'INSERT INTO [AQD].[qaoc_Finding_Log]' is in Connection #3 but the next statement is in Connection #4.
I don't know when I'll find time to repro this but basically the code writes a record then reads it but the read fails as the transaction ended with the connection closing. In previous versions of LLBL the connection stays open.
Joined: 25-Oct-2005
Otis wrote:
So you're saying: after a rollback the connection is closed while in previous versions the connection was kept open?
Pseudo code:
[TestMethod]
public void SomeCreateReadTest()
{
using (new TransactionScope())
{
var adapter = new DataAccessAdapter(){KeepConnectionOpen = true};
var id = SomeCodeThatCreatesAndSavesAnEntity(adapter );
// In v5.8 the connection is now closed while previously it stayed open
SomeCodeThatReadsThatEntity(adapter ,id).Should().BeTrue(); // In v5.8 this is no longer in Transaction
}
}
Joined: 17-Aug-2003
And you share an adapter? This isn't really helpful, please post the test code so we can see what's going on, we otherwise have to fill in the details which matter. For instance, in the code you show, the adapter used in the first method closes the connection, the adapter used in the second method opens a new one. This is logical and would have been done in previous versions too, so there's way more detail needed to fix this. If you share an adapter then things are different, but I can't see that from the pseudocode you posted.
It's also the question if there's something that needs fixing, I still don't know what it is that's actually wrong. That some unit test fails doesn't mean the actual API is buggy
Joined: 25-Oct-2005
Otis wrote:
And you share an adapter?
Yes, I've edited the Pseudo code above to be indicate that. This test against Northwind works fine in v5.8 though the KeepConnectionOpen seems to be ignored while in earlier version it's all done in 1 connection.
[TestMethod]
public void CreateReadEntityTest()
{
using (new TransactionScope())
{
var northwindLinqMetaData = new LinqMetaData(new DataAccessAdapter())
{
AdapterToUse = {KeepConnectionOpen = true}
};
var product = new ProductEntity {ProductName = "ProductName"};
northwindLinqMetaData.AdapterToUse.SaveEntity(product, true).Should().BeTrue();
var productEntity = northwindLinqMetaData.Product.FirstOrDefault(p => p.ProductId == product.ProductId);
productEntity.Should().NotBeNull();
}
}
Joined: 17-Aug-2003
Reproduced indeed. https://imgur.com/6otcdTz
Connection 7 and 8 are in 5.8, connection 9 is same test but on 5.7. Looking into it
(edit) bug. In TrasnactionBase.Commit it calls Reset, but in Reset it always closes the connection.
Joined: 25-Oct-2005
Otis wrote:
Fixed. See the hotfix build for 5.8.2
Yep sorted it thanks but I've still got one failing test so not quite out of the woods yet. Completely unrelated to above obviously but getting this against Oracle in 5.8 - ORA-00904: "LPA_L1"."ActionPriorityLevelActionPriorityLevelSeqNo": invalid identifier.
SELECT *
FROM (SELECT "LPA_L1"."Action",
"LPA_L1"."ActionID",
"LPA_L1"."ActionPriorityLevelActionPriorityLevel",
"LPA_L1"."ActionPriorityLevelActionPriorityLevelSeqNo",
"LPA_L1"."ActionIDSortable",
"LPA_L1"."ActionNo",
"LPA_L1"."ActionResponseText",
"LPA_L1"."ActionStatus",
"LPA_L1"."ActionStatusId",
"LPA_L1"."ActionType",
"LPA_L1"."AssignedTo",
"LPA_L1"."CauseNo",
"LPA_L1"."CompletedOn",
"LPA_L1"."ContactPerson",
"LPA_L1"."Department",
"LPA_L1"."DueDate",
"LPA_L1"."ExtActionReference",
"LPA_L1"."ExtAuditReference",
"LPA_L1"."WRNumber",
"LPA_L1"."WRPeriod",
"LPA_L1"."WRType",
"LPA_L1"."FindingCategory",
"LPA_L1"."FindingID",
"LPA_L1"."FindingNo",
"LPA_L1"."IsActionRequest",
"LPA_L1"."Likelihood",
"LPA_L1"."RecheckDue",
"LPA_L1"."EnteredById",
"LPA_L1"."RegisteredOn",
"LPA_L1"."ReplacementAction",
"LPA_L1"."ReplacementActionID",
"LPA_L1"."RiskCauseNo",
"LPA_L1"."RiskNo",
"LPA_L1"."Severity",
"LPA_L1"."StaffMemberName",
"LPA_L1"."TrackedBy",
"LPA_L1"."WRID",
"LPA_L1"."LPFA_28",
"LPA_L1"."SourceGroupRole",
"LPA_L1"."SourceRole",
"LPA_L1"."Overdue",
"LPA_L1"."ApprovalDefinitionID",
"LPA_L1"."RejectedApprovalDefinitionID",
"LPA_L1"."ResponsibleDepartmentID"
FROM (SELECT "LPA_L3"."ACTION" AS "Action",
"LPA_L3"."ACTION_ID" AS "ActionID",
"LPA_L4"."ACTION_PRIORITY_LEVEL" AS "F__1830505937",
"LPA_L4"."SEQNO" AS "F__1143628958",
"LPA_L3"."ACTION_ID_SORTABLE" AS "ActionIDSortable",
"LPA_L3"."ACTION_NO" AS "ActionNo",
"LPA_L3"."ACTION_RESPONSE_TEXT" AS "ActionResponseText",
"LPA_L3"."ACTION_STATUS_TEXT" AS "ActionStatus",
"LPA_L3"."ACTION_STATUS" AS "ActionStatusId",
"LPA_L5"."ACTION_TYPE" AS "ActionType",
"LPA_L3"."ASSIGNED_TO" AS "AssignedTo",
"LPA_L3"."CAUSE_NO" AS "CauseNo",
"LPA_L3"."COMPLETED_ON" AS "CompletedOn",
"LPA_L3"."CONTACT_PERSON" AS "ContactPerson",
"LPA_L3"."DEPARTMENT" AS "Department",
"LPA_L3"."DUE_DATE" AS "DueDate",
"LPA_L3"."EXT_ACTION_REFERENCE" AS "ExtActionReference",
"LPA_L6"."EXT_AUDIT_REFERENCE" AS "ExtAuditReference",
"LPA_L6"."WR_NUMBER" AS "WRNumber",
"LPA_L6"."WR_PERIOD" AS "WRPeriod",
"LPA_L6"."WR_TYPE" AS "WRType",
"LPA_L7"."FINDING_CATEGORY" AS "FindingCategory",
"LPA_L3"."FINDING_ID" AS "FindingID",
"LPA_L6"."FINDING_NO" AS "FindingNo",
"LPA_L3"."IS_ACTION_REQUEST" AS "IsActionRequest",
"LPA_L9"."GLOBAL_LIKELIHOOD" AS "Likelihood",
"LPA_L3"."RECHECK_DUE" AS "RecheckDue",
"LPA_L3"."REGISTERED_BY_ID" AS "EnteredById",
"LPA_L3"."REGISTERED_ON" AS "RegisteredOn",
"LPA_L10"."ACTION_NO" AS "ReplacementAction",
"LPA_L3"."REPLACEMENT_ACTION_ID" AS "ReplacementActionID",
"LPA_L3"."RISK_CAUSE_NO" AS "RiskCauseNo",
"LPA_L3"."RISK_NO" AS "RiskNo",
"LPA_L11"."GLOBAL_SEVERITY" AS "Severity",
"LPA_L3"."REGISTERED_BY_NAME" AS "StaffMemberName",
"LPA_L3"."TRACKED_BY" AS "TrackedBy",
"LPA_L3"."WRID",
1 AS "LPFA_28",
"LPA_L2"."StaffRole" AS "SourceGroupRole",
Trim((SELECT "LPA_L15"."STAFF_ROLE" AS "StaffRole"
FROM ((("AQD"."QAOC_WORK_REQUEST_VIEW" "LPA_L12"
RIGHT JOIN "AQD"."QAOC_FINDING" "LPA_L13"
ON "LPA_L12"."WR_NUMBER" = "LPA_L13"."WR_NUMBER"
AND "LPA_L12"."WR_PERIOD" = "LPA_L13"."WR_PERIOD"
AND "LPA_L12"."WR_TYPE" = "LPA_L13"."WR_TYPE")
INNER JOIN "AQD"."QAOC_WR_STAFF" "LPA_L14"
ON "LPA_L12"."WR_NUMBER" = "LPA_L14"."WR_NUMBER"
AND "LPA_L12"."WR_PERIOD" = "LPA_L14"."WR_PERIOD"
AND "LPA_L12"."WR_TYPE" = "LPA_L14"."WR_TYPE")
LEFT JOIN "AQD"."QAOC_WR_STAFF_ROLE" "LPA_L15"
ON "LPA_L15"."STAFF_ROLE_ID" = "LPA_L14"."STAFF_ROLE_ID")
WHERE (((("LPA_L13"."FINDING_ID" = "LPA_L3"."FINDING_ID")
AND ("LPA_L14"."STAFF_MEMBER" = 136 /* :p3 */))))
AND rownum <= 1)
|| ' ' /* :p2 */
|| "LPA_L2"."StaffRole") AS "SourceRole",
CASE
WHEN CASE
WHEN ((("LPA_L3"."ACTION_STATUS" <> 8 /* :p4 */)
AND ("LPA_L3"."ACTION_STATUS" <> 4 /* :p5 */))
AND ("LPA_L3"."DUE_DATE" < '20210518' /* :p6 */)) THEN 1
ELSE 0
END = 1 THEN 3 /* :p8 */
ELSE
CASE
WHEN CASE
WHEN (((("LPA_L3"."ACTION_STATUS" <> 8 /* :p9 */)
AND ("LPA_L3"."ACTION_STATUS" <> 4 /* :p10 */))
AND ("LPA_L3"."DUE_DATE" < '20210519' /* :p11 */))
OR ((("LPA_L3"."ACTION_STATUS" <> 8 /* :p12 */)
AND ("LPA_L3"."ACTION_STATUS" <> 4 /* :p13 */))
AND ("LPA_L3"."DUE_DATE" < '20210525' /* :p14 */))) THEN 1
ELSE 0
END = 1 THEN 2 /* :p16 */
ELSE
CASE
WHEN CASE
WHEN (((("LPA_L3"."ACTION_STATUS" <> 8 /* :p17 */)
AND ("LPA_L3"."ACTION_STATUS" <> 4 /* :p18 */))
AND ("LPA_L3"."DUE_DATE" < '20210526' /* :p19 */))
OR ((("LPA_L3"."ACTION_STATUS" <> 8 /* :p20 */)
AND ("LPA_L3"."ACTION_STATUS" <> 4 /* :p21 */))
AND ("LPA_L3"."DUE_DATE" < '20210601' /* :p22 */))) THEN 1
ELSE 0
END = 1 THEN 1 /* :p24 */
ELSE 0 /* :p26 */
END
END
END AS "Overdue",
(SELECT "LPLA_18"."APPROVAL_DEFINITION_ID" AS "ApprovalDefinitionID"
FROM "AQD"."APP_APPROVAL" "LPLA_18"
WHERE (((("LPA_L3"."ACTION_ID" = "LPLA_18"."RECORD_ID")
AND ((("LPLA_18"."CURRENT_FLAG" = 1 /* :p27 */)
AND ("LPLA_18"."RECORD_TYPE" = 1 /* :p28 */))
AND EXISTS
(SELECT "LPLA_19"."STEP_STATE_ID" AS "StepStateID"
FROM "AQD"."APP_APPROVAL_STEP" "LPLA_19"
WHERE ("LPLA_18"."APPROVAL_ID" = "LPLA_19"."APPROVAL_ID"
AND ("LPLA_19"."STEP_STATE_ID" = 1 /* :p29 */)))))))
AND rownum <= 1) AS "ApprovalDefinitionID",
(SELECT "LPLA_20"."APPROVAL_DEFINITION_ID" AS "ApprovalDefinitionID"
FROM "AQD"."APP_APPROVAL" "LPLA_20"
WHERE (((("LPA_L3"."ACTION_ID" = "LPLA_20"."RECORD_ID")
AND (("LPLA_20"."CURRENT_FLAG" = 1 /* :p30 */)
AND EXISTS
(SELECT "LPLA_21"."STEP_STATE_ID" AS "StepStateID"
FROM "AQD"."APP_APPROVAL_STEP" "LPLA_21"
WHERE ("LPLA_20"."APPROVAL_ID" = "LPLA_21"."APPROVAL_ID"
AND ("LPLA_21"."STEP_STATE_ID" = 3 /* :p31 */)))))))
AND rownum <= 1) AS "RejectedApprovalDefinitionID",
"LPA_L3"."RESPONSIBLE_DEPARTMENT" AS "ResponsibleDepartmentID"
FROM ((((((((((SELECT "LPA_L21"."STAFF_ROLE" AS "StaffRole",
"LPA_L17"."FINDING_ID" AS "FindingID"
FROM ((((("AQD"."QAOC_WORK_REQUEST_VIEW" "LPA_L16"
RIGHT JOIN "AQD"."QAOC_FINDING" "LPA_L17"
ON "LPA_L16"."WR_NUMBER" = "LPA_L17"."WR_NUMBER"
AND "LPA_L16"."WR_PERIOD" = "LPA_L17"."WR_PERIOD"
AND "LPA_L16"."WR_TYPE" = "LPA_L17"."WR_TYPE")
INNER JOIN "AQD"."QAOC_WR_GROUP_ASSIGNMENT" "LPA_L18"
ON "LPA_L16"."WR_NUMBER" = "LPA_L18"."WR_NUMBER"
AND "LPA_L16"."WR_PERIOD" = "LPA_L18"."WR_PERIOD"
AND "LPA_L16"."WR_TYPE" = "LPA_L18"."WR_TYPE")
INNER JOIN "AQD"."QAOC_ASSIGNMENT_GROUP" "LPA_L19"
ON "LPA_L19"."ASSIGNMENT_GROUP_ID" = "LPA_L18"."ASSIGNMENT_GROUP_ID")
INNER JOIN "AQD"."QAOC_ASSIGNMENT_GROUP_MEMBER" "LPA_L20"
ON "LPA_L19"."ASSIGNMENT_GROUP_ID" = "LPA_L20"."ASSIGNMENT_GROUP_ID")
INNER JOIN "AQD"."QAOC_WR_STAFF_ROLE" "LPA_L21"
ON "LPA_L21"."STAFF_ROLE_ID" = "LPA_L18"."STAFF_ROLE_ID")
WHERE ((("LPA_L20"."STAFF_MEMBER_ID" = 136 /* :p32 */)))) "LPA_L2"
RIGHT JOIN "AQD"."QAOC_ACTN_VIEWSECURED" "LPA_L3"
ON "LPA_L3"."FINDING_ID" = "LPA_L2"."FindingID")
LEFT JOIN "AQD"."QAOC_ACTION_PRIORITY_LEVEL" "LPA_L4"
ON "LPA_L4"."ACTION_PRIORITY_LEVEL_ID" = "LPA_L3"."ACTION_PRIORITY_LEVEL_ID")
INNER JOIN "AQD"."QAOC_ACTION_TYPE" "LPA_L5"
ON "LPA_L5"."ACTION_TYPE_ID" = "LPA_L3"."ACTION_TYPE_ID")
LEFT JOIN "AQD"."QAOC_FINDING" "LPA_L6"
ON "LPA_L6"."FINDING_ID" = "LPA_L3"."FINDING_ID")
LEFT JOIN "AQD"."QAOC_FINDING_CATEGORY" "LPA_L7"
ON "LPA_L7"."FINDING_CATEGORY_ID" = "LPA_L6"."FINDING_CATEGORY_ID")
LEFT JOIN "AQD"."RM_RISK_MATRIX_CELL" "LPA_L8"
ON "LPA_L8"."RISK_MATRIX_CELL_ID" = "LPA_L6"."RISK_MATRIX_CELL_ID")
LEFT JOIN "AQD"."RM_GLOBAL_LIKELIHOOD" "LPA_L9"
ON "LPA_L9"."GLOBAL_LIKELIHOOD_ID" = "LPA_L8"."GLOBAL_LIKELIHOOD_ID")
LEFT JOIN "AQD"."QAOC_ACTION" "LPA_L10"
ON "LPA_L10"."ACTION_ID" = "LPA_L3"."REPLACEMENT_ACTION_ID")
LEFT JOIN "AQD"."RM_GLOBAL_SEVERITY" "LPA_L11"
ON "LPA_L11"."GLOBAL_SEVERITY_ID" = "LPA_L8"."GLOBAL_SEVERITY_ID")
WHERE (((("LPA_L3"."STAFF_MEMBER_ID" = 136 /* :p33 */))
AND (("LPA_L3"."ACTION_STATUS" IS NOT NULL)
AND ("LPA_L3"."ACTION_STATUS" IN (6 /* :p34 */, 1 /* :p35 */, 5 /* :p36 */, 4 /* :p37 */,
8 /* :p38 */, 7 /* :p39 */))))
AND ("LPA_L3"."IS_ACTION_REQUEST" = 0 /* :p40 */))) "LPA_L1"
ORDER BY "LPA_L1"."ActionID" ASC)
WHERE rownum <= 10
while in 5.7
SELECT *
FROM (SELECT "LPA_L1"."Action",
"LPA_L1"."ActionID",
"LPA_L1"."F__1830505937",
"LPA_L1"."F__1143628958",
"LPA_L1"."ActionIDSortable",
"LPA_L1"."ActionNo",
"LPA_L1"."ActionResponseText",
"LPA_L1"."ActionStatus",
"LPA_L1"."ActionStatusId",
"LPA_L1"."ActionType",
"LPA_L1"."AssignedTo",
"LPA_L1"."CauseNo",
"LPA_L1"."CompletedOn",
"LPA_L1"."ContactPerson",
"LPA_L1"."Department",
"LPA_L1"."DueDate",
"LPA_L1"."ExtActionReference",
"LPA_L1"."ExtAuditReference",
"LPA_L1"."WRNumber",
"LPA_L1"."WRPeriod",
"LPA_L1"."WRType",
"LPA_L1"."FindingCategory",
"LPA_L1"."FindingID",
"LPA_L1"."FindingNo",
"LPA_L1"."IsActionRequest",
"LPA_L1"."Likelihood",
"LPA_L1"."RecheckDue",
"LPA_L1"."EnteredById",
"LPA_L1"."RegisteredOn",
"LPA_L1"."ReplacementAction",
"LPA_L1"."ReplacementActionID",
"LPA_L1"."RiskCauseNo",
"LPA_L1"."RiskNo",
"LPA_L1"."Severity",
"LPA_L1"."StaffMemberName",
"LPA_L1"."TrackedBy",
"LPA_L1"."WRID",
"LPA_L1"."LPFA_28",
"LPA_L1"."SourceGroupRole",
"LPA_L1"."SourceRole",
"LPA_L1"."Overdue",
"LPA_L1"."ApprovalDefinitionID",
"LPA_L1"."RejectedApprovalDefinitionID",
"LPA_L1"."ResponsibleDepartmentID"
FROM (SELECT "LPA_L3"."ACTION" AS "Action",
"LPA_L3"."ACTION_ID" AS "ActionID",
"LPA_L4"."ACTION_PRIORITY_LEVEL" AS "F__1830505937",
"LPA_L4"."SEQNO" AS "F__1143628958",
"LPA_L3"."ACTION_ID_SORTABLE" AS "ActionIDSortable",
"LPA_L3"."ACTION_NO" AS "ActionNo",
"LPA_L3"."ACTION_RESPONSE_TEXT" AS "ActionResponseText",
"LPA_L3"."ACTION_STATUS_TEXT" AS "ActionStatus",
"LPA_L3"."ACTION_STATUS" AS "ActionStatusId",
"LPA_L5"."ACTION_TYPE" AS "ActionType",
"LPA_L3"."ASSIGNED_TO" AS "AssignedTo",
"LPA_L3"."CAUSE_NO" AS "CauseNo",
"LPA_L3"."COMPLETED_ON" AS "CompletedOn",
"LPA_L3"."CONTACT_PERSON" AS "ContactPerson",
"LPA_L3"."DEPARTMENT" AS "Department",
"LPA_L3"."DUE_DATE" AS "DueDate",
"LPA_L3"."EXT_ACTION_REFERENCE" AS "ExtActionReference",
"LPA_L6"."EXT_AUDIT_REFERENCE" AS "ExtAuditReference",
"LPA_L6"."WR_NUMBER" AS "WRNumber",
"LPA_L6"."WR_PERIOD" AS "WRPeriod",
"LPA_L6"."WR_TYPE" AS "WRType",
"LPA_L7"."FINDING_CATEGORY" AS "FindingCategory",
"LPA_L3"."FINDING_ID" AS "FindingID",
"LPA_L6"."FINDING_NO" AS "FindingNo",
"LPA_L3"."IS_ACTION_REQUEST" AS "IsActionRequest",
"LPA_L9"."GLOBAL_LIKELIHOOD" AS "Likelihood",
"LPA_L3"."RECHECK_DUE" AS "RecheckDue",
"LPA_L3"."REGISTERED_BY_ID" AS "EnteredById",
"LPA_L3"."REGISTERED_ON" AS "RegisteredOn",
"LPA_L10"."ACTION_NO" AS "ReplacementAction",
"LPA_L3"."REPLACEMENT_ACTION_ID" AS "ReplacementActionID",
"LPA_L3"."RISK_CAUSE_NO" AS "RiskCauseNo",
"LPA_L3"."RISK_NO" AS "RiskNo",
"LPA_L11"."GLOBAL_SEVERITY" AS "Severity",
"LPA_L3"."REGISTERED_BY_NAME" AS "StaffMemberName",
"LPA_L3"."TRACKED_BY" AS "TrackedBy",
"LPA_L3"."WRID",
1 AS "LPFA_28",
"LPA_L2"."StaffRole" AS "SourceGroupRole",
Trim((SELECT "LPA_L15"."STAFF_ROLE" AS "StaffRole"
FROM ((("AQD"."QAOC_WORK_REQUEST_VIEW" "LPA_L12"
RIGHT JOIN "AQD"."QAOC_FINDING" "LPA_L13"
ON "LPA_L12"."WR_NUMBER" = "LPA_L13"."WR_NUMBER"
AND "LPA_L12"."WR_PERIOD" = "LPA_L13"."WR_PERIOD"
AND "LPA_L12"."WR_TYPE" = "LPA_L13"."WR_TYPE")
INNER JOIN "AQD"."QAOC_WR_STAFF" "LPA_L14"
ON "LPA_L12"."WR_NUMBER" = "LPA_L14"."WR_NUMBER"
AND "LPA_L12"."WR_PERIOD" = "LPA_L14"."WR_PERIOD"
AND "LPA_L12"."WR_TYPE" = "LPA_L14"."WR_TYPE")
LEFT JOIN "AQD"."QAOC_WR_STAFF_ROLE" "LPA_L15"
ON "LPA_L15"."STAFF_ROLE_ID" = "LPA_L14"."STAFF_ROLE_ID")
WHERE (((("LPA_L13"."FINDING_ID" = "LPA_L3"."FINDING_ID")
AND ("LPA_L14"."STAFF_MEMBER" = 136 /* :p3 */))))
AND rownum <= 1)
|| ' ' /* :p2 */
|| "LPA_L2"."StaffRole") AS "SourceRole",
CASE
WHEN CASE
WHEN ((("LPA_L3"."ACTION_STATUS" <> 8 /* :p4 */)
AND ("LPA_L3"."ACTION_STATUS" <> 4 /* :p5 */))
AND ("LPA_L3"."DUE_DATE" < '20210518' /* :p6 */)) THEN 1
ELSE 0
END = 1 THEN 3 /* :p8 */
ELSE
CASE
WHEN CASE
WHEN (((("LPA_L3"."ACTION_STATUS" <> 8 /* :p9 */)
AND ("LPA_L3"."ACTION_STATUS" <> 4 /* :p10 */))
AND ("LPA_L3"."DUE_DATE" < '20210519' /* :p11 */))
OR ((("LPA_L3"."ACTION_STATUS" <> 8 /* :p12 */)
AND ("LPA_L3"."ACTION_STATUS" <> 4 /* :p13 */))
AND ("LPA_L3"."DUE_DATE" < '20210525' /* :p14 */))) THEN 1
ELSE 0
END = 1 THEN 2 /* :p16 */
ELSE
CASE
WHEN CASE
WHEN (((("LPA_L3"."ACTION_STATUS" <> 8 /* :p17 */)
AND ("LPA_L3"."ACTION_STATUS" <> 4 /* :p18 */))
AND ("LPA_L3"."DUE_DATE" < '20210526' /* :p19 */))
OR ((("LPA_L3"."ACTION_STATUS" <> 8 /* :p20 */)
AND ("LPA_L3"."ACTION_STATUS" <> 4 /* :p21 */))
AND ("LPA_L3"."DUE_DATE" < '20210601' /* :p22 */))) THEN 1
ELSE 0
END = 1 THEN 1 /* :p24 */
ELSE 0 /* :p26 */
END
END
END AS "Overdue",
(SELECT "LPLA_18"."APPROVAL_DEFINITION_ID" AS "ApprovalDefinitionID"
FROM "AQD"."APP_APPROVAL" "LPLA_18"
WHERE (((("LPA_L3"."ACTION_ID" = "LPLA_18"."RECORD_ID")
AND ((("LPLA_18"."CURRENT_FLAG" = 1 /* :p27 */)
AND ("LPLA_18"."RECORD_TYPE" = 1 /* :p28 */))
AND EXISTS
(SELECT "LPLA_19"."STEP_STATE_ID" AS "StepStateID"
FROM "AQD"."APP_APPROVAL_STEP" "LPLA_19"
WHERE ("LPLA_18"."APPROVAL_ID" = "LPLA_19"."APPROVAL_ID"
AND ("LPLA_19"."STEP_STATE_ID" = 1 /* :p29 */)))))))
AND rownum <= 1) AS "ApprovalDefinitionID",
(SELECT "LPLA_20"."APPROVAL_DEFINITION_ID" AS "ApprovalDefinitionID"
FROM "AQD"."APP_APPROVAL" "LPLA_20"
WHERE (((("LPA_L3"."ACTION_ID" = "LPLA_20"."RECORD_ID")
AND (("LPLA_20"."CURRENT_FLAG" = 1 /* :p30 */)
AND EXISTS
(SELECT "LPLA_21"."STEP_STATE_ID" AS "StepStateID"
FROM "AQD"."APP_APPROVAL_STEP" "LPLA_21"
WHERE ("LPLA_20"."APPROVAL_ID" = "LPLA_21"."APPROVAL_ID"
AND ("LPLA_21"."STEP_STATE_ID" = 3 /* :p31 */)))))))
AND rownum <= 1) AS "RejectedApprovalDefinitionID",
"LPA_L3"."RESPONSIBLE_DEPARTMENT" AS "ResponsibleDepartmentID"
FROM ((((((((((SELECT "LPA_L21"."STAFF_ROLE" AS "StaffRole",
"LPA_L17"."FINDING_ID" AS "FindingID"
FROM ((((("AQD"."QAOC_WORK_REQUEST_VIEW" "LPA_L16"
RIGHT JOIN "AQD"."QAOC_FINDING" "LPA_L17"
ON "LPA_L16"."WR_NUMBER" = "LPA_L17"."WR_NUMBER"
AND "LPA_L16"."WR_PERIOD" = "LPA_L17"."WR_PERIOD"
AND "LPA_L16"."WR_TYPE" = "LPA_L17"."WR_TYPE")
INNER JOIN "AQD"."QAOC_WR_GROUP_ASSIGNMENT" "LPA_L18"
ON "LPA_L16"."WR_NUMBER" = "LPA_L18"."WR_NUMBER"
AND "LPA_L16"."WR_PERIOD" = "LPA_L18"."WR_PERIOD"
AND "LPA_L16"."WR_TYPE" = "LPA_L18"."WR_TYPE")
INNER JOIN "AQD"."QAOC_ASSIGNMENT_GROUP" "LPA_L19"
ON "LPA_L19"."ASSIGNMENT_GROUP_ID" = "LPA_L18"."ASSIGNMENT_GROUP_ID")
INNER JOIN "AQD"."QAOC_ASSIGNMENT_GROUP_MEMBER" "LPA_L20"
ON "LPA_L19"."ASSIGNMENT_GROUP_ID" = "LPA_L20"."ASSIGNMENT_GROUP_ID")
INNER JOIN "AQD"."QAOC_WR_STAFF_ROLE" "LPA_L21"
ON "LPA_L21"."STAFF_ROLE_ID" = "LPA_L18"."STAFF_ROLE_ID")
WHERE ((("LPA_L20"."STAFF_MEMBER_ID" = 136 /* :p32 */)))) "LPA_L2"
RIGHT JOIN "AQD"."QAOC_ACTN_VIEWSECURED" "LPA_L3"
ON "LPA_L3"."FINDING_ID" = "LPA_L2"."FindingID")
LEFT JOIN "AQD"."QAOC_ACTION_PRIORITY_LEVEL" "LPA_L4"
ON "LPA_L4"."ACTION_PRIORITY_LEVEL_ID" = "LPA_L3"."ACTION_PRIORITY_LEVEL_ID")
INNER JOIN "AQD"."QAOC_ACTION_TYPE" "LPA_L5"
ON "LPA_L5"."ACTION_TYPE_ID" = "LPA_L3"."ACTION_TYPE_ID")
LEFT JOIN "AQD"."QAOC_FINDING" "LPA_L6"
ON "LPA_L6"."FINDING_ID" = "LPA_L3"."FINDING_ID")
LEFT JOIN "AQD"."QAOC_FINDING_CATEGORY" "LPA_L7"
ON "LPA_L7"."FINDING_CATEGORY_ID" = "LPA_L6"."FINDING_CATEGORY_ID")
LEFT JOIN "AQD"."RM_RISK_MATRIX_CELL" "LPA_L8"
ON "LPA_L8"."RISK_MATRIX_CELL_ID" = "LPA_L6"."RISK_MATRIX_CELL_ID")
LEFT JOIN "AQD"."RM_GLOBAL_LIKELIHOOD" "LPA_L9"
ON "LPA_L9"."GLOBAL_LIKELIHOOD_ID" = "LPA_L8"."GLOBAL_LIKELIHOOD_ID")
LEFT JOIN "AQD"."QAOC_ACTION" "LPA_L10"
ON "LPA_L10"."ACTION_ID" = "LPA_L3"."REPLACEMENT_ACTION_ID")
LEFT JOIN "AQD"."RM_GLOBAL_SEVERITY" "LPA_L11"
ON "LPA_L11"."GLOBAL_SEVERITY_ID" = "LPA_L8"."GLOBAL_SEVERITY_ID")
WHERE (((("LPA_L3"."STAFF_MEMBER_ID" = 136 /* :p33 */))
AND (("LPA_L3"."ACTION_STATUS" IS NOT NULL)
AND ("LPA_L3"."ACTION_STATUS" IN (6 /* :p34 */, 1 /* :p35 */, 5 /* :p36 */, 4 /* :p37 */,
8 /* :p38 */, 7 /* :p39 */))))
AND ("LPA_L3"."IS_ACTION_REQUEST" = 0 /* :p40 */))) "LPA_L1"
ORDER BY "LPA_L1"."ActionID" ASC)
WHERE rownum <= 10
Joined: 17-Aug-2003
At first, when looking at that massive query, I was like
But I think this is caused by the change we made to allow longer identifier names on Oracle, as you can see the outer projection rightfully allows ActionPriorityLevelActionPriorityLevelSeqNo
and ActionPriorityLevelActionPriorityLevel
, however the inner query doesn't seem to honor that and changes the identifier name to a shorthand version we used in v5.7 and earlier.
We'll look into it.
(it's related to this fix: https://www.llblgen.com/tinyforum/Thread/27269, made for 5.7.4, 5.8.1 )
Joined: 17-Aug-2003
Fixed. (new hotfix v5.8.2 and v5.7.4) We clamped aliases but if a field became a derived table targeting field, and thus targeting an aliased field, the target wasn't considered to be clamped, which has now been corrected. I think we cover all cases now, if not, please let me know.