CODEWITHSUNDEEP
sqlteradata
Adam
Adam

Reputation: 2455

Teradata CASE WHEN attribute IN SELECT

I have 2 tables in Teradata - "a" and "ch". Table ch contains 2 colums "amt" and "code". Between tables "a" and "ch" is LEFT JOIN. Join is made, and in the SELECT part I am trying to SUM amt values. But when a "code" attribute has a speific values it hast to take only 70% of the "amt" value.

This is what I have tried:

SELECT SUM 
       (
       CASE WHEN ch.code IN (SELECT code from ...)
          then 0.7*ch.amt
       )   
       else ch.amt
       END
FROM a LEFT JOIN ch

I get an error:

Illegal expression in WHEN Clause of CASE expression.

Google says, that it is because CASE does not allow SELECT statements.

Any suggestion how can I achieve the above described functionality?

Upvotes: 2

Views: 12397

Answers (3)

access_granted
access_granted

Reputation: 1907

Technically, you can use select in a case expression, you'll have to style it:

select 
  (case when (select 'match' colM from (select 1 col1) s0 where s0.col1=s1.col1) is not null
    then '1'
    else '0'
  end) case_when_select_col
from (select '1' col1) s1

In your case:

  select case when 
    (select 1 from (select code from ...) s1 where ch.code=s1.code) is null
    then ch.amt
    else 0.7 * ch.amt
    end
  from a left join ch

Upvotes: 1

Mani Bharataraju
Mani Bharataraju

Reputation: 65

Since you are trying to write a query inside when I assume that the specific values are from another table/based on some other condition. You can try the following.

 SELECT 
      SUM(CASE WHEN MATCH = 1 THEN 0.7*AMT ELSE AMT END)
 FROM
      ( 
      SELECT AMT,CODE,
            CASE WHEN C.CODE IS NOT NULL THEN 1 ELSE 0 END AS MATCH
      FROM
      A LEFT OUTER JOIN CH
      ON JOIN_CONDITION
      LEFT OUTER JOIN 
      (SELECT CODE FROM ....) C
       ON CH.CODE=C.CODE) A

Upvotes: 0

Concarney
Concarney

Reputation: 21

A simple solution to this is as follows:

        SELECT SUM(
                    SELECT 0.7*amt AS amt 
                    FROM ch
                    WHERE ch.code = specific_value

                    UNION ALL 

                    SELECT amt from ch 
                    WHERE  ch.code <> specific_value
                  )
        FROM a LEFT JOIN ch 
        ON a.join_index = ch.join_index

If you don't have an index to join these two tables on, you will need to use two SELECT sub-queries joined together with the UNION operator.

Upvotes: 0

Related Questions