hi please help in solving this
i am recieving this error"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."
for the code
CREATE procedure [dbo].[ADI_spfrmGetLogEntries]
(@frmMasterID int,
@startDate datetime,
@endDate datetime,
@startPos int,
@rowCnt int,
@snapShotDate datetime,
@pagingByDate bit=0,
@ttlCnt int=0 OUTPUT
)
as
begin
SET NOCOUNT ON
--THIS IS USED FOR RESTRICTING NEW RECORDS FROM BEING INCLUDED
--DURING PAGING
if @snapShotDate is null set @snapShotDate=getdate()
--TABLE TO STORE LOG ENTRIES
CREATE TABLE #TAB
(logRank int identity(1,1),
frmLogEntryID int,
frmRevID int,
dateCreated datetime)
INSERT INTO #TAB
SELECT
l.frmLogEntryID,
l.frmRevID,
l.dateCreated
FROM
frmRevision r
inner JOIN frmLogEntry l
on r.frmRevID = l.frmRevID
and
(
(@startDate is not null and convert(varchar,l.dateCreated,101) >=@startDate)
or
(@startDate is null)
)
and
(
(@endDate is not null and convert(varchar,l.dateCreated,101) <= @endDate)
or
(@endDate is null)
)
and convert(varchar,l.dateCreated,101) <= @snapShotDate
WHERE r.frmMasterID = @frmMasterID
ORDER BY l.frmlogEntryID desc
--IF PAGING BY EACH DAY, NEED TO GET A LIST OF DISTINCT DATES.
if @pagingByDate =1
BEGIN
DECLARE @dateTab TABLE
(logRank int identity(1,1),
dateCreated datetime)
INSERT INTO @dateTab
SELECT
distinct convert(varchar,dateCreated,101)
FROM #TAB
ORDER BY convert(varchar,dateCreated,101) desc
SELECT
a.frmAttrDisplayName,
a.frmRevID,
l.frmLogEntryID,
l.dateCreated,
frmAttrValue = CASE
WHEN a.frmAttrName like '%country%'
THEN
ISNULL((select country
from dbo.country
where abbreviation = ld.frmAttrValue),ld.frmAttrValue)
WHEN a.frmAttrName like '%state%'
THEN
ISNULL((select full_name
from dbo.state
where state = ld.frmAttrValue and (StateCountry is NULL or StateCountry <> 'MX')), ld.frmAttrValue)
ELSE ld.frmAttrValue
END,
a.frmAttrID
FROM
#TAB as l
inner join @dateTab as dt
on l.dateCreated >=dt.dateCreated
and l.dateCreated < dateadd(d,1,dt.dateCreated)
inner join frmAttribute a
on l.frmRevID = a.frmRevID
inner join frmLogEntryDetail ld
on l.frmLogEntryID=ld.frmLogEntryID
and a.frmAttrID = ld.frmAttrID
WHERE
dt.logRank = @startPos
Order by a.frmAttrID
if @startPos=1
BEGIN
/*SELECT @ttlCnt = MAX(logRank) from @dateTab*/
SELECT @ttlCnt = MAX(logRank) from #Tab
END
END
ELSE IF @rowCnt=0 and @pagingByDate = 0
BEGIN
SELECT
a.frmAttrDisplayName,
a.frmRevID,
l.frmLogEntryID,
l.dateCreated,
frmAttrValue = CASE
WHEN a.frmAttrName like '%country%'
THEN
ISNULL((select country
from dbo.country
where abbreviation = ld.frmAttrValue),ld.frmAttrValue)
WHEN a.frmAttrName like '%state%'
THEN
ISNULL((select full_name
from dbo.state
where state = ld.frmAttrValue and (StateCountry is NULL or StateCountry <> 'MX')), ld.frmAttrValue)
ELSE ld.frmAttrValue
END,
a.frmAttrID
FROM
#TAB as l
inner join frmAttribute a
on l.frmRevID = a.frmRevID
inner join frmLogEntryDetail ld
on l.frmLogEntryID=ld.frmLogEntryID
and a.frmAttrID = ld.frmAttrID
Order by a.frmAttrID
END
ELSE
BEGIN
DECLARE @endPos INT
SET @endPos = @startPos + @rowCnt
SELECT
a.frmAttrDisplayName,
a.frmRevID,
l.frmLogEntryID,
l.dateCreated,
frmAttrValue = CASE
WHEN a.frmAttrName like '%country%'
THEN
ISNULL((select country
from dbo.country
where abbreviation = ld.frmAttrValue),ld.frmAttrValue)
WHEN a.frmAttrName like '%state%'
THEN
ISNULL((select full_name
from dbo.state
where state = ld.frmAttrValue and (StateCountry is NULL or StateCountry <> 'MX')), ld.frmAttrValue)
ELSE ld.frmAttrValue
END,
a.frmAttrID
FROM
#TAB as l
inner join frmAttribute a
on l.frmRevID = a.frmRevID
inner join frmLogEntryDetail ld
on l.frmLogEntryID=ld.frmLogEntryID
and a.frmAttrID = ld.frmAttrID
WHERE
logRank >= @startPos
and logRank <@endPos
Order by a.frmAttrID
if @startPos=1
BEGIN
SELECT @ttlCnt = MAX(logRank) from #TAB
END
END
DROP TABLE #TAB
SET NOCOUNT OFF
end
GO