3707 Error

Database
Enthusiast

3707 Error

SELECT

sales_segment,

pickup_yyyymm,

AVERAGE (Days) OVER (PARTITION  BY  sales_segment + pickup_yyyymm ORDER BY  sales_segment,pickup_yyyymm DESC)  AS mean_days,

MAX (Days) OVER (PARTITION  BY sales_segment + pickup_yyyymm ORDER BY sales_segment,pickup_yyyymm DESC) AS max_days,

MIN (Days) OVER (PARTITION  BY   sales_segment +pickup_yyyymm ORDER BY  sales_segment,pickup_yyyymm DESC) AS min_days,

MEDIAN (Days) OVER (PARTITION  BY  sales_segment + pickup_yyyymm ORDER BY  sales_segment,pickup_yyyymm DESC) AS median_days

FROM 

(

SELECT 

 A.shp_pro_nbr,

--B.sales_align_terr_cd,

CASE 

when b.sales_div_nbr=1 and b.sales_grp_nbr<>2  and b.sales_terr_nbr in (20,21,22,23,24,25,26,27,28,29,70,71,72,73,74,75,76,77,78,79) then 'FSAD'

 when b.sales_div_nbr=1 and b.sales_grp_nbr<>2  and b.sales_terr_nbr in (30,31,32,33,34,35,36,37,38,39,50,51,52,53,54,55,56,57,58,59) then 'FSMD'

 when b.sales_div_nbr=1 and b.sales_grp_nbr<>2  and b.sales_terr_nbr in (40,41,42,43,44,45,46,47,48,49) then 'FSSD'

 when b.sales_div_nbr=1 and b.sales_grp_nbr<>2 then 'FS Other'

 when b.sales_div_nbr=1 and b.sales_grp_nbr=2 and b.sales_org_nbr=7 and b.sales_area_nbr=3 then 'BSF'

 when b.sales_div_nbr=1 and b.sales_grp_nbr=2 and b.sales_org_nbr=7 then 'BSI'

 when b.sales_div_nbr=1 and b.sales_grp_nbr=2 and b.sales_org_nbr=8 then 'Presls'

 when b.sales_div_nbr=2 then 'WWS'

 when b.sales_div_nbr=3 then 'Specialty'

 when b.sales_div_nbr=8 then 'Non-US'

 when b.sales_div_nbr=90 then 'MKTG'

 when b.sales_div_nbr=80 then 'WWS'

 else 'None' end as sales_segment,

--A.eff_dt,

--A.pckup_dt,

SUBSTR( CAST(CAST (A.pckup_dt AS DATE) AS DATE FORMAT 'yyyy/mm/dd'),1,7) AS pickup_yyyymm,

(CAST( A.eff_dt AS DATE) - CAST (A.pckup_dt AS DATE)  ) AS Days

 FROM ISH_FEDXFGT_PROD_VIEW_DB.fxf_ship_rev_comp  A

INNER JOIN UI_ISH_PROD_DB.sales_quarter_end_alignment B

ON A.payor_cust_nbr = B.cf_cust_nbr AND B.align_typ_cd ='P'AND  fscl_qtr_nbr = 4 AND fscl_yr_nbr = 2016 AND B.prim_cvge_flg= 'Y'

AND CAST(A.pckup_dt AS DATE) BETWEEN ADD_MONTHS(CURRENT_DATE,-12) AND CURRENT_DATE 

GROUP BY

1,2,3,4

) a

GROUP BY 1,2;

5 REPLIES
Junior Contributor

Re: 3707 Error

MEDIAN is only an aggregate function.

But this query makes no sense, you apply an OLAP function on unique rows.

What are you trying to do?

Enthusiast

Re: 3707 Error

Thanks a lot for your prompt resposne. I am very new to teradata. I want to calculate a couple of statistical measures on the Days column below. I successfully calculated the Median using one of your proposed solution but I stll have not completely understood the functioning due to my lack of knowledge and experience. I have attched the output screenshot of the output as well. Can you please help me completely understand the logic for median calculation and how to fix the little glitch in the output.

Thanks! 

SELECT 

sales_segment,

pickup_yyyymm,

Days,

COUNT(*) over(partition by sales_segment,pickup_yyyymm order by sales_segment,pickup_yyyymm desc)  AS no_of_records,

SUM(Days) over(partition by sales_segment,pickup_yyyymm order by sales_segment,pickup_yyyymm desc)  AS sum_days,

