Is is possible to use Aggregate functions inside CASE statement?

Database
Ambassador

Re: Is is possible to use Aggregate functions inside CASE statement?

Hi Kalyan,

first you should change this join on concatenated columns:

and a.hs_id||a.cus_no=b.home_store_id||b.cus_no

to an ANDed join condition:

and a.hs_id=b.home_store_id

and a.cus_no=b.cus_no

to get a better estimation of the number of rows. Check if Explain changes.

You might also try to aggregate as early as possible, i.e. move the SUM into a Derived Table and join this to the other tables. This avoids creating a huge intermediate result set before doing the aggregation.

In best case it will look like this:

SELECT
a.st AS DC,
b.hs AS HS,
b.cus_no AS Cuscode,
b.Cus_nm AS Customername,
c.cus_assort_se_id,
d.cust_ass_se_desc AS Lvl1desc,
a."Year2011",
a."Year2012"
FROM
( SELECT
a.st AS DC,
a.hs_id,
a.cus_no,
SUM (CASE SUBSTR(LTRIM(a.m_id),1,4) WHEN '2011' THEN ( a.cu_net_val_nsp) ELSE 0 END) AS "Year2011",
SUM (CASE SUBSTR(LTRIM(a.m_id),1,4) WHEN '2012' THEN ( a.cu_net_val_nsp) ELSE 0 END) AS "Year2012"
FROM indccp_dwh_views.dw_v_cu_in a
WHERE a.month_id IN (201205,201105)
AND a.St IN (10)
GROUP BY 1,2,3
) a,
indccp_dwh_views.dw_v_cus b,
indccp_dwh_views.dw_v_cust_brh c,
indccp_dwh_views.dw_v_cust_brnh_family d
WHERE a.hs_id =b.home_store_id AND a.cus_no=b.cus_no
AND c.brh_id=b.brh_id
AND c.cust_asrt_section_id=d.cust_asrt_section_id
ORDER BY 1,2;

You might have to add another SUM(YEAR2011/YEAR2012) in the outer table, based on your PK/FK relations.

Btw, those database names sound familiar to me :-)

Dieter

Enthusiast

Re: Is is possible to use Aggregate functions inside CASE statement?

Hi,

OK thanks......a lot,  but in the above query it is asking for union function

Enthusiast

Re: Is is possible to use Aggregate functions inside CASE statement?

Hi

Another Issue,, The below syntax i feel it is wrong...pls

And function in sum (case)

sum((Case when a11.fn_c in ('nl') and case when a11.cu_sell_v_np in(> 968680.0) then a11.cu_sell_v_np else NULL end)) Nl_sales,

so on...............two more conditions, I am doing this to avoid the Having Clause in the Group By.

Thanks

Enthusiast

Re: Is is possible to use Aggregate functions inside CASE statement?

Hi,

When i shoot the below query it says a syntax error......3706 data type m_id dos not match defined type name

===================

select

ste_id

he_ste_id

cu_no

/*substr(ltrim(m_id),1,4),*/

CASE WHEN substr(ltrim(m_id),1,4) in ('2011') THEN SUM(cu_s_v_np) OVER( PARTITION BY ,ste_id,he_ste_id,cu_no) else 0 end) as "Year2011",

CASE WHEN substr(ltrim(m_id),1,4) in ('2012') THEN SUM(cu_s_v_np) OVER( PARTITION BY ,ste_id,he_ste_id,cu_no) else 0 end) as "Year2012"

from indccp_dwh_views.dw_v_cu_inv

where m_id in (201205,201105)

and Ste_id in (10)

group by ste_id,he_ste_id,cu_no

========================================

Ambassador

Re: Is is possible to use Aggregate functions inside CASE statement?

LTRIM is no Teradata SQL function, it's a ODBC extension (which works in some cases in SQL Assistant), you should replace it with TRIM(m_id)

Dieter

Enthusiast

Re: Is is possible to use Aggregate functions inside CASE statement?

Hi

Thanks a lot, issue is solved.

Is there a way to get the Syntax list for advanced teradata functions

Thanks

Enthusiast

Re: Is is possible to use Aggregate functions inside CASE statement?

Hi,

 Here i am facing problem redgarding Teradata service control.

  I am using Teradata 13.0 express edition in my pc,  When try to start the teradata service control it is restarting the system.   I formated the os and again i installed the Teradata but even now also u am facing the same problem.  Please share the information regarding this issue.

Thanks in advance.

Enthusiast

Re: Is is possible to use Aggregate functions inside CASE statement?

Hi,

Hi have  one more doubt is it possible to delete particular field values from a table without deleting the entire record?

  My source data like as follows

  Id   Dept    NAME

      10      Ram

  2     20      sathish

  3      10     ram

  4      20    sathish

  Here in the dept and name columns duplicate data is existed i need to delete that duplicate data without deleting the ID of that corresponding columns.  Is it possible can you please explain?   please

Highlighted
Enthusiast

Re: Is is possible to use Aggregate functions inside CASE statement?

Hi,

 can you please tell me how to delete the duplicate data from a table by keeping single copy of that each duplicated data record.

  Other than using set table method.

    Is there any alternate methods?

Thanks in advance

Enthusiast

Re: Is is possible to use Aggregate functions inside CASE statement?

Hi,

 can anyone explain how to load a particular dept data into table by usng teradata load utilities with an example. 

   Here i need to skip all other dept data.

Thanks in advance