Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2004
    Posts
    25

    Unanswered: User Defined Function Help!

    I am trying to compare the data from one table (shipments) with the data from a view. The point of the function is to match the correct territory with the account depending on the data. Basically, I this deals with accounts that are transfering from one territory to another. The transfers take effect on the first day of the month, retroactive, therefore we need to allocate the sales data to the correct territory in the shipments table. Here is my function. Can someone tell me how I can get this to work and insert a territory ID for the account that has transfered into the shipments table?

    CREATE FUNCTION fnShipments ()
    RETURNS @Shipments TABLE (AccountID CHAR(10), DateInvoice DateTime, DollarShipments Money, TerritoryID CHAR(10))
    AS
    BEGIN
    INSERT @Shipments (AccountID, DateInvoice, DollarShipments, TerritoryID)
    SELECT Shipments.AccountID, Shipments.DateInvoice, DollarShipments, ISNULL((SELECT TerritoryID FROM vwAccountTransfers

    WHERE Shipments.AccountID = vwAccountTransfers.AccountID

    AND vwAccountTransfers.EffectiveMonth =

    (SELECT MIN(EffectiveMonth)
    FROM vwAccountTransfers

    WHERE Shipments.AccountID = vwAccountTransfers.AccountID
    AND DatePart(m,Shipments.DateInvoice) < vwAccountTransfers.EffectiveMonth)),
    (SELECT TerritoryID FROM vwAccountTransfers
    WHERE Shipments.AccountID = vwAccountTransfers.AccountID
    AND vwAccountTransfers.EffectiveMonth Is Null )
    ) AS Territory
    FROM Shipments

    ORDER BY Shipments.AccountID, Shipments.DateInvoice;
    RETURN
    END

  2. #2
    Join Date
    Aug 2003
    Location
    Kingston, Ontario
    Posts
    106
    User functions as I understand it are not allowed to have any side-effects. This includes changing data in tables.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •