4

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
Chris
  • 1,421
  • 3
  • 18
  • 31
  • 1
    Does it give you a syntax error when you run it? What's the error? – JNK Jul 28 '10 at 14:42
  • What is "doesn't like?" What's the error? – Dan Jul 28 '10 at 14:43
  • 1
    "3771: Illegal expression in WHEN clause of CASE expresssion". Here's what I'm actually doing: select t.foo, (case when 1=1 then '1' else '0' end) as lapsedFlag, (case when exists (select cc.date_dt from x.bar cc where cc.foo = t.foo and cc.date_dt between '2010-05-01' (date) and '2010-07-31' (date) ) then '1' else '0' end) as MyFlag from x.bar t – Chris Jul 28 '10 at 14:48

4 Answers4

3

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)
hol
  • 8,255
  • 5
  • 33
  • 59
  • Right. This is kind of a gray area where I'm trying to mix procedure and structure in some unholy union, but the structure actually is "show me info, including whether or not the info passes some test". – Chris Jul 28 '10 at 17:17
  • Of course, the standby solution is to have two it in two steps: one with the test "if exists" in the where, and literally setting my flag to "1", and then doing it again with "if not exists", literally setting the flag to "0". Either into a temp table or I guess use a union... – Chris Jul 28 '10 at 17:28
  • @Chris: I am not sure now. Even if I look at the other SQL statement you added as a comment at the questions I am not sure why my SQL wouldn't solve problem. What is still needed? Like this:select t.foo, (case when 1=1 then '1' else '0' end) as lapsedFlag, (case when (select count(*) from x.bar cc where cc.foo = t.foo and cc.date_dt between '2010-05-01' (date) and '2010-07-31' (date) ) > 0 then '1' else '0' end) as MyFlag from x.bar – hol Jul 28 '10 at 17:29
2

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 :(

Chris
  • 1,421
  • 3
  • 18
  • 31
  • +1 I think the UNION ALL is a good workaround because the intention is clear and therefore easier to correct when this 'bug' gets fixed in a later version of Teradata (not sure why a temp table is needed, though) – onedaywhen Jul 29 '10 at 09:09
0

Don't use a from clause in your exists in a case

Case when exists(select x.foo where blahblah>0 then 1 end) from mydb
Left /inner join somedb x
Andrew Barber
  • 39,603
  • 20
  • 94
  • 123
Djpsiphi
  • 1
0

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