SQL: case-when statement with "exists"

Question

I'd like to be able to set add a field that answers the question "For a value in this record, does that value meet some condition in another table?". I thought I'd try a case-when with an exists, but Teradata (my dbms) does not like it. Any recommendations?

select foo,
   (case when exists (select x.foo
                      from somedb x
                      where x.bar > 0)
    then '1' else '0' end) as MyFlag

from mydb

Answer 1

It looks like you're missing the END for the CASE statement?

select foo,
   (case when exists (select x.foo
                      from somedb x
                      where x.bar > 0)
    then '1' else '0' END) as MyFlag

from mydb

Answer 2

There is probably more than one solution to this. Sometimes there is a relationship between the two tables. Then I make a JOIN and handle it in the WHERE clause. I do not know Teradata but in Oracle I can also do something like this.

SELECT foo 
FROM   mydb
WHERE  (select count(*) from somedb where x.bar > 0) > 0

or maybe more like your code

select foo,  
   (case when (select count(*)
                      from somedb x  
                      where x.bar > 0) > 0   
    then '1' else '0') as MyFlag       
from mydb

I know to use the EXISTS only in the WHERE clause "I only want that rows where the following SELECT gives me something". This only makes sense if there is some connection between the one and the other table.

select id,foo from mydb y
where exists (select x.id from somedb x where x.id = y.id)

Answer 3

Since you're only interested in 1 and 0 as flag values, try the following:

select foo,
   coalesce((select max(1)
             from somedb x
             where x.bar > 0), 0) as MyFlag
from mydb

Answer 4

I couldn't come up with a solution that was easy to read (key when you're dim-witted as I am), so I did a union into a temporary table:

create multiset table someDb.NiceFlags as
(
    select t.foo,
           '1' as myFlag
    from someDb.pos_txn_mstr t
    where exists(select x...)

  union all

    select t.foo,
           '0' as myFlag
    from someDb.pos_txn_mstr t
    where not exists(select x...)

) with data primary index(foo)

But now I don't feel like a tough guy :(