January 6, 2009 at 5:54 pm
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Description:APL Audit report dataset
-- =============================================
ALTER PROCEDURE [dbo].[rpt_APL_Audit_monthenddate]
-- Parameters
@pAsOfDate datetime,
@pPortAccID int = null
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
SET NOCOUNT ON;
--Main Query
declare @_AsOfDate as datetime
declare @_PortAccID as int
declare @_CashSecId as varChar(15)
set @_AsOfDate = dbo.svf_GetPriorMonthEndDate_asofdate[/color]--@pAsOfDate
set @_PortAccID = @pPortAccID
set @_CashSecId = dbo.svf_GetCashSecId('USD')
--Set for Query Debug
--set @_AsOfDate = convert(datetime,'20080220',112)
--set @_PortAccID = 8074
select p.AsOfDate,
p.PortAccID,
a.PortCode,
a.PortName,
p.MgrGrpCode,
h.SecMajGrpId,
h.SecMajIssueGrpName,
h.SecMinGrpId,
h.SecMinIssueGrpName,
p.Units,
s.IssuerName,
convert(decimal(18,3),nullif(s.CpnRate,0)) as CpnRate,
s.MatDate,
p.PrimarySecId,
s.Ticker,
p.Cost,
case when p.Units = 0 then
0
else
p.Cost / p.Units / s.TradeUnits
end as UnitCost,
p.ClosePrice,
p.MarketVal
from vew_Position p
left join vew_Portfolio a on p.PortAccID = a.PortAccID
left join vew_Security s on p.PrimarySecId = s.PrimarySecId
and p.AsOfDate = s.AsOfDate
left join vew_Hierarcy_SecIssue h on s.SecIssueTypeId = h.SecIssueTypeId
where p.AsOfDate = @_AsOfDate
and p.PortAccID = isnull(@_PortAccID, p.PortAccID)
and s.SecIssueTypeId not in (28, 30) -- Cash, CashEquivalent
union all
select c.AsOfDate,
c.PortAccID,
a.PortCode,
a.PortName,
NULL,
h.SecMajGrpId,
h.SecMajIssueGrpName,
h.SecMinGrpId,
h.SecMinIssueGrpName,
null,
s.IssuerName,
null,
null,
s.PrimarySecId,
s.Ticker,
SUM(c.Cash + c.CashEquivalent),
1,
1,
SUM(c.Cash + c.CashEquivalent)
from vew_Value_MgrGrp c
left join vew_Portfolio a on c.PortAccID = a.PortAccID
left join vew_Security s on @_CashSecId= s.PrimarySecId
and c.AsOfDate = s.AsOfDate
left join vew_Hierarcy_SecIssue h on 30 = h.SecIssueTypeId
where c.AsofDate = @_AsOfDate
and c.PortAccID = isnull(@_PortAccID, c.PortAccID)
and Cash + c.CashEquivalent > 0
GROUP BY c.AsOfDate,
c.PortAccID,
a.PortCode,
a.PortName,
--c.MgrGrpCode,
h.SecMajGrpId,
h.SecMajIssueGrpName,
h.SecMinGrpId,
h.SecMinIssueGrpName,
s.IssuerName,
s.PrimarySecId,
s.Ticker
GRANT EXECUTE ON rpt_APL_Audit_monthenddate TO PUBLIC
END
i try put set @_AsOfDate = dbo.svf_GetPriorMonthEndDate_asofdate
it giving error : The multi-part identifier "dbo.svf_GetPriorMonthEndDate_asofdate" could not be bound.
January 7, 2009 at 3:26 am
Put parenthesis around the function call...
--Ramesh
January 7, 2009 at 8:38 am
i have done that still giving same error
January 7, 2009 at 9:04 am
UDFs have to be followed by parentheses, even if they don't have input parameters.
Is your command:
set @_AsOfDate = dbo.svf_GetPriorMonthEndDate_asofdate
or is it:
set @_AsOfDate = dbo.svf_GetPriorMonthEndDate_asofdate()
If it doesn't have the parentheses, it will give a binding error, since it doesn't know it's a function.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 7, 2009 at 11:19 am
i have done
set @_AsOfDate = dbo.svf_GetPriorMonthEndDate_asofdate()
it not giving me error
but i am not getting any data
i am calling function dbo.svf_GetPriorMonthEndDate_asofdate()
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Description:Get Prior Month End Date
-- =============================================
ALTER FUNCTION [dbo].[svf_GetPriorMonthEndDate_asofdate]
(
--parameters
--@pDate datetime
)
RETURNS datetime
AS
BEGIN
-- Return variable
DECLARE @pdate datetime
DECLARE @asofdate datetime
set @asofdate = @asofdate
set @pdate = CONVERT(char(10), DATEADD(day, - (1 * DATEPART(day, @asofdate)), @asofdate), 101)
--Main Query
-- Return the result of the function
RETURN @pdate
END
January 7, 2009 at 11:25 am
All those variables are empty... null is the only thing that can come out of this.
What exactly do you need to do? There's no need for a function here (unless you are just learning about tsql).
January 7, 2009 at 11:27 am
can i set
set @_AsOfDate = @pAsOfDate
set @_AsOfDate = dbo.svf_GetPriorMonthEndDate_asofdate()
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Description:APL Audit report dataset
-- =============================================
ALTER PROCEDURE [dbo].[rpt_APL_Audit_monthenddate]
-- Parameters
@pAsOfDate datetime,
@pPortAccID int = null
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
SET NOCOUNT ON;
--Main Query
declare @_AsOfDate as datetime
declare @_PortAccID as int
declare @_CashSecId as varChar(15)
set @_AsOfDate = @pAsOfDate
set @_AsOfDate = dbo.svf_GetPriorMonthEndDate_asofdate()
set @_PortAccID = @pPortAccID
set @_CashSecId = dbo.svf_GetCashSecId('USD')
--Set for Query Debug
--set @_AsOfDate = convert(datetime,'20080220',112)
--set @_PortAccID = 8074
select p.AsOfDate,
p.PortAccID,
a.PortCode,
a.PortName,
p.MgrGrpCode,
h.SecMajGrpId,
h.SecMajIssueGrpName,
h.SecMinGrpId,
h.SecMinIssueGrpName,
p.Units,
s.IssuerName,
convert(decimal(18,3),nullif(s.CpnRate,0)) as CpnRate,
s.MatDate,
p.PrimarySecId,
s.Ticker,
p.Cost,
case when p.Units = 0 then
0
else
p.Cost / p.Units / s.TradeUnits
end as UnitCost,
p.ClosePrice,
p.MarketVal
from vew_Position p
left join vew_Portfolio a on p.PortAccID = a.PortAccID
left join vew_Security s on p.PrimarySecId = s.PrimarySecId
and p.AsOfDate = s.AsOfDate
left join vew_Hierarcy_SecIssue h on s.SecIssueTypeId = h.SecIssueTypeId
where p.AsOfDate = @_AsOfDate
and p.PortAccID = isnull(@_PortAccID, p.PortAccID)
and s.SecIssueTypeId not in (28, 30) -- Cash, CashEquivalent
union all
select c.AsOfDate,
c.PortAccID,
a.PortCode,
a.PortName,
NULL,
h.SecMajGrpId,
h.SecMajIssueGrpName,
h.SecMinGrpId,
h.SecMinIssueGrpName,
null,
s.IssuerName,
null,
null,
s.PrimarySecId,
s.Ticker,
SUM(c.Cash + c.CashEquivalent),
1,
1,
SUM(c.Cash + c.CashEquivalent)
from vew_Value_MgrGrp c
left join vew_Portfolio a on c.PortAccID = a.PortAccID
left join vew_Security s on @_CashSecId= s.PrimarySecId
and c.AsOfDate = s.AsOfDate
left join vew_Hierarcy_SecIssue h on 30 = h.SecIssueTypeId
where c.AsofDate = @_AsOfDate
and c.PortAccID = isnull(@_PortAccID, c.PortAccID)
and Cash + c.CashEquivalent > 0
GROUP BY c.AsOfDate,
c.PortAccID,
a.PortCode,
a.PortName,
--c.MgrGrpCode,
h.SecMajGrpId,
h.SecMajIssueGrpName,
h.SecMinGrpId,
h.SecMinIssueGrpName,
s.IssuerName,
s.PrimarySecId,
s.Ticker
GRANT EXECUTE ON rpt_APL_Audit_monthenddate TO PUBLIC
END
January 7, 2009 at 11:58 am
i need change as of date to default prior month end date
January 7, 2009 at 12:26 pm
i need to write function
first check asofdate is current month or prior month
If as of date is prior month and timclose status = c then month end date
else
if current month and timclose status = null then asof date
January 7, 2009 at 1:42 pm
I am assuming you
want this:
set @_AsOfDate = @pAsOfDate
set @_AsOfDate = dbo.svf_GetPriorMonthEndDate_asofdate()
to be this :
set @_AsOfDate = dbo.svf_GetPriorMonthEndDate_asofdate(@pAsOfDate)
* Noel
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply