Case when used with a subquery strange result..

[saskapa]

Hi,

Can someone tell me why does this code return a flag  '1'  while it shouldn't ?

proc sql;

create table test  as

select region,case

                      when region in (select region from sashelp.bmt ) then  '1'   

                      else                                                                         '0'

                    end

                    as case

from sashelp.demographics;

quit;

Obviously, Region variable is not in BMT dataset so Case variable should be set to 0.

Thanks in advance,

Skp

[PGStats · Accepted solution]

Puzzling indeed, at first! Here is a hint, try this:

proc sql;

create table test  as

select

    d.region,

    case

        when d.region in (select d.region from sashelp.bmt) then  '1'  

        else                                                                                            '0'

        end as case

from sashelp.demographics as d;

quit;

Explanation: The reference to column region in the subquery is assumed to be from table sashelp.demographics because region is not a column in sashelp.bmt.

PG

[jwillis]

I am using 9.4 64 bit and I still receive a '1' for case.  Can you give a stronger hint?

proc sql;

drop table test;

create table test  as

select

d.region,

case

        when d.region in (select d.region from sashelp.bmt ) then  '1'  

        else                                                                         '0'

        end  as case

from sashelp.demographics as d ;

quit;

  Obs    region    case

                                              1     AMR       1

                                              2     AMR       1

                                              3     AMR       1

                                              4     AMR       1

                                              5     AMR       1

                                              6     AMR       1

                                              7     AMR       1

[Reeza]

The subquery or whatever it's called assumes that region in the select statement is from sashelp.demographics because there is no alias prefix to tell it otherwise so it defaults to whatever table it finds the variable in.

The following will generate the results you expect:

proc sql;

drop table test;

create table test  as

select

D.region,

case

        when d.region in (select B.region from sashelp.bmt as B ) then  '1' 

        else                                                                         '0'

        end  as case

from sashelp.demographics as D ;

quit;

[PGStats]

Notice the "d." prefix to column name region in the subquery. d.region refers to column region in sashelp.demographics. As far as the subquery is concerned, d.region is a constant (just like as if you said select "A" from sashelp.bmt, try it! ), the result of which being the value of the constant repeated as many times as there are lines in sashelp.bmt. Note that column region doesn't even exists in table sashelp.bmt, which is why region in the original question was considered to mean d.region by the SQL compiler. Thus, the when condition is always true because b.region is always part of a column of repeated b.region values.

PG  

[Reeza]

I agree with @pgstats and think this is considered an inline query not a subquery - IIRC, inline queries run for each observation whereas a subquery would run only once.

[PGStats]

I couldn't find any reliable source to clarify this point. The expression "inline query" is never mentioned in the 600+ pages standard manual for SQL92. Subqueries however are divided into scalar-, row- and table- subqueries. Thus it seems that any query within another query is a subquery, as far as the standard goes.

PG

[Reeza]

Then I probably don't recall correctly

I'm thinking of when I used to use T-SQL and those types of queries were used occasionally but it's been a *few* years now!

[gergely_batho]

It is called correlated sub-query.

But usually it really correlates to the outer query.

[saskapa]

Thanks PGStats ! About the terminology, I couldn't find any 'Inline querry' terminology...I looks like that  it is indeed  a correlated sub-query.

Cheers

skp