April 9, 2010 at 7:53 am
Do you have a batch terminator "GO" at the very END of the procedure and function code window?
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgApril 9, 2010 at 8:29 am
i just added it(Batch terminator) and still giving the same error message
April 9, 2010 at 8:33 am
It doesn't APPEAR to be in the proc. One of your functions maybe?
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgApril 9, 2010 at 8:46 am
both make a call to this function which shown below
and that's where i think the error is originating
CREATE FUNCTION [dbo].[fnUTUnitTrustNAV] (
@UnitTrustID INT,
@ValueDate SMALLDATETIME
)
RETURNS @tbl TABLE (
[ID] BIGINT,
[CallBalance] DECIMAL(38, 5),
[TradingBalance] DECIMAL(38, 5),
[UnitTrustTradingBalance] DECIMAL(38, 5),
[MoneyMarket] DECIMAL(38, 5),
[FixtureBalance] DECIMAL(38, 5),
[Equities] DECIMAL(38, 5),
[SharesBalance] DECIMAL(38, 5),
[OtherAssets] DECIMAL(38, 5),
[OutstandingDividends] DECIMAL(38, 5),
[AccruedInterest] DECIMAL(38, 5),
[GAV] DECIMAL(38, 5)
)
AS
BEGIN
DECLARE @UtCpID INT
DECLARE @EQPortfolioValue DECIMAL(38, 8)
,@MMPortfolioValue DECIMAL(38, 8)
,@OutstandingDividends DECIMAL(38, 8)
,@OtherAssetsValue DECIMAL(38, 8)
,@UnitTrustTradingBalance DECIMAL(38, 8)
,@TradingBalance DECIMAL(38, 8)
,@CallBalance DECIMAL(38, 8)
,@SharesBalance DECIMAL(38, 8)
,@GAV DECIMAL(38, 8)
SELECT @UtCpID= [CounterpartyID]
FROM tblUTUnitTrust
WHERE [ID] = @UnitTrustID
INSERT INTO @tbl (
[ID],
[CallBalance],
[TradingBalance],
[UnitTrustTradingBalance],
[FixtureBalance],
[SharesBalance],
[OtherAssets],
[OutstandingDividends]
)
SELECT@UnitTrustID,
SUM([CallBalance]),
SUM([TradingBalance]),
SUM([UnitTrustTradingBalance]),
SUM([FixtureBalance]),
SUM([SharesBalance]),
SUM([OtherAssets]),
SUM([OutStandingDividends])
FROM (
SELECTCASE
WHEN [AccountType] = dbo.fnAccountType('Call') THEN dbo.fnSiftAvailableBalance([ID], @ValueDate)
ELSE 0
END AS [CallBalance],
CASE
WHEN [AccountType] = dbo.fnAccountType('Trading') THEN dbo.fnSiftAvailableBalance([ID], @ValueDate)
ELSE 0
END AS [TradingBalance],
CASE
WHEN [AccountType] = dbo.fnAccountType('Unit Trust Trading') THEN dbo.fnSiftAvailableBalance([ID], @ValueDate)
ELSE 0
END AS [UnitTrustTradingBalance],
CASE
WHEN [AccountType] = dbo.fnAccountType('Fixture') THEN dbo.fnSiftAvailableBalance([ID], @ValueDate)
ELSE 0
END AS [FixtureBalance],
CASE
WHEN [AccountType] = dbo.fnAccountType('Shares') THEN dbo.fnSiftAvailableBalance([ID], @ValueDate)
ELSE 0
END AS [SharesBalance],
ISNULL(dbo.fnOtherAssetPortfolioValue(acc.[CounterpartyID], @ValueDate), 0) AS [OtherAssets],
ISNULL(dbo.fnAccountOutstandingDividends(acc.[ID], @ValueDate), 0) AS [OutstandingDividends]
FROM tblAccount acc
WHERE ([CounterpartyID] = @UtCpID OR [UnitTrustID] = @UnitTrustID)
AND [acc].[Used] = 1
) AS itd
UPDATE @tbl
SET [Equities] = dbo.fnEQSharesPortfolioValue(@UtCpID, @ValueDate)
DECLARE @AccruedInterest DECIMAL(38, 5);
DECLARE @AccountID INT;
SELECT @AccountID = acc.[ID]
FROM [tblAccount] acc
INNER JOIN tblAccountType act
ON act.[Type] = acc.[AccountType] AND act.[MMAllowAllocation] = 1
WHERE ([CounterpartyID] = @UtCpID OR [UnitTrustID] = @UnitTrustID)
AND [acc].[Used] = 1
IF @AccountID > 0 BEGIN
SELECT @AccruedInterest = SUM([InterestAccrued])
FROM [fnMMExtendedDealDetails]
(NULL, --@DealID INT
@AccountID, --@AccountID INT,
NULL, --@StartValueDate DATETIME
NULL, --@EndValueDate DATETIME
@ValueDate, --@StartMaturityDate DATETIME
NULL, --@EndMaturityDate DATETIME
@ValueDate, --DATETIME
1, --@Confirmed BIT
0, --@Rejected BIT
NULL, --@Matured BIT
NULL, --@Settled BIT
0 --@Terminated BIT
)
END;
UPDATE @tbl SET [AccruedInterest] = ISNULL(@AccruedInterest, 0);
--calculating GAV
SELECT@EQPortfolioValue = SUM([Equities]),
@CallBalance = SUM([CallBalance]),
@TradingBalance = SUM([TradingBalance]),
@UnitTrustTradingBalance = SUM([UnitTrustTradingBalance]),
@MMPortfolioValue = SUM([FixtureBalance]),
@SharesBalance = SUM([SharesBalance]),
@OtherAssetsValue = SUM([OtherAssets]),
@OutstandingDividends = SUM([OutStandingDividends]),
@AccruedInterest = SUM([AccruedInterest])
FROM @tbl
SELECT@GAV =
@EQPortfolioValue +
@MMPortfolioValue +
@OtherAssetsValue +
@OutstandingDividends +
@SharesBalance +
@UnitTrustTradingBalance +
@CallBalance +
@TradingBalance +
@AccruedInterest
UPDATE @tbl SET [GAV] = ISNULL(@GAV, 0);
RETURN
END
GO
This function calculates the GAV Value that is used by the stored proc and function throwing that err message
to get GAV value the above function i use another function which selects GAV returned by the above function [dbo].[fnUTUnitTrustNAV]
The function passing GAV to the proc and function throwing an err message is given by:
CREATE FUNCTION [dbo].[fnGetGAVValue]
(
@UnitTrustID INT,
@ValueDate SMALLDATETIME
)
RETURNS DECIMAL(38, 5)
AS
BEGIN
DECLARE @GAV DECIMAL(38, 5);
SELECT @GAV = ISNULL([GAV], 0)
FROM [dbo].[fnUTUnitTrustNAV] (@UnitTrustID, @ValueDate)
RETURN ISNULL(@GAV ,0)
END
GO
Viewing 4 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply