Distinct in Case clause

Database

Distinct in Case clause

Hi all,

this is my input table:

val1 val2 val3 val4

111  5      aa   13:00

111  8      bb   13:00

111  12    cc    8:00

This is the query i used:

..

..

case when ( distinct val4 < time '12:00:00' then 1

     else 0 end) as fascia_1,

case when ( distinct val4 >= time '12:00:00' then 1

else 0 end) as fascia_2

What  i need to have is a one time evaluation of the variable var4.

How can i do it?

Thanks in advanced

D.

5 REPLIES
Teradata Employee

Re: Distinct in Case clause

Please share the desired output of the dataset as well.

Re: Distinct in Case clause

Yes for sure.

I use a more realistic sample to explain.

My starting table:








Sales_Trx_Id Gross_Sales_Amt Item_Qty Item_Division_Cd Sales_Trx_Tm
710566684 002 001 24 17:56:19
711879367 001 001 24 16:42:03
711879367 002 001 24 16:42:03
713314337 001 001 24 18:20:50

 The code in the query:

 ..

case when ( Sales_Trx_Tm time '12:00:00' then 1

else 0 end) as fascia_1,

case when ( Sales_Trx_Tm>= time '12:00:00' then 1

else 0 end) as fascia_2

from table

group by sales_trx_id

 When group by sales_trx_id i want that for value 711879367 i have fascia_2=1.

So i need to evaluate just 1 time the value 16:42:03.

Is it clear now?

Thanks

Senior Apprentice

Re: Distinct in Case clause

count(distinct case when Sales_Trx_Tm time < '12:00:00' then Sales_Trx_Tm end) 

Dieter

Re: Distinct in Case clause

Sorry, errata corridge:

Group by clause is over ITem_Division_Cd and not by sales_trx_id.

 

Thanks

Senior Apprentice

Re: Distinct in Case clause

Could you format your data, it's not readable.

Dieter