Error using derived table and "case" block

[former_member299080]

Hello my friends,

Does anyone know why this does not work?

select col1, col2,
    "col3" =
        case
            when (select 'test' as colx) = 'test' then 'match'
            else 'do not match'
        end

from
    (select 'x' as col1, 'y' as col2) as internal_table

The error says:

Could not execute statement.
The column 'col1' in the outer query is not present in the derived table
expression.
Sybase error code=11751
Severity Level=15, State=1, Transaction State=1
Line 9

1:select col1, col2,
2:    "col3" =
3:        case
4:            when (select 'test' as colx) = 'test' then 'match'
5:            else 'do not match'
6:        end
7:
8:from
9:    (select 'x' as col1, 'y' as col2) as internal_table

Thanks!

Marco

[former_member188958]

Hi Mark,

I get the same 11572 error as Marco on 15.7.2  ESD 4.3.  Using an ISQL client:

1> select it.col1, it.col2
,

        case
            when (select 'test' as colx) = 'test' then 'match'
            else 'do not match'
        end as "col3"

from
    (select 'x' as col1, 'y' as col2 from sysusers) it
go
2> 3> 4> 5> 6> 7> 8> 9> 10> 11> Msg 11751, Level 15, State 1:
Server 'rel1572_bret_sun2', Line 10:
The column 'col1' in the outer query is not present in the derived table
expression.

So I think the parser is confusing the "(select 'test' as colx)" expression in the CASE statement with the actual derived table in the FROM clause.

-bret

[former_member185487]

Hi  Parsons,

This one good stuff regarding ASE as data derived table ..

Thanks

Asish Mohanty

[former_member188958]

I think this is likely a bug.  The parser seems to be getting confused by the "(select 'test' as colx)" in the CASE statement.  The query works without error when I substitute for that either a simple literal "'test'" or a subquery referencing an existing table "select min(test) from sysusers"

1> select col1, col2
,

        case
            when (select min('test') as colx from sysusers) = 'test' then 'match'
            else 'do not match'
        end as "col3"

from
    (select 'x' as col1, 'y' as col2 ) internal_table
go
2> 3> 4> 5> 6> 7> 8> 9> 10> 11>  col1 col2 col3
---- ---- ------------
x    y    match

(1 row affected)

1> select col1, col2
,

        case
            when 'test' = 'test' then 'match'
            else 'do not match'
        end as "col3"

from
    (select 'x' as col1, 'y' as col2 ) internal_table
go
2> 3> 4> 5> 6> 7> 8> 9> 10> 11>  col1 col2 col3
---- ---- ------------
x    y    match

(1 row affected)