Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Mon, 17 Mar 2003 @ 14:51:04 GMT


     
  <Prev Next>  
<<First
<Prev
Next> Last>>  


Subj:   Several count(distinct) in one select clause
 
From:   Villeneuve Philippe

Hello,

I would like to know how I can correct the following sql code to avoid the error:

<< 3628 : only identical aggregated expressions can use the distinct option >>

how I can i retrieve several count(distinct) in one request? sql (designed for oracle) :

SELECT
DW_PRFL_PART.NUMR_PERS,
DW_PRFL_PART.CODE_SEGM_COMP,
DW_PRFL_PART.CODE_NIV_SERV,
DW_PRFL_PART.CODE_VALR_CLNT,
DW_PRFL_PART.IDNT_EDS_DOMC,
count(distinct(DW_PRFL_TELEC.REFR_PRDT)),
count(distinct(DW_PRFL_CART_AURO.REFR_PRDT)),
count(distinct(DW_PRFL_PROF.NUMR_CHRN_PROF)),
count(distinct(DW_EVNM_COML_PERS.CODE_EVNM_COML)),
count(distinct(DW_SYNTH_PRDT_HORS_CTR.NUMR_SEQN_PRDT_EXTN)),
count(distinct(DW_PRFL_CART.NUMR_CONT_CART))

FROM DW_PRFL_PART
left outer join DW_PRFL_TELEC
on DW_PRFL_PART.NUMR_PERS = DW_PRFL_TELEC.NUMR_PERS
and DW_PRFL_TELEC.DATE_FIN_VALD_INFO='9999-12-31'
and DW_PRFL_TELEC.CODE_ETAT_GENR='O'
left outer join DW_PRFL_CART_AURO
on DW_PRFL_PART.NUMR_PERS = DW_PRFL_CART_AURO.NUMR_PERS
and DW_PRFL_CART_AURO.DATE_FIN_VALD_INFO='9999-12-31'
and DW_PRFL_CART_AURO.CODE_ETAT_GENR='O'
left outer join DW_PRFL_PROF
on DW_PRFL_PART.NUMR_PERS = DW_PRFL_PROF.NUMR_PERS
and DW_PRFL_PROF.DATE_FIN_VALD_INFO='9999-12-31'
left outer join DW_EVNM_COML_PERS
on DW_PRFL_PART.NUMR_PERS = DW_EVNM_COML_PERS.NUMR_PERS
and DW_EVNM_COML_PERS.DATE_FIN_VALD_INFO='9999-12-31'
left outer join DW_SYNTH_PRDT_HORS_CTR
on DW_PRFL_PART.NUMR_PERS = DW_SYNTH_PRDT_HORS_CTR.NUMR_PERS
and DW_SYNTH_PRDT_HORS_CTR.DATE_FIN_VALD_INFO = '9999-12-31'
left outer join DW_PRFL_CART
on DW_PRFL_PART.NUMR_PERS = DW_PRFL_CART.NUMR_PERS_TITL
and DW_PRFL_CART.DATE_FIN_VALD_INFO='9999-12-31'
and DW_PRFL_CART.CODE_ETAT_GENR='O'
WHERE DW_PRFL_PART.NUMR_PERS = 1 and
DW_PRFL_PART.DATE_FIN_VALD_INFO='9999-12-31'

group by
DW_PRFL_PART.NUMR_PERS,
DW_PRFL_PART.CODE_SEGM_COMP,
DW_PRFL_PART.CODE_NIV_SERV,
DW_PRFL_PART.CODE_VALR_CLNT,
DW_PRFL_PART.IDNT_EDS_DOMC

Bests regards
Thanks

Philippe VILLENEUVE
CTRCEAPC



     
  <Prev Next>  
<<First
<Prev
Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016