Nested SELECT does not work

[akatecker]

Metl Version tested: 3.0.0 Component: RDBMS Reader Database: MS SQL, Sybase, maybe others Problem: When the SQL contains nested SELECT statements, although the SQL executes correctly, the component does not output any value for the field, nor for any following field. Example: SELECT table.value1 /* tab.field1 */, (SELECT sum(nested.value2) FROM nested WHERE nested.value1=table.value1) /* tab.field2 */, table.value3 /* tab.field3 */ FROM table This will execute and show the correct table when pushing "Execute" inside the SQL editor. When running the flow and logging the output of the component, tab.field1 will be extracted, but tab.field2 and tab.field3 will be omitted (not set).

[abrougher]

There is a known issue with nesting select statements within the SQL. The issue is around parsing the first "from" keyword.

Two options...

1. Rewrite the subselect as a join.
2. Put '--' after the from and wrap the rest of the line. Since the parser is looking for a space after the from this hack will get you around the issue.

For example: The following line:

(SELECT sum(nested.value2) FROM nested WHERE nested.value1=table.value1) /* tab.field2 */,

Would become...

(SELECT sum(nested.value2) FROM-- nested WHERE nested.value1=table.value1) /* tab.field2 */,

[akatecker]

Cool workaround. Works like charm. I already rewrote some of my queries, but in other cases the nested selects are just so much cleaner. Thank you!