AVERAGE(Days) over(partition by sales_segment,pickup_yyyymm order by sales_segment,pickup_yyyymm desc)  AS AVG_days,

Min(Days) over(partition by sales_segment,pickup_yyyymm order by sales_segment,pickup_yyyymm desc)  AS Min_days,

MAX (Days) OVER(PARTITION BY sales_segment,pickup_yyyymm ORDER BY sales_segment,pickup_yyyymm DESC)  AS Max_days,

CASE 

 WHEN ROW_NUMBER( ) OVER (PARTITION BY sales_segment,pickup_yyyymm ORDER BY Days) =  COUNT(*) OVER (PARTITION BY sales_segment,pickup_yyyymm) / 2 +1

 THEN 

   CASE 

     WHEN COUNT (*) OVER (PARTITION BY  sales_segment,pickup_yyyymm) MOD 2=1 THEN Days

     ELSE AVERAGE(Days) OVER(PARTITION BY sales_segment,pickup_yyyymm ORDER BY Days ROWS 1 PRECEDING) 

   END 

 END AS Median_Days     

FROM

(SELECT 

sales_segment,

pickup_yyyymm,

Days

FROM

(SELECT 

 A.shp_pro_nbr,

CASE 

when b.sales_div_nbr=1 and b.sales_grp_nbr<>2  and b.sales_terr_nbr in (20,21,22,23,24,25,26,27,28,29,70,71,72,73,74,75,76,77,78,79) then 'FSAD'

 when b.sales_div_nbr=1 and b.sales_grp_nbr<>2  and b.sales_terr_nbr in (30,31,32,33,34,35,36,37,38,39,50,51,52,53,54,55,56,57,58,59) then 'FSMD'

 when b.sales_div_nbr=1 and b.sales_grp_nbr<>2  and b.sales_terr_nbr in (40,41,42,43,44,45,46,47,48,49) then 'FSSD'

 when b.sales_div_nbr=1 and b.sales_grp_nbr<>2 then 'FS Other'

 when b.sales_div_nbr=1 and b.sales_grp_nbr=2 and b.sales_org_nbr=7 and b.sales_area_nbr=3 then 'BSF'

 when b.sales_div_nbr=1 and b.sales_grp_nbr=2 and b.sales_org_nbr=7 then 'BSI'

 when b.sales_div_nbr=1 and b.sales_grp_nbr=2 and b.sales_org_nbr=8 then 'Presls'

 when b.sales_div_nbr=2 then 'WWS'

 when b.sales_div_nbr=3 then 'Specialty'

 when b.sales_div_nbr=8 then 'Non-US'

 when b.sales_div_nbr=90 then 'MKTG'

 when b.sales_div_nbr=80 then 'WWS'

 else 'None' end as sales_segment,

SUBSTR( CAST(CAST (A.pckup_dt AS DATE) AS DATE FORMAT 'yyyy/mm/dd'),1,7) AS pickup_yyyymm,

(CAST( A.eff_dt AS DATE) - CAST (A.pckup_dt AS DATE)  ) AS Days 

 FROM ISH_FEDXFGT_PROD_VIEW_DB.fxf_ship_rev_comp  A

INNER JOIN UI_ISH_PROD_DB.sales_quarter_end_alignment B

ON A.payor_cust_nbr = B.cf_cust_nbr AND B.align_typ_cd ='P'AND  fscl_qtr_nbr = 4 AND fscl_yr_nbr = 2016 AND B.prim_cvge_flg= 'Y'

AND CAST(A.pckup_dt AS DATE) BETWEEN ADD_MONTHS(CURRENT_DATE,-1) AND CURRENT_DATE 

GROUP BY

1,2,3,4

) a

GROUP BY 1,2,3)b

Enthusiast

Re: 3707 Error

Can I display the median value for all the rows instead of a ? for that set. Because I need it for a report.

Junior Contributor

Re: 3707 Error

The last query in my post on MEDIAN (http://developer.teradata.com/blog/dnoeth/2011/06/missing-functions-percentile-disc-percentile-cont-... how to get the it as OLAP result, you need to add another nesting level:

SELECT 
sales_segment,
pickup_yyyymm,
...,
MIN(Median_Days) over(partition by sales_segment,pickup_yyyymm) AS Median_Days
FROM
(
SELECT
sales_segment,
pickup_yyyymm,
Days,
COUNT(*) over(partition by sales_segment,pickup_yyyymm) AS no_of_records,
SUM(Days) over(partition by sales_segment,pickup_yyyymm) AS sum_days,
AVERAGE(Days) over(partition by sales_segment,pickup_yyyymm) AS AVG_days,
Min(Days) over(partition by sales_segment,pickup_yyyymm) AS Min_days,
MAX (Days) OVER(PARTITION BY sales_segment,pickup_yyyymm) AS Max_days,
CASE
WHEN ROW_NUMBER( ) OVER (PARTITION BY sales_segment,pickup_yyyymm ORDER BY Days) = COUNT(*) OVER (PARTITION BY sales_segment,pickup_yyyymm) / 2 +1
THEN
CASE
WHEN COUNT (*) OVER (PARTITION BY sales_segment,pickup_yyyymm) MOD 2=1 THEN Days
ELSE AVERAGE(Days) OVER(PARTITION BY sales_segment,pickup_yyyymm ORDER BY Days ROWS 1 PRECEDING)
END
END AS Median_Days
FROM
(
SELECT
sales_segment,
pickup_yyyymm,
Days
FROM
(
SELECT
A.shp_pro_nbr,
CASE
when b.sales_div_nbr=1 and b.sales_grp_nbr<>2 and b.sales_terr_nbr in (20,21,22,23,24,25,26,27,28,29,70,71,72,73,74,75,76,77,78,79) then 'FSAD'
when b.sales_div_nbr=1 and b.sales_grp_nbr<>2 and b.sales_terr_nbr in (30,31,32,33,34,35,36,37,38,39,50,51,52,53,54,55,56,57,58,59) then 'FSMD'
when b.sales_div_nbr=1 and b.sales_grp_nbr<>2 and b.sales_terr_nbr in (40,41,42,43,44,45,46,47,48,49) then 'FSSD'
when b.sales_div_nbr=1 and b.sales_grp_nbr<>2 then 'FS Other'
when b.sales_div_nbr=1 and b.sales_grp_nbr=2 and b.sales_org_nbr=7 and b.sales_area_nbr=3 then 'BSF'
when b.sales_div_nbr=1 and b.sales_grp_nbr=2 and b.sales_org_nbr=7 then 'BSI'
when b.sales_div_nbr=1 and b.sales_grp_nbr=2 and b.sales_org_nbr=8 then 'Presls'
when b.sales_div_nbr=2 then 'WWS'
when b.sales_div_nbr=3 then 'Specialty'
when b.sales_div_nbr=8 then 'Non-US'
when b.sales_div_nbr=90 then 'MKTG'
when b.sales_div_nbr=80 then 'WWS'
else 'None' end as sales_segment,

--A.eff_dt,
--A.pckup_dt,
SUBSTR( CAST(CAST (A.pckup_dt AS DATE) AS DATE FORMAT 'yyyy/mm/dd'),1,7) AS pickup_yyyymm,
(CAST( A.eff_dt AS DATE) - CAST (A.pckup_dt AS DATE) ) AS Days
FROM ISH_FEDXFGT_PROD_VIEW_DB.fxf_ship_rev_comp A
INNER JOIN UI_ISH_PROD_DB.sales_quarter_end_alignment B
ON A.payor_cust_nbr = B.cf_cust_nbr AND B.align_typ_cd ='P'AND fscl_qtr_nbr = 4 AND fscl_yr_nbr = 2016 AND B.prim_cvge_flg= 'Y'
AND CAST(A.pckup_dt AS DATE) BETWEEN ADD_MONTHS(CURRENT_DATE,-24) AND CURRENT_DATE
GROUP BY 1,2,3,4
) a
GROUP BY 1,2,3
)b
) as dt

I removed all the order by sales_segment,pickup_yyyymm desc because it's not needed.

Another remark on the pickup_yyyymm calculation, you don't need a substring:

TRIM(CAST (A.pckup_dt FORMAT 'yyyy/mm')) AS pickup_yyyymm,

Would be more efficient if there's no cast to string at all:

EXTRACT(YEAR FROM A.pckup_dt) * 100 + EXTRACT(MONTH FROM A.pckup_dt)
Enthusiast

Re: 3707 Error

Thanks a ton for all the help,guidance and suggestion. The code works superfast now. You have made my day:)