Sybase - Subquery in FROM clause

Question

I'm using Sybase ASE 12.5.0.3 and I'm unable to do subqueries like:

select * from (select '1' union select '2' ) X

I've been looking around and as far as I know it should be possible after Sybase ASE 12, am I doing something wrong, or is it not possible with this version???

Edit - Even after changing the query to:

select * from (select '1' as col1 union select '2' as col1 ) X

So even giving alias to the columns, it fails anyways...

score 6 · Answer 1 · answered Sep 04 '12 at 17:47

6

Without seeing an error message, it appears that you need to give column aliases in your sub-query:

select * 
from 
(
    select '1' as yournewCol 
    union 
    select '2' as yournewCol
) X

answered Sep 04 '12 at 17:47

Taryn

242,637
56
362
405

score 1 · Answer 2 · answered Sep 04 '12 at 17:41

1

You need to give your columns name. Try this:

answered Sep 04 '12 at 17:41

Gordon Linoff

1,242,037
58
646
786

I am unable to insert the code sample. Just put "as col" after '1'. – Gordon Linoff Sep 04 '12 at 17:44

score 1 · Answer 3 · answered Mar 13 '13 at 12:11

1

Sybase ASE does not support subqueries in the FROM clause:

Subqueries can be nested inside the where or having clause of an outer select, insert, update, or delete statement, inside another subquery, or in a select list. Alternatively, you can write many statements that contain subqueries as joins; Adaptive Server processes such statements as joins.

answered Mar 13 '13 at 12:11

yoroto

97
1
2

I don't think the above quote is quite correct. Sybase refers to the OP syntax as "SQL-Derived Tables" which is basically what we'd consider as a subquery in a FROM clause - supported as early as 12.5.1. I just tried the OP's fixed version in ASE 15.7 and it worked fine (the one without column names did not work). http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc32300.1570/html/sqlug/sqlug198.htm – David Storfer Apr 12 '19 at 18:26

Sybase - Subquery in FROM clause

3 Answers3