SAP SQL Anywhere

Question

Quick question:

Why this works in the most of DBMSs, but in Ultralite doesn't?

SELECT 
    (
        SELECT cdTable2 
        FROM table2 T2
        INNER JOIN T1xT2
            ON T1xT2.idTable2 = T2.idTable2
            AND T1xT2.idTable1 = T1.idTable1
    )
FROM table1 T1

Column 'T1.idTable1' not found

Ps: This example is just a very simple scenario. It could be easily solved by replacing the sub-select to joins. But the question is still valid for other complex scenarios we have today!

Just to complement, this limitation only occurs in joins. If you move the filter condition to the where clause, this works, as follow:

SELECT 
    (
        SELECT cdTable2 
        FROM table2 T2
        INNER JOIN T1xT2
            ON T1xT2.idTable2 = T2.idTable2
        WHERE T1xT2.idTable1 = T1.idTable1
    )
FROM table1 T1

Accepted Answer

4

The problem here is that the ON clause isn't allowed to refer to tables other than those in the associated join. Please see this note in the documentation.

Also note that conditions in the WHERE clause can have unexpected effect with outer joins. For inner joins, the ON and WHERE clause are semantically equivalent, but this is not true for outer joins.

permanent link

answered 14 Feb '17, 17:24

Tim McClements
2.0k●18●30
accept rate: 35%

flat view

Answer 2

THIS IS NOT an official answer. ...but...

The UL parser (being 'light' like the rest of Ultra*Lite*) supports a more limited syntax (ie. a subset of SQL) and only some semantics that may be available in other RDBMS.

In your case, neither of your 2 uses of sub-queries have official support so the fact that it works in the latter case is a unofficial bonus. If I had to guess, the UltraLite's parser is recognizing your latter query as a correlated sub-query and may be flattening/rewriting that (getting the plan for that might tell more).

Given the noted 'official' restriction, your first query probably does not look like a proper correlated subquery and so the parser unit will likely just be looking at correlation names (tokens) from the subquery alone.

SAP SQL Anywhere

Why Ultralite doesn't support sub-query linking in join conditions?

Follow this question

Related questions