3

I've tried modifying a SQL query from an application but cannot get it working

the error:

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

the original query:

SELECT fycode, fyname, class3, '', '', '', defje, zjm, '', ''
FROM zy_fy

UNION ALL
SELECT Ypcode, Ypname,
    (SELECT a.lbname FROM YK_yplb a where a.lbid = b.yplb)
, gg, sldw, '', '', '', '', ''
FROM YK_ypzd b

UNION ALL
SELECT FYID, NAME, DYKS+'-'+CLASS2, '', '次', '', FYMONEY, ZJM, ZJM1, '' 
FROM mz_fy

the modified query: 

SELECT fycode, fyname, class3, '', '', '', defje, zjm, '', ''
FROM zy_fy

UNION ALL
SELECT Ypcode, Ypname, 
(select a.lbname from YK_yplb a  where a.lbid = b.yplb)
,gg,sldw, '', 
(select c.dj from YK_kc c  where c.Ypcode = b.Ypcode)
 ,'','',''
FROM YK_ypzd b

UNION ALL
SELECT FYID, NAME, DYKS+'-'+CLASS2, '', '次', '', FYMONEY, ZJM, ZJM1, ''
FROM mz_fy

I've just inserted a subquery in the 7th column of the 3rd select statement

Thank you!

underscore_d
  • 5,331
  • 3
  • 29
  • 56
benson
  • 147
  • 1
  • 11
  • 4
    the error message is quite clear, the subquery is returning more than 1 value...you can see why that isn't allowed – Lamak Jan 29 '18 at 14:06
  • 3
    which dbms are you using – Bibin Mathew Jan 29 '18 at 14:08
  • All it takes is one record where the subquery returns multiple values to get this error. If you want multiple values, use a JOIN instead of a subquery. If the multiple values are trivial or you only want one, then use an aggregate function like MAX() to force the subquery to only return one value. – Greg Viers Jan 29 '18 at 14:09
  • notwithstanding the impenetrable formatting, this is useless without example input data – underscore_d Jan 29 '18 at 14:09
  • How did you check that the query only returns one record? `SELECT c.Ypcode FROM YK_ypzd b INNER JOIN YK_kc c ON b.Ypcode = c.Ypcode GROUP BY c.Ypcode HAVING COUNT(*) > 1` should give you the row you need to address. If not, try the first query - perhaps data was entered in `YK_yplb` while you weren't looking? – Forty3 Jan 29 '18 at 14:11
  • it's mssql 2000. – benson Jan 29 '18 at 14:11

3 Answers3

3
    select fycode,fyname,class3,'','','',defje,zjm,'',''  from zy_fy union all
select  Ypcode, Ypname,(select a.lbname from YK_yplb a  where a.lbid =
b.yplb limit 1),gg,sldw,'',(select c.dj from YK_kc c  where c.Ypcode =
b.Ypcode),'','',''  from YK_ypzd b  union all  select FYID,NAME,DYKS+'-
+CLASS2,'','次','',FYMONEY,ZJM,ZJM1,''  from mz_fy

Use limit

Sumesh TG
  • 2,367
  • 1
  • 12
  • 29
  • Sorry, but (select a.lbname from YK_yplb a where a.lbid = b.yplb) just work. It's (select c.dj from YK_kc c where c.Ypcode = b.Ypcode),which I modeled after the former. – benson Jan 29 '18 at 14:18
  • 2
    (A) `limit` is not MSSQL syntax, which the OP has (albeit unhelpfully in the comments) stated they are using. (B) Not explicitly ordering with `top` means you get an arbitrary row back, which is almost certainly not what the OP really wants. – underscore_d Jan 29 '18 at 14:23
  • There should be similar feature like LIMIT in mySql refer [https://stackoverflow.com/questions/603724/how-to-implement-limit-with-microsoft-sql-server](https://stackoverflow.com/questions/603724/how-to-implement-limit-with-microsoft-sql-server) – Sumesh TG Jan 29 '18 at 14:37
2

You just need to use TOP 1 to limit the subquery to return only one row

SELECT fycode, fyname, class3, '', '', '', defje, zjm, '', ''
FROM zy_fy

UNION ALL
SELECT Ypcode, Ypname, 
(select TOP 1 a.lbname from YK_yplb a  where a.lbid = b.yplb)
,gg,sldw, '', 
(select TOP 1 c.dj from YK_kc c  where c.Ypcode = b.Ypcode)
 ,'','',''
FROM YK_ypzd b

UNION ALL
SELECT FYID, NAME, DYKS+'-'+CLASS2, '', '次', '', FYMONEY, ZJM, ZJM1, ''
FROM mz_fy
Eduardo Molteni
  • 37,007
  • 23
  • 135
  • 201
-1

please use TOP 1 to limit the subquery to return only one row