473,320 Members | 1,991 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

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
0 2025

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

Similar topics

0
by: leegold2 | last post by:
I tried what's below, seemed OK, so I replaced an "IN" for the "=" in the subquery below because of the subquery's error message. I thought w/"IN" I'd get three (3) records returned as expected....
2
by: Nachi | last post by:
Hi, Urgent Help appreciated.... I am getting resultset with first condition and when try to get the resutlset from second condition i am getting the above error in SQL200. I know that i am...
2
by: Zri Man | last post by:
I have a situation that is as follows: First an Index of what is discussed below: FED_A ->> Federated Objected/Table that has lots of rows LOC_B ->> Local Table which is selected and...
5
by: Rod | last post by:
I have a client site where the code below has been working happily for at least four months. The site is using SQL Server 7. The code is ASP.NET Last week an error appeared related to the...
2
by: reap76 | last post by:
I am running the following query: if (select IntExternalAccountID from ExternalAccount) = (select IntExternalAccountID from InternalAccount) select * from InternalAccount where AccountPurpose=2 ...
3
by: Shals | last post by:
Hi, I'm using Form and a subform control within that form. Main form is getting data from one table(Buildings) and subform is getting data from another table(BldgDataByFloor). but both the form...
0
by: fubaba | last post by:
hi, i execute a store procedure got Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.'...
2
by: neilgilbert1234 | last post by:
Hi Everyone, Im sure this is an easyone, im a newbie to anything half complex in sql so any help would be greatly appreaciated. I have one table that has two values, i need to multiply those values...
3
by: jideesh | last post by:
---------------trigger code create trigger DeletepurchaseItems on table_purchaseitems for delete,update as begin select * from deleted update table_STOCK set ostock=(ostock-(select sqty from...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.