Archives of the TeradataForum

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

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

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>>