Sybase subquery in from clause cannot have where when called from linkedserver in sql server?

Question

The following sql works in sql server via "SAP ASE OLE DB Provider".

select * from openquery(Sybase,'
  select * from (
    select * from X
  ) x
')

However, the following doesn't work. It sounds like a hideous bug.

select * from openquery(Sybase,'
    select * from (
      select * from X 
      where not exists (select * from Y where Y.c = X.c) -- c is not key in both tables
    ) x
')

Is it a way to rewrite the subquery to make it work? (it has to be in the subquery). Or is there a problem on the Sybase linked server driver?

Answer 1

try this one (if c is integer and cannot be -1)

select
    *
from
    (   select
            X.*
        from
            X
            left join ( select distinct
                            c
                        from
                            Y) as y on X.c = y.c
            join (  select
                        -1 as c) as z on coalesce(y.c, -1) = z.c
    ) x