By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,835 Members | 1,909 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,835 IT Pros & Developers. It's quick & easy.

Subquery returned more than 1 value. This is not permitted when the subquery follows

P: 1
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
Aug 18 '07 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.