quote:

---

Originally posted by aesquis

I have this query and I need a list of customers for a mailing, the first address must be type 97, if is not exist the first record of material receiver and if is not exist the first record of applicant.

The Query works but when there are two differents mails in one of this groups I obtain booth and I only want the first.

hope this was clear

Thanks for all

SELECT DISTINCT
KNVP.VKORG as OKCONO,
KNA1.KUNNR as OKCUNO,
KNA1.NAME1 as OKCUNM,
KNA1.STRAS as OKCUA1,
KNA1.ORT01 as OKCUA2,
KNA1.REGIO as OKCUA3,
KNA1.SPRAS as OKLHCD,
DC_IDIOMES.TexteMail1, DC_IDIOMES.TexteMail2,
Correu = Case
When KNVK.PAFKT = '97' Then ADR697.SMTP_ADDR
When ADR6DEST.SMTP_ADDR IS not null Then ADR6DEST.SMTP_ADDR
When ADR6SOL.SMTP_ADDR IS not null Then ADR6SOL.SMTP_ADDR
ELSE ADR697.SMTP_ADDR
END
FROM
KNA1
LEFT JOIN KNVK as KNVK on KNA1.KUNNR = KNVK.KUNNR and KNA1.MANDT = KNVK.MANDT
LEFT JOIN ADR6 as ADR697 on KNVK.PRSNR = ADR697.PERSNUMBER and KNVK.MANDT = ADR697.CLIENT
LEFT JOIN ADR6 as ADR6DEST on KNA1.ADRNR = ADR6DEST.ADDRNUMBER and KNA1.MANDT = ADR6DEST.CLIENT
LEFT JOIN KNVP as KNVP on KNA1.KUNNR = KNVP.KUNN2 and KNA1.MANDT = KNVP.MANDT and (KNVP.KUNNR like '0001%')
LEFT JOIN KNA1 as KNA1SOL on KNVP.KUNNR = KNA1SOL.KUNNR and KNVP.MANDT = KNA1SOL.MANDT
LEFT JOIN ADR6 as ADR6SOL on KNA1SOL.ADRNR = ADR6SOL.ADDRNUMBER and KNA1SOL.MANDT = ADR6SOL.CLIENT
INNER JOIN DC_IDIOMES ON KNA1.SPRAS = DC_IDIOMES.LHCD AND KNVP.VKORG = DC_IDIOMES.CONO
INNER JOIN
(select row_number() over (partition by LHCD,CONO order by LHCD,CONO) as rnum,* from DC_IDIOMES) as DC_IDIOMES
ON KNA1.SPRAS = DC_IDIOMES.LHCD AND KNVP.VKORG = DC_IDIOMES.CONO AND DC_IDIOMES.rnum=1


WHERE


KNA1.MANDT = '200'
and KNA1.KUNNR = '0002100001'
-- and KNVP.VKORG <> '1000'



Group BY
KNVP.VKORG, KNA1.KUNNR, KNA1.NAME1, KNA1.STRAS, KNA1.ORT01, KNA1.REGIO, KNA1.SPRAS, DC_IDIOMES.TexteMail1, DC_IDIOMES.TexteMail2, ADR697.SMTP_ADDR, KNVK.PAFKT,
ADR6DEST.SMTP_ADDR, ADR6SOL.SMTP_ADDR

---

quote:

---

Originally posted by aesquis

Thanks for your help. I get the mail from :

Correu = Case
When KNVK.PAFKT = '97' Then ADR697.SMTP_ADDR
When ADR6DEST.SMTP_ADDR IS not null Then ADR6DEST.SMTP_ADDR
When ADR6SOL.SMTP_ADDR IS not null Then ADR6SOL.SMTP_ADDR
ELSE ADR697.SMTP_ADDR

The DC_IDIOMES it's only for text and signature depending the language.

I tried this suggestion and I obtained this error:


'row_number' Is not a knowledge function

But I really apreciate your help

---

quote:

---

Originally posted by aesquis

Dear sir, I'm using SQL server 2008, and the error msg was this

Msg 195, Level 15, State 10, Line 26
'row_number' no es un nombre de función reconocido.

---

EXEC sp_dbcmptlevel 'yourdbname'