subqueries in a from clause in a view

[Former Member]

Windows 10 64-bit SQL Anywhere 17 17.0.4.2053 I have a view that contains a sub-query in a "from" clause like this:

       s1_name_and_address location_naa,
       s1_release,
       (SELECT CASE WHEN cntmf.misc_field_nbr = 1
                        THEN cntmf.miscellaneous_field
                        ELSE NULL
               END AS misc_column_description1
          FROM s1_miscellaneous_field_data cntmf
         WHERE cntmf.program_code IN ('PCE','SCE','OCE')
           AND cntmf.table_key = 's1_contract'
       ),

when I execute this SQL I get this error:

Could not execute statement.
Syntax error near ',' on line 244 SQLCODE=-131, ODBC 3 State="42000" Line 244

Line 244 points to the end of the sub-query. When I execute the sub-query in a stand alone manner it behaves as expected. Is this a limitation or an error or ?? Thanks

[VolkerBarth]

Note that an If expression would be shorter than the CASE expression you are using, and the ELSE NULL is the default if you omit an ELSE clause. Just saying..

[VolkerBarth]

In a view definition, each column in the SELECT list must have a defined name. For a subselect you need to add an alias after the closing bracket. For a regular SELECT there is no need to give an alias here.

[Former Member]

Thanks Volker - that worked!!! did not know about the "alias" requirement...