Subquery returned more than 1 value...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Subquery returned more than 1 value...

Author

Topic

sherrireid
Yak Posting Veteran

58 Posts

Posted - 2013-09-18 : 18:57:11

I get this error message:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression

When I run the following query:

create table #Data1 (
BATCH_NO int,
JNL_ACCT varchar(60),
JNL_DESC char(60),
JNL_AMT money,
JNL_CODE char(8),
JNL_LNG_DESC varchar(200))

insert into #Data1 (
BATCH_NO, JNL_ACCT, JNL_DESC, JNL_AMT, JNL_CODE, JNL_LNG_DESC )

select
JHDT.BATCH_NO,
JHDT.JNL_ACCT,
JHDT.JNL_DET_DESC,
JHDT.JNL_AMT,
JHHD.JNL_CODE,
JHHD.JNL_LNG_DESC

from JHDT
join JHHD on JHDT.BATCH_NO = JHHD.BATCH_NO

where JHHD.FLX_UPDATE_DATE >= '08/01/2013'
and JHHD.FLX_UPDATE_DATE >= '08/31/2013'
and JHHD.JNL_CODE in ('ACCR','INVOICE','HPCASH') and
JNL_ACCT like '10140014%'

create table #Data2 (
BATCH_NO int,
JNL_ACCT varchar(60),
JNL_DESC char(60),
JNL_AMT money,
JNL_CODE char(8),
JNL_LNG_DESC varchar(200))

insert into #Data2 (
BATCH_NO, JNL_ACCT, JNL_DESC, JNL_AMT, JNL_CODE, JNL_LNG_DESC )

select
JHDT.BATCH_NO,
JHDT.JNL_ACCT,
JHDT.JNL_DET_DESC,
JHDT.JNL_AMT,
JHHD.JNL_CODE,
JHHD.JNL_LNG_DESC

from JHDT
join JHHD on JHDT.BATCH_NO = JHHD.BATCH_NO

where JHHD.FLX_UPDATE_DATE >= '08/01/2013'
and JHHD.FLX_UPDATE_DATE >= '08/31/2013'
and JHHD.JNL_CODE in ('AFAS','MGEN')
and JNL_ACCT = '102030249100'

select
#Data1.BATCH_NO,
#Data1.JNL_ACCT,
#Data1.JNL_AMT,
#Data1.JNL_CODE,
#Data1.JNL_DESC,
#Data1.JNL_LNG_DESC

from #Data1
where abs(#Data1.JNL_AMT) not in (select abs(JNL_AMT) from #Data2)
and CAST(#Data1.BATCH_NO as CHAR(20)) = (select SUBSTRING(#Data2.JNL_DESC,13,7) from #Data2)

drop table #Data1
drop table #Data2

I know it is yelling about the line with CAST in it above because when I change the = to an 'in' the query runs.

Unfortunately I don't get any data with 'in'. (I know that there should be a match because I can look at the results in #Data1 and #Data2 and manually match the records)

My Data looks like the following:

#Data1

Batch_NO JNL_ACCT JNL_DESC JNL_AMT
1218491 101400140209 DUE FM RST HMHS RECYCLING PROG 64.00
1218491 101400140209 DUE FM RST HMHS RECYCLING PROG 21.92
1219281 101400140003 DUE FM RST MISSION OF CARING 1000.00

#Data2

Batch_NO JNL_ACCT JNL_DESC JNL_AMT
1223522 102030249100 8018 HPCASH 1218491 DUE FM RST RECYCLING PROG 64.00
1223522 102030249200 8018 HPCASH 1219281 DUE FM RST MISSION OF CARING 1000.00

Basically I am trying to see if the data from Data1 also exists in Data2 -- if it DOESN'T exist, then I want to see it.

Does anyone have any suggestions?

Thanks
Sherri Reid

SLReid
Forum Newbie
Renton, WA USA

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-09-19 : 03:27:11

--try this WHERE condition
where abs(#Data1.JNL_AMT) not in (select abs(JNL_AMT) from #Data2 where JNL_AMT IS NOT NULL)
and #Data1.BATCH_NO IN (select SUBSTRING(#Data2.JNL_DESC,13,7) from #Data2)

--
Chandu

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-09-19 : 04:34:17

[code]
SELECT t1.BATCH_NO,
t1.JNL_ACCT,
t1.JNL_AMT,
t1.JNL_CODE,
t1.JNL_DESC,
t1.JNL_LNG_DESC
FROM #Data1 AS t1
WHERE EXISTS(SELECT * FROM #Data2 AS x WHERE SUBSTRING(x.JNL_DESC, 13, 7) = CAST(t1.BATCH_NO as CHAR(20)))
AND NOT EXISTS(SELECT * FROM #Data2 AS y WHERE ABS(y.JNL_AMT) = ABS(t1.JNL_AMT));[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources