Archives of the TeradataForum

Message Posted: Tue, 04 Jan 2011 @ 07:35:30 GMT

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

Hi Team,

This is my query.

     sel
       substr(case  when T04.Name_Type_cd = 'IN' then T04.Full_Name
       Else case  when T05.Name_Type_cd = 'ONM' then  T05.Org_Name END END,1,50) as
     Des_Longname  ,substr(case  when T04_X.Name_Type_cd = 'ISN' then T04_X.Full_Name
       Else case  when T05_X.Name_Type_cd = 'OSN' then  T05_X.Org_Name END
     END,1,50)  as Des_Shortname from LEFT JOIN INDIVIDUAL_NAME_HIST T04 ON V0100.Party_Id=
     T04.Individual_Party_Id LEFT JOIN ORGANIZATION_NAME_HIST T05 On V0100.Party_Id=
     T05.Org_Party_Id

AS per the above query am getting duplicate records.

if i do as per below, am not getting any duplicate, but am joining same table twise .. is thery any optimization here. not to get duplicate and not to use more temp tables.

     SELECT
         substr(case  when T04.Name_Type_cd = 'IN' then T04.Full_Name
       Else case  when T05.Name_Type_cd = 'ONM' then  T05.Org_Name END END,1,50) as
     Des_Longname  ,substr(case  when T04_X.Name_Type_cd = 'ISN' then T04_X.Full_Name
       Else case  when T05_X.Name_Type_cd = 'OSN' then  T05_X.Org_Name END
     END,1,50)  as Des_Shortname

       From
     DERIVE_CIF_TMP
       LEFT JOIN
     INDIVI_NAME_HIST  T04
      ON V0100.Party_Id= T04.Individual_Party_Id  AND  T04.Name_Type_cd = 'IN'
      AND T04.Data_Source_Cd='OS'
      LEFT JOIN
      ORGAN_NAME_HIST_RPRO T05
      On V0100.Party_Id= T05.Org_Party_Id
      AND  T05.Name_Type_cd = 'ONM'
      AND T05.Data_Source_Cd='OS'
      LEFT JOIN
     INDIVI_NAME_HIST_RPRO  T04_X
      ON V0100.Party_Id= T04_X.Individual_Party_Id  AND T04_X.Data_Source_Cd='OS'
      AND  T04_X.Name_Type_cd = 'ISN'
      LEFT JOIN
      ORGAN_NAME_HIST_RPRO T05_X
      ON V0100.Party_Id= T05_X.Org_Party_Id
      AND T05_X.Data_Source_Cd='OS'
      AND  T05_X.Name_Type_cd = 'OSN'

Regards

Chander

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