This query is sytactically correct and should run without problems.

Dieter

SELECT ITEM_ID
, LOC_ID
, MKT_NBR
, SLS_YR_WK_KEY
, SLS_YEAR
, SLS_WK
, SLS_MNTH
, MUS_VLCTY_IND
, Sum(CASE WHEN MUS_VLCTY_IND = 0 THEN SLS_QTY OVER( PARTITION BY ITEM_ID , MKT_NBR,SLS_YR_WK_KEY)
ELSE SLS_QTY OVER( PARTITION BY ITEM_ID , LOC_ID,SLS_YR_WK_KEY)
END AS AGG_SLS_QTY)
FROM SNLTY_TEMP1_V

Sum( Case Condition I s possible here) ok

SELECT ITEM_ID
, LOC_ID
, MKT_NBR
, SLS_YR_WK_KEY
, SLS_YEAR
, SLS_WK
, SLS_MNTH
, MUS_VLCTY_IND
, Sum(CASE WHEN MUS_VLCTY_IND = 0 THEN SLS_QTY OVER( PARTITION BY ITEM_ID , MKT_NBR,SLS_YR_WK_KEY)
ELSE SLS_QTY OVER( PARTITION BY ITEM_ID , LOC_ID,SLS_YR_WK_KEY)
END AS AGG_SLS_QTY)
FROM SNLTY_TEMP1_V

Sum( Case Condition I s possible here) ok

sum(case condition) and group by the columns will work

Hi

I have a qyery:

Below is the resultset from the query

DC

HS

cuscode

cuname

Lvl1cd

Year_

sales

1800

1400

1,023

AMRUTH DHARA INDUSTRIES

7

2011

919

1800

1400

1,079

D T D C COURIERS CARGO LTD

5

2012

16702

1800

1400

1,092

APOORVA IYENGAR'S BAKERY

3

2011

4011

1800

1400

1,094

SIDDARTHA STEEL & IRON

7

2011

3214

1800

1400

1,094

SIDDARTHA STEEL & IRON

7

2012

5787

1800

1400

1,100

ANAND FLOORINGS

7

2012

4791

1800

1400

1,121

MADHU SWEETS

1

2012

1432

1800

1400

1,125

MAHAVEER MEDICALS

3

2011

8265

1800

1400

1,131

G G WELLING

5

2012

5663

1800

1400

1,134

VIJAYA HAMSA STORES

3

2012

7090

1800

1400

1,140

MOHAN BHANDAR DEPARTMENTAL STORE

3

2011

161983

I want the resultset as below:

DC

HS

cuscode

cuname

Lvl1cd

2011

2012

1800

1400

1,023

AMRUTH DHARA INDUSTRIES

7

919

1800

1400

1,079

D T D C COURIERS CARGO LTD

5

16702

1800

1400

1,092

APOORVA IYENGAR'S BAKERY

3

4011

1800

1400

1,094

SIDDARTHA STEEL & IRON

7

3214

5787

1800

1400

1,100

ANAND FLOORINGS

7

4791

1800

1400

1,121

MADHU SWEETS

1

1432

1800

1400

1,125

MAHAVEER MEDICALS

3

8265

1800

1400

1,131

G G WELLING

5

5663

1800

1400

1,134

VIJAYA HAMSA STORES

3

7090

1800

1400

1,140

MOHAN BHANDAR DEPARTMENTAL STORE

3

161983

TTL

178392

41465

Can anyone give a solution to this

Thanks

Kalyan S R

Hi Kalyan,
it's time for CASE pivot table :-)

When "sales" is calculated as SUM(blabla), you just have to replace it with

SUM(CASE WHEN year_sales = 2011 THEN blabla END) AS "2011",
SUM(CASE WHEN year_sales = 2012 THEN blabla END) AS "2012"

This will return NULL for non-existing, if you want 0 then add "ELSE 0" to the CASE.

Dieter

Hi

Thanks a lot for the response.

My doubt is

substr(ltrim(a.month_id),1,4) as Saal, (in the table it is like 201105,201205..so on...)

          which is givng the output as 2011,2012 etc in the smme coloumn which I want to split

sum (a.net_Sales)

The below did'nt work....

SUM(CASE WHEN Saal ='2011' THEN (a.net_sales) Else 0,end) AS "2011",
SUM(CASE WHEN Saal ='2012' THEN (a.net_sales) Else 0,end) AS "2012"

Can you suggest?

Thanks

Kalyan S R

Hi Kalyan,
you have to move the calculation into the CASE and remove the Saal column:

select DC, HS, cuscode, cuname, Lvl1cd
  SUM(CASE WHEN substr(ltrim(a.month_id),1,4)='2011' THEN (a.net_sales) Else 0,end) AS "2011",
  SUM(CASE WHEN substr(ltrim(a.month_id),1,4)='2012' THEN (a.net_sales) Else 0,end) AS "2012"
from tab
group by 1,2,3,4,5

Dieter

Hi,

Thanks I got it, Another issue now, in the below query if i add one or two mnths more, one or two stores more I get a "2646 Spool space error' Can we optimise the query to achieve this result?

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,

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,

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.month_id in (201205,201105)

and a.St in (10)

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

and c.brh_id=b.brh_id

and c.cust_asrt_section_id=d.cust_asrt_section_id

group by a.store_id,b.hs,b.cust_no,b.cus_name,c.cust_asrt_section_id,cust_asrt_section_desc

order by 1,2;

Thanks'

Kalyan S R

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

Hi,

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

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

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

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

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

Hi

Thanks a lot, issue is solved.

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

Thanks

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.

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

  1     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

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

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

Hi,

Insert all the distinct records (with all fields) into a new  table and delete the old one is what i feel is easy.

yes u r right kalyan but that is not the case i need to that without using second table can any one help me

yes u r right kalyan but that is not the case i need to that without using second table can any one help me

do you have a time stamp in the table?

Hi,

First create the backup of the table as a standby -- Very important

Set the Primary index with concatenation if it is more than one field

use  your dbname....

del from yourtablename
where primary index column in
(
sel primary index column from yourtablename
qualify row_number()over(partition by primary index order by some column) > 1
)

.. This  will keep one record and deletes all the other. you do not need to create any seperate table for this

Hope this may serve the purpose

Thanks kalyan

hi,

 i have one doubt that is on which basis we need to set the nuber of sessions for a script etheir 6 or 8 or ....

in any utility can you please help

Hi Ram,

you should create new topics for new questions:

Go to the appropriate forum, at the top or bottom of the page there's a "create new forum topic"

Dieter

hi frnds ,

1. I ve table like dis table name is EMPL

NAME      DOB    AGE
-------------------------
K.hema   12thmarch 22
d.Hari   7thmay    35
k.kannan 2 apr     55
K.kannan 6 july    55
g.hema   12 march  31

d,hari    7 may      35

here employee id not given, such cases ve same name,same age,same dob den how to make a query for giving random number for empolyees.....

em the beginner of MS-sql any one can help.... other wise give query in oracle