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

asked 08 Feb, 14:46

Murray%20Sobol's gravatar image

Murray Sobol
61227
accept rate: 0%

edited 08 Feb, 15:18

Mark%20Culp's gravatar image

Mark Culp
23.6k9134278

1

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..

(08 Feb, 14:57) Volker Barth

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.

permanent link

answered 08 Feb, 14:53

Volker%20Barth's gravatar image

Volker Barth
32.0k326468687
accept rate: 32%

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

(08 Feb, 15:13) Murray Sobol
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×29

question asked: 08 Feb, 14:46

question was seen: 162 times

last updated: 08 Feb, 15:18