Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
Clear All
new posts

    SQLDataSource ansi joins and $defaultTableClause

    SmartClient Version: SNAPSHOT_v12.1d_2019-07-20/Enterprise Deployment (built 2019-07-20)

    Hello, I've just noticed this behaviour. In a dataSource based on table="A", which includes a field from table B:
    Code:
        <field name="foo" includeFrom="B.foo"/>
    where I include another field from a table C without using the includeFrom attribute:
    <field name="bar" tableName="C"/>
    and the join with C is written in the tableClause:
    Code:
    <tableClause>
        $defaultTableClause                
        JOIN C ON B.ID = C.ID
    </tableClause>
    I obtain a query formed like this:

    Code:
    FROM
    A
    JOIN C ON B.ID = C.ID
    JOIN B ON A.ID = B.ID
    which clearly doesn't work, while I was expecting this:
    Code:
    FROM
    A
    JOIN B ON A.ID = B.ID
    JOIN C ON B.ID = C.ID
    because I thought that the $defaultTableClause would contain the automatic ANSI joins.

    Actually I could use the includeFrom also for the 'bar' field, but in the actual use case I have a condition for the join, which is something I can't express with the includeFrom attribute.

    So I'm asking if it's a bug or a known limitation, as I don't see a note in the documentation against mixing includeFrom and explicit ANSI joins.

    #2
    Hello, actually I've found another use case where it seems problematic to have in the same dataSource some 'includeFrom' fields and a field declared with a customSelectExpression.

    The customSelectExpression is defined as COALESCE(table_A.foo, table_B.bar, table_C.foobar), and this same field is a foreignKey used to join a table_D via includeFrom. So the COALESCE() is used in the join condition.

    Also table_B and table_C are joined via includeFrom.
    The table_B join is written before the join using the COALESCE function, but the table_C join is written after, so it raises an error. because table_C.foobar is not a valid identifier (ORA-00904).


    Comment


      #3
      The issue from #1 is fixed and available for download in nightly builds since Aug 10 (today).

      Regarding #2, please check if it is still an issue after the fix above and let us know. Also, if issue will still be present please describe it with more details and a sample.

      Comment


        #4
        It's come to our attention that sometimes the most useful behavior is to include an ANSI join in the table clause, and sometimes not - certain queries become more difficult, or have to be entirely hand-coded, depending on which approach you take. So we've made it switchable - to get the behavior you want when you are configured to use ANSI joins, you now need to set includeAnsiJoinsInTableClause to "true" on your operationBinding.

        Comment

        Working...
        X