Salut, je sais plus ou j en suis ...cherche une solution a ajouter a ma jointure pour finaliser cet sql (voir ci-dessous) et passer a autres choses.
Si pas de reponses ben va falloir que je fasse autrement..
le fameux sql: si dans la table aliasreferencetype (droite) j ai 2 records pour la table trading_partner (gauche) , je retrouve dans le resultat de ma requete 2 lignes ayant le meme trading_partner_ID ...
je souhaiterai ne prendre que le 1er record de la table aliasreferencetype pour chaque record de la table trading_partner en jointure...(par left join)
MARCI!
SELECT
cast(OATSTDB.OA_GET_CMNT_FLAG('COTRDPTP',T1.Trading_Partner_ID) as
char(1)) as Comment_Flag,
T1.Trading_Partner_ID,
T1.TradingPartnerNumber,
T1.Party_Role,
T1.Name,
cast(OATSTDB.OA_GET_CODE_VALUE(T1.StatusCodeID) as char(5)) as Status_Code,
T2.ReferenceTypeCode as Alias_Type,
case
when t2.referencetablename = 'NATIONAL_VENDOR_REFERENCE'
then(select Dec(t3.National_Vendor_Number,10,0)
from OATSTDB.National_Vendor_reference t3
where t3.trading_Partner_ID = t1.Trading_partner_ID)
when t2.referencetablename = 'ACCOUNTING_VENDOR_REFERENCE'
then(select dec(digits(t4.ap_Vendor_Number) || digits(t4.ap_vendor_suffix),10,0)
from OATSTDB.accounting_vendor_reference t4
where t4.trading_Partner_ID = t1.Trading_partner_ID)
when t2.referencetablename = 'BUY_VENDOR_REFERENCE'
then(select dec(digits(t5.buy_Vendor_Number) || digits(t5.buy_vendor_suffix),10,0 )
from OATSTDB.buy_Vendor_Reference t5
where t5.trading_Partner_ID = t1.Trading_partner_ID)
end as alias_number ,
case
when (select count(*) from OATSTDB.aliasreferencetype t6 where t6.trading_partner_ID = t1.trading_partner_ID) > 1
then 'Y'
else 'N'
end as alias_multiple
FROM OATSTDB.trading_partner t1
left join
OATSTDB.Aliasreferencetype T2
on
t1.Trading_Partner_ID = T2.Trading_Partner_ID