locked
SQL server 2016, subquery returned more than one value. RRS feed

  • Question

  • I run the following query:

    select word,nowdatetime,MAX_COUNT_ID,MIN_COUNT_ID,[border(%)],id
    from(
    select
    word, nowdatetime, id,  CAST(MAX_COUNT_ID AS FLOAT) as MAX_COUNT_ID, CAST(MIN_COUNT_ID AS FLOAT) as MIN_COUNT_ID
    ,((((CAST(MAX_COUNT_ID AS FLOAT))-(CAST(MIN_COUNT_ID AS FLOAT)))/(CAST(MIN_COUNT_ID AS FLOAT)))*100) as 'border(%)'
    from(
    select distinct word
    ,(select MAX(nowdatetime) from Presign_Problem where nowdatetime >= DATEADD(HOUR, -4, GETDATE())) as nowdatetime
    ,(select MIN(count_id) from Presign_Problem pp2 where pp1.word=pp2.word and nowdatetime >= DATEADD(HOUR, -4, GETDATE()) group by word) as MIN_COUNT_ID
    ,(select MAX(count_id) from Presign_Problem pp2 where pp1.word=pp2.word and nowdatetime >= DATEADD(HOUR, -4, GETDATE()) group by word) as MAX_COUNT_ID
    ,(select id from Presign_Problem pp2 where pp1.word=pp2.word and nowdatetime >= DATEADD(HOUR, -4, GETDATE()) and count_id = (SELECT MAX(count_id) FROM Presign_Problem pp3 where pp1.word=pp3.word and nowdatetime >= DATEADD(HOUR, -4, GETDATE()))  ) as id
    from Presign_Problem pp1 
    where nowdatetime >= DATEADD(HOUR, -4, GETDATE())
    )tabl
    where id is not null
    )tabl2

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


    Error in line below:
    ,(select id from Presign_Problem pp2 where pp1.word=pp2.word and nowdatetime >= DATEADD(HOUR, -4, GETDATE()) and count_id = (SELECT MAX(count_id) FROM Presign_Problem pp3 where pp1.word=pp3.word and nowdatetime >= DATEADD(HOUR, -4, GETDATE()))  ) as id

    Previously, the query did not display an error.


    Below is an example of the data in the table:
    word count_word count_id nowdatetime id 
    computer 73 61 22.08.2017 15:24 ID23679381,ID48525000,ID23597299
    computer 74 62 22.08.2017 14:24 ID23682019,ID23499955,ID23534359
    computer 80 70 22.08.2017 12:24 ID23629307,ID48529530,ID23612836

    Any ideas? How can i fix it?
    Tuesday, August 22, 2017 12:47 PM

Answers

  • Check the subquery in bold which seems to be the culprit:

    select word,nowdatetime,MAX_COUNT_ID,MIN_COUNT_ID,[border(%)],id
    from(
    select
    word, nowdatetime, id,  CAST(MAX_COUNT_ID AS FLOAT) as MAX_COUNT_ID, CAST(MIN_COUNT_ID AS FLOAT) as MIN_COUNT_ID
    ,((((CAST(MAX_COUNT_ID AS FLOAT))-(CAST(MIN_COUNT_ID AS FLOAT)))/(CAST(MIN_COUNT_ID AS FLOAT)))*100) as 'border(%)'
    from(
    select distinct word
    ,(select MAX(nowdatetime) from Presign_Problem where nowdatetime >= DATEADD(HOUR, -4, GETDATE())) as nowdatetime
    ,(select MIN(count_id) from Presign_Problem pp2 where pp1.word=pp2.word and nowdatetime >= DATEADD(HOUR, -4, GETDATE()) group by word) as MIN_COUNT_ID
    ,(select MAX(count_id) from Presign_Problem pp2 where pp1.word=pp2.word and nowdatetime >= DATEADD(HOUR, -4, GETDATE()) group by word) as MAX_COUNT_ID
    ,(select id from Presign_Problem pp2 where pp1.word=pp2.word and nowdatetime >= DATEADD(HOUR, -4, GETDATE()) and count_id = (SELECT MAX(count_id) FROM Presign_Problem pp3 where pp1.word=pp3.word and nowdatetime >= DATEADD(HOUR, -4, GETDATE()))  ) as id
    from Presign_Problem pp1 
    where nowdatetime >= DATEADD(HOUR, -4, GETDATE())
    )tabl
    where id is not null
    )tabl2


    AMB

    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas


    • Edited by HunchbackMVP Tuesday, August 22, 2017 1:00 PM
    • Marked as answer by Aleks Roth Wednesday, August 23, 2017 1:14 PM
    Tuesday, August 22, 2017 12:58 PM
  • Hello Aleks,

    Try to go step by step to see the right select that was generating this problem... Like this

    ,(select id from Presign_Problem pp2 where pp1.word=pp2.word and nowdatetime >= DATEADD(HOUR, -4, GETDATE()) and count_id = (SELECT MAX(count_id) FROM Presign_Problem pp3 where pp1.word=pp3.word and nowdatetime >= DATEADD(HOUR, -4, GETDATE()))  ) as id

    and then you look for this:

    (select id from Presign_Problem pp2 where pp1.word=pp2.word and nowdatetime >= DATEADD(HOUR, -4, GETDATE()) and count_id = (SELECT MAX(count_id) FROM Presign_Problem pp3 where pp1.word=pp3.word and nowdatetime >= DATEADD(HOUR, -4, GETDATE()))  )

    Because you have a lot of subquerrys and when you use some not defined information like MAX.. it can generate more results than you expect.

    • Marked as answer by Aleks Roth Wednesday, August 23, 2017 1:14 PM
    Tuesday, August 22, 2017 1:46 PM

