~savoy/ade#13: 
feature: allow for specific column choices in Construct

Construct columns should allow you to indicate the table it comes from e.g. "AgreementHeader.validToDate" and give a warning if the same column name exists in multiple used tables if the table isn't already specified.

As an anecdote without this feature, this will cause undesired behavior. For example, the following query will not pull the expected validToDate:

out = remote.Construct(
    "InvoiceLine",
    [
        "invoiceNumber",
        "agreementNumber",
        "agreementLineNbr",
        "validFromDate",
        "validToDate"
    ],
    ["2021-01", "2021-12"],
    agreements=["A632782"],
    joins={
        "Invoice": [("invoiceId", "invoiceId")],
        "AgreementHeader": [("agreementHeaderId", "agreementHeaderId")],
        "AgreementLines": [
            ("agreementNumber", "agreementNumber"),
            ("agreementLineNbr", "lineNumber)
        ]
    }
).pull()

As both AgreementHeader and AgreementLines contain validToDate, and with the Header joined first, the constructor will pull the first validToDate it finds, which would be in Header. This value, however, contains nothing but null values compared to the actual data we want, which is the validToDate from the agreement line itself.

Although this can be fixed by making sure to drop Header (as it's not needed) or join on Lines first, what if it was InvoiceLine that had validToDate? There's no way to join that second and if the query would not make sense to start on AgreementLines and then join InvoiceLine, there's no way around it.

Status
REPORTED
Submitter
~savoy
Assigned to
Submitted
10 months ago
Updated
10 months ago
Labels
feature