Post reply

Help on UDF

  • December 13, 2007 at 7:45 am

    #179876

    I have a table which looks like this

    ConsultantID

    EffectiveDate

    FirstName

    LastName

    SponsorID

    The sponsorid is the consultantID of the consultants supervisor. I am trying to design a UDF that will return me the sponsorname (a derived column). Any ideas.

    Thanks,
    Art
    Database Analyst
    Tastefully Simple, Inc.

    alorenzini@tastefullysimple.com
    " Some days, it's not even worth chewing through the restraints!

  • December 13, 2007 at 8:00 am

    #759837

    Can you pull it back in a SELECT statement?

    Meaning if I give you a sponsor ID, can you not just query for the sponsor name?

    It doesn't appear obvious why you want a UDF or is this just to make it simple. Or are you adding this as a "computed column" somehow.

    select a.firstname + ' ' + b.lastname

    from table

    where consultantid = @sponsorid

    would work, where you pass in the sponsor ID. Wrap that in your function, use it in the column.

  • December 13, 2007 at 8:12 am

    #759848

    part of the issue is that the consultant table has multiple records for a given consultant (it's not a one to one). Also we are passing in the consultantID.

    Thanks,
    Art
    Database Analyst
    Tastefully Simple, Inc.

    alorenzini@tastefullysimple.com
    " Some days, it's not even worth chewing through the restraints!

  • December 13, 2007 at 8:15 am

    #759851

    This is the code that I am currently working with.

    DECLARE@ConsultantIDASnVarChar(50)

    ,@PeriodDateASDateTime

    SET @ConsultantID = '0000344'

    SET @PeriodDate = '2007-12-03 11:26:44.263'

    SELECT A.ConsultantID

    ,A.EffectiveDate

    ,A.FirstName + ' ' + A.LastName AS ConsultantName

    ,A.SponsorID

    ,(SELECT DISTINCT FirstName + ' ' + LastName FROM dbo.uvwConsultantDownLine

    WHERE @consultantID = SponsorID AND EffectiveDate='2007-12-03 11:26:44.263') AS SponsorName

    FROM dbo.uvwConsultantDownLine a

    WHERE a.Consultantid = @ConsultantID AND a.EffectiveDate=@PeriodDate

    But when I run this I get the following error:

    Msg 512, Level 16, State 1, Line 7

    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, = or when the subquery is used as an expression.

    Thanks,
    Art
    Database Analyst
    Tastefully Simple, Inc.

    alorenzini@tastefullysimple.com
    " Some days, it's not even worth chewing through the restraints!

  • December 13, 2007 at 8:34 am

    #759864

    The error is explaining the problem. The subquery is being used as a column, that means it has to return a single row because you can't have two or more rows of data inside of a column inside of a single row of data.

    You can move that part of the query to something like this:

    SELECT A.ConsultantID

    ,A.EffectiveDate

    ,A.FirstName + ' ' + A.LastName AS ConsultantName

    ,A.SponsorID

    ,b.SponsorName

    FROM dbo.uvwConsultantDownLine a

    JOIN (SELECT DISTINCT FirstName + ' ' + LastName AS SponsorName,SponsorId,EffectiveDate FROM dbo.uvwConsultantDownLine) AS b

    ON b.SponsorId = a.ConsultantId and b.EffectiveDate = @PeriodDate

    WHERE a.Consultantid = @ConsultantID AND a.EffectiveDate=@PeriodDate

    Now you'll get duplicate rows, where a consultant can match more than one sponsor.

    I'd stay away from functions for queries like this.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • December 13, 2007 at 8:43 am

    #759872

    I think I solved my issue: Final code:

    SELECT A.ConsultantID

    ,A.EffectiveDate

    ,A.FirstName + ' ' + A.LastName as ConsultantName

    ,A.BillToFirstName + ' ' + A.BillToLastName as BillToName

    ,BillToAddressLine1

    ,BillToAddressLine2

    ,BillToCity

    ,BillToState

    ,BillToZip

    ,BillToPhone

    ,A.ShiptoFirstName + ' ' + A.ShipToLastName as ShipToName

    ,A.ShipToAddressLine1

    ,A.ShipToAddressLine2

    ,A.ShipToCity

    ,A.ShipToState

    ,A.ShipToZip

    ,A.ShipToPhone

    ,A.SponsorID

    ,(SELECT DISTINCT B.FirstName + ' ' + B.LastName FROM dbo.consultant

    INNER JOIN dbo.uvwConsultantDownLine AS B ON A.SponsorID = B.ConsultantID

    WHERE a.EffectiveDate=@PeriodDate) As SponsorName

    FROM dbo.uvwConsultantDownLine a

    WHERE a.Consultantid = @ConsultantID AND a.EffectiveDate=@PeriodDate

    Thanks

    Thanks,
    Art
    Database Analyst
    Tastefully Simple, Inc.

    alorenzini@tastefullysimple.com
    " Some days, it's not even worth chewing through the restraints!

Viewing 6 posts - 1 through 5 (of 5 total)

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