All replies

  • Check the subquery in bold which seems to be the culprit:

    select word,nowdatetime,MAX_COUNT_ID,MIN_COUNT_ID,[border(%)],id
    from(
    select
    word, nowdatetime, id,  CAST(MAX_COUNT_ID AS FLOAT) as MAX_COUNT_ID, CAST(MIN_COUNT_ID AS FLOAT) as MIN_COUNT_ID
    ,((((CAST(MAX_COUNT_ID AS FLOAT))-(CAST(MIN_COUNT_ID AS FLOAT)))/(CAST(MIN_COUNT_ID AS FLOAT)))*100) as 'border(%)'
    from(
    select distinct word
    ,(select MAX(nowdatetime) from Presign_Problem where nowdatetime >= DATEADD(HOUR, -4, GETDATE())) as nowdatetime
    ,(select MIN(count_id) from Presign_Problem pp2 where pp1.word=pp2.word and nowdatetime >= DATEADD(HOUR, -4, GETDATE()) group by word) as MIN_COUNT_ID
    ,(select MAX(count_id) from Presign_Problem pp2 where pp1.word=pp2.word and nowdatetime >= DATEADD(HOUR, -4, GETDATE()) group by word) as MAX_COUNT_ID
    ,(select id from Presign_Problem pp2 where pp1.word=pp2.word and nowdatetime >= DATEADD(HOUR, -4, GETDATE()) and count_id = (SELECT MAX(count_id) FROM Presign_Problem pp3 where pp1.word=pp3.word and nowdatetime >= DATEADD(HOUR, -4, GETDATE()))  ) as id
    from Presign_Problem pp1 
    where nowdatetime >= DATEADD(HOUR, -4, GETDATE())
    )tabl
    where id is not null
    )tabl2


    AMB

    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas


    • Edited by HunchbackMVP Tuesday, August 22, 2017 1:00 PM
    • Marked as answer by Aleks Roth Wednesday, August 23, 2017 1:14 PM
    Tuesday, August 22, 2017 12:58 PM
  • Hello Aleks,

    Try to go step by step to see the right select that was generating this problem... Like this

    ,(select id from Presign_Problem pp2 where pp1.word=pp2.word and nowdatetime >= DATEADD(HOUR, -4, GETDATE()) and count_id = (SELECT MAX(count_id) FROM Presign_Problem pp3 where pp1.word=pp3.word and nowdatetime >= DATEADD(HOUR, -4, GETDATE()))  ) as id

    and then you look for this:

    (select id from Presign_Problem pp2 where pp1.word=pp2.word and nowdatetime >= DATEADD(HOUR, -4, GETDATE()) and count_id = (SELECT MAX(count_id) FROM Presign_Problem pp3 where pp1.word=pp3.word and nowdatetime >= DATEADD(HOUR, -4, GETDATE()))  )

    Because you have a lot of subquerrys and when you use some not defined information like MAX.. it can generate more results than you expect.

    • Marked as answer by Aleks Roth Wednesday, August 23, 2017 1:14 PM
    Tuesday, August 22, 2017 1:46 PM
  • Hi Aleks,

    According to the script of the SQL, I find that there are too many subqueries in the statement. Thus, it would be easy to get error message. To avoid getting errors, you'd better use table joins rather than subqueries. you could try to solve the issue by referring to code below:

    create table Presign_Problem
    (
     word varchar(20),
     count_word int,
     count_id int,
     nowdatetime datetime,
     id varchar(256)
     )
    
     insert into Presign_Problem values
     ('computer',73,61,'2017-8-22 15:24:00','ID23679381,ID48525000,ID23597299'),
     ('computer',74,62,'2017-8-22 14:24:00','ID23682019,ID23499955,ID23534359'),
     ('computer',80,70,'2017-8-22 12:24:00','ID23629307,ID48529530,ID23612836')
     GO
    
    ;with Presign_Problem1 as
    (
    select
    word,
    count_word,
    count_id,
    nowdatetime,
    id
    from Presign_Problem
    where nowdatetime >= DATEADD(HOUR, -4, GETDATE())
    ),
    group_Presign as
    (
    select
    word,
    min(count_id) as min_count_id,
    max(count_id) as max_count_id
    from Presign_Problem1
    group by word
    )
    --select 
    --word,
    --nowdatetime,
    --MAX_COUNT_ID,
    --MIN_COUNT_ID,
    --[border(%)],
    --id
    --from
    --(
      select
          word, 
    	  nowdatetime, 
    	  id,  
    	  CAST(MAX_COUNT_ID AS FLOAT) as MAX_COUNT_ID, 
    	  CAST(MIN_COUNT_ID AS FLOAT) as MIN_COUNT_ID,
    	  ((((CAST(MAX_COUNT_ID AS FLOAT))-(CAST(MIN_COUNT_ID AS FLOAT)))/(CAST(MIN_COUNT_ID AS FLOAT)))*100) as [border(%)]
          from(
                select distinct 
    			       pp1.word,
    			       --word,
    				   (select MAX(nowdatetime) from Presign_Problem1) as nowdatetime,
    		          --(select MAX(nowdatetime) from Presign_Problem where nowdatetime >= DATEADD(HOUR, -4, GETDATE())) as nowdatetime,
    				  pp2.min_count_id,
    				  pp2.max_count_id,
    				  --(select MIN(count_id) from Presign_Problem pp2 where pp1.word=pp2.word and nowdatetime >= DATEADD(HOUR, -4, GETDATE()) group by word) as MIN_COUNT_ID,--?
    				  --(select MAX(count_id) from Presign_Problem pp2 where pp1.word=pp2.word and nowdatetime >= DATEADD(HOUR, -4, GETDATE()) group by word) as MAX_COUNT_ID,--?
    				  pp3.id
    				 -- (select id from Presign_Problem pp2 
    				 --   where pp1.word=pp2.word 
    					--and nowdatetime >= DATEADD(HOUR, -4, GETDATE()) 
    					--and count_id = (SELECT MAX(count_id) FROM Presign_Problem pp3 where pp1.word=pp3.word and nowdatetime >= DATEADD(HOUR, -4, GETDATE()))  
    					--) as id
    		 from Presign_Problem1 pp1 
    		 left join group_Presign pp2 on pp1.word=pp2.word
    		 left join Presign_Problem1 pp3 on pp1.word=pp3.word and pp2.max_count_id=pp3.count_id
             --from Presign_Problem pp1 
             --where nowdatetime >= DATEADD(HOUR, -4, GETDATE())
             )tabl
     where id is not null
    --)tabl2

    If I misunderstand the logic of your code, please kindly correct me.

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, August 23, 2017 6:14 AM