Select subQuery error - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Select subQuery error

Author

Topic

kdeutsch
Yak Posting Veteran

83 Posts

Posted - 2009-05-19 : 15:41:38

I get the follwoing error whn I try doing a subSlect in either my select statement and in my where clause.

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

What do i need to do to get rid of this.

SELECT at.UPC as UIC,
ut.Addr_City as City,
at.Auth_Para_Dsg as PARA,
at.Auth_Line_Dsg as LINE,
at.GRADE,
substring(at.POSC, 1,4) as DMOS,
case at.AUTH_PERS_IDENT when 'E' then 'M' when 'W' then 'M' when 'O' then 'M' else 'I' end GENDER,
at.AUTH_STR,
CASE WHEN ISNUMERIC( case when at.AUTH_DOC_POSN_TITLE like '%(%)%' then substring(at.AUTH_DOC_POSN_TITLE, 1 + charindex('(', at.AUTH_DOC_POSN_TITLE), charindex(')', at.AUTH_DOC_POSN_TITLE) - (charindex('(', at.AUTH_DOC_POSN_TITLE) + 1)) else '0' end ) = 0 THEN 0
ELSE
case when at.AUTH_DOC_POSN_TITLE like '%(%)%' then substring(at.AUTH_DOC_POSN_TITLE, 1 + charindex('(', at.AUTH_DOC_POSN_TITLE), charindex(')', at.AUTH_DOC_POSN_TITLE) - (charindex('(', at.AUTH_DOC_POSN_TITLE) + 1)) else '0' end END AS OS,
at.ASGN_STR,
at.AUTH_STR + CASE WHEN ISNUMERIC( case when at.AUTH_DOC_POSN_TITLE like '%(%)%' then substring(at.AUTH_DOC_POSN_TITLE, 1 + charindex('(', at.AUTH_DOC_POSN_TITLE), charindex(')', at.AUTH_DOC_POSN_TITLE) - (charindex('(', at.AUTH_DOC_POSN_TITLE) + 1)) else '0' end ) = 0 THEN 0
ELSE
case when at.AUTH_DOC_POSN_TITLE like '%(%)%' then substring(at.AUTH_DOC_POSN_TITLE, 1 + charindex('(', at.AUTH_DOC_POSN_TITLE), charindex(')', at.AUTH_DOC_POSN_TITLE) - (charindex('(', at.AUTH_DOC_POSN_TITLE) + 1)) else '0' end - at.ASGN_STR END AS VAC,
case substring(at.DOC_NBR, 1, 2) when 'NG' then 'TDA' else 'MTOE' End as Unit_Type,
case st.strStat when 'L' then 'Locked' when 'C' then 'Critical' Else '' end as UNIT_Stat
FROM SIDPERS..SIDPERS.PERS_UNIT_TBL as ut Inner join
SIDPERS..SIDPERS.PERS_AUTH_STR_TBL as at on at.UPC = ut.UPC Left Outer Join
RecruitDotNet.dbo.tblStatUic as st on st.strUIc = at.UPC
where Substring(at.grade, 1,1) = 'E' And ut.OESTS = 'N' or st.strStat = (Select st.strStat from tblStatUic where dtExpire > getdate())
Order by at.UPC, at.Auth_Para_Dsg, at.Auth_Line_Dsg

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-05-19 : 16:29:58

quote:
Originally posted by kdeutsch

What do i need to do to get rid of this.

The only sub query is returning more than one row so you either need to make it return one row or change how you are using the sub query. but, getting rid of the error may not get you the correct data.

Option 1:
Limit to one result with a TOP or aggregate function (MIN, MAX, etc) for example:

st.strStat = (Select MAX(st.strStat) from tblStatUic where dtExpire > getdate())

Option 2:
Change the equality check to an IN statement:

st.strStat IN (Select st.strStat from tblStatUic where dtExpire > getdate())

option 3:
Join to the table:

INNER JOIN
	tblStatUic 
	ON st.strStat = tblStatUic.strStat
	AND tblStatUic.dtExpire > getdate()

My Assumption is that you want Option 2 or 3.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources