Post reply

Using the OVER command together with WITH, and Excel with a parameter

  • December 29, 2014 at 5:44 am

    #314165

    I'm am using an Excel with a parameter, in this case a customer-number. For retrieving the data I use a view. In this view I start with a WITH to retrieve the data because I need the customer number in several places in the view. In the beginning all data I need is a 1:1 relation with the customer but problems occurs when there is a 1:n relation, in my case a customer can have more contact persons. When I try to collect the contact person information it always finds the first contact person in the table and that's from a different customer.

    What do I have to do to find the contact person(s) that belongs to my specific customer/?

    with Customer as (

    Select * from [ods].[custtable] where ODSACTIVE = '1' AND DATAAREAID = 'NL01'

    )

    union all

    select src.accountnum, 'Contact Person 1 Name' as 'Field', SRC.NAME as 'Value'

    FROM (SELECT CP.NAME, CT.accountnum, ROW_NUMBER() OVER (ORDER BY CP.PARTYID) AS RN, ct.partyid

    from [ods].[contactperson] cp inner join CUSTOMER CT on ct.partyid = cp.ORGpartyid and ct.DATAAREAID = cp.DATAAREAID

    WHERE cp.DATAAREAID = 'NL01'

    ) AS SRC WHERE SRC.RN = 1

  • December 29, 2014 at 5:56 am

    #1766743

    Table definitions (as CREATE TABLE) and some sample data (as INSERT statements) please, if you want a tested answer.

    Initial guess, your ROW_NUMBER needs a Partition By

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 29, 2014 at 8:41 am

    #1766801

    the partition by was the solution

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply