Help me with this syntax (1Customer code note more)

• GG_BI_GG

  SSCrazy

  Points: 2627

  More actions

  December 4, 2015 at 10:38 am

  #302776

  I've Next Question . I am currently working on a location dimension. The following is not going so well for me. I want to be a customer who has several addresses flatten to one customer. So if there is a type 5 or 1 then I want to have the telephone number and mail in one column but does not occur more than once in the same customer code . You know how I have to change my syntax than the case statement I made now ?

  SELECT

  DISTINCT CT.Accountnum AS CustomerNumber

  ,UPPER(CAST(CT.DataAreaID as nvarchar(250))) AS DataAreaID

  ,DPT.Name AS CustomerName

  ,DPPA.Street AS BillingAddressStreet

  ,DPPA.ZipCode AS BillingAddressPostalCode

  ,DPPA.City AS BillingAddressCity

  ,DPPA.CountryRegionId AS BillingAddressCountry

  ,CASE

  WHEN DPCI.Type = 1

  THEN DPCI.Locator

  ELSE NULL

  END AS TelephoneNumber

  , CASE

  WHEN DPCI.Type = 5

  THEN DPCI.Locator

  ELSE NULL

  END AS FaxNumber

  ,CASE

  WHEN DPCI.Type = 3

  THEN DPCI.Locator

  ELSE NULL

  END AS Website

  ,CASE

  WHEN DPCI.Type = 2

  THEN DPCI.Locator

  ELSE NULL

  END AS EmailAddress

  ,'Customer' AS BusinessRelationShip

  ,CT.LineOfBusinessId AS CustomerType

  --PrimaryCustomerSector

  --SecondaryCustomerSector

  ,CT.Segmentid AS PrimaryIndustrySegment

  ,CT.SPLSecondBusiness AS SecondaryIndustrySegment

  FROM AX2012.CustTable CT

  inner JOIN AX2012.DirPartyTable DPT

  ON DPT.Recid = CT.Party

  INNER JOIN AX2012.DirPartyPostalAddressView DPPA

  ON CT.Party = DPPA.Party

  INNER JOIN AX2012.DirPartyContactInfoView DPCI

  ON CT.Party = DPCI.Party

  where DPPA.ISPRIMARY = 1

  AND DPCI.ISPRIMARY = 1

  AND DPPA.VALIDTO = '2154-12-31 23:59:59.000'

  Order by CT.ACCOUNTNUM

  How i get it from 1 customer! Look picture for results.

• Jeff Moden

  SSC Guru

  Points: 1004558

  More actions

  December 5, 2015 at 8:19 am

  #1844205

  Please see the following...

  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)