2

I am using a statement as below and get this error:

SELECT Failed. 3771: Illegal expression in WHEN clause of CASE expression.

I had better hopes from Teradata. SQL Server can do it but Teradata can't. How can I work around this? Any solution?

sel ( CASE
    WHEN  EXISTS   ( sel '1' from VolatileTable Dtb1 where Dtb1.c1=FACT_Table_5MillionRows.C1)
    THEN "FACTTablew5MillionRows"."CustomColumName" 
ELSE 'ALL OTHER'
END  ) (NAMED "CustomColumName" )

from
"Db"."FACTTablew5MillionRows"
mwigdahl
  • 16,268
  • 7
  • 50
  • 64
user1874594
  • 2,277
  • 1
  • 25
  • 49
  • FOLKS how abt if I LOJ the "FACTTablew5MillionRows" and VolatileTable Dtb1 and then do when Dtb1.c1=FACT_Table_5MillionRows.C1 then "_____" else "___" WOuld that afford equivalence ? – user1874594 Oct 07 '15 at 03:34

3 Answers3

3

Replace

WHEN EXISTS (...)

By

WHEN 1 = (SELECT 1 WHERE EXISTS (...))
Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
2

Teradata doesn't like EXISTS in Correlated Scalar Subqueries within a CASE, but you can rewrite it like this:

select
  ( CASE
       WHEN C1 = ( select MIN(C1) from VolatileTable Dtb1 
                   where Dtb1.c1=ft.C1)
       THEN ft."CustomColumName" 
       ELSE 'ALL OTHER'
    END  ) (NAMED "CustomColumName" )
from
"Db"."FACTTablew5MillionRows" as ft

If VolatileTable.C1 is unique you can remove the MIN.

But in 95% logic like this can be replaced by a LEFT JOIN:

select
   ft.*,
   CASE WHEN Dtb1.c1 IS NOT NULL 
        THEN ft."CustomColumName" 
        ELSE  'ALL OTHER' 
   end as "CustomColumName" 
from "Db"."FACTTablew5MillionRows" as ft
left join VolatileTable Dtb1 
on Dtb1.c1=ft.C1

This will return duplicated rows if VolatileTable.C1 is not unique, then you need to change it to:

from "Db"."FACTTablew5MillionRows" as ft
left join (select distinct C1 from VolatileTable) Dtb1 
on Dtb1.c1=ft.C1
dnoeth
  • 59,503
  • 4
  • 39
  • 56
  • 1
    Hi Dieter the LOJ was what I was thinking about and implemented it.But I quite dont get the 1st approach 1 `select ( CASE WHEN C1 = ( select MIN(C1) from VolatileTable Dtb1 where Dtb1.c1=ft.C1) THEN ft."CustomColumName" ELSE 'ALL OTHER' END ) (NAMED "CustomColumName" ) from "Db"."FACTTablew5MillionRows" as ft` .Min ( Col) will be a constant not the same as a what the LOJ would do right ? – user1874594 Oct 08 '15 at 00:04
  • 1
    TY Dieter. I updated my other Q's right here http://stackoverflow.com/questions/32877616/teradata-sql-tuning-with-sum-and-other-aggregate-functions – user1874594 Oct 08 '15 at 01:15
  • 1
    and here http://stackoverflow.com/questions/33004757/teradata-sql-optimization-not-in-list-col-and-in-list-optimization – user1874594 Oct 08 '15 at 01:15
1
WHEN  EXISTS (select '1' from VolatileTable Dtb1 
              where Dtb1.c1=FACT_Table_5MillionRows.C1)
THEN somevalue --or a statement that yields a scalar value

You were selecting a column in the then part, where you should be assigning a unique value.

Vamsi Prabhala
  • 48,685
  • 4
  • 36
  • 58