|
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 |
|
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. Thanks Volker - that worked!!! did not know about the "alias" requirement...
(08 Feb '18, 15:13)
Murray Sobol
|
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..