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

General SQL Server Forums

New to SQL Server Programming

Subquery Returned more than 1 value

Author

Topic

maqza
Starting Member

7 Posts

Posted - 2011-02-26 : 04:14:22

Hi!
Select Symhis.Mktprc, Symhis.PrcDte, Symhis,Symcde,
(select max(VlnAsshis.VlnDte) from VlnAssHis
where
VlnAssHis.Symcde=Symhis.Symcde and
VlnAssHis.VlnDte<Symhis.PrcDte) VlnDte,
(Select Ownbal from VlnAssHis
where
VlnAssHis.Symcde=Symhis.Symcde
And
VlnDte=
(select max(VlnAsshis.VlnDte) from VlnAssHis
where
VlnAssHis.Symcde=Symhis.Symcde and
VlnAssHis.VlnDte<Symhis.PrcDte) ) ownbal from Symhis

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-02-26 : 04:19:07

one of your sub query is returning more than 1 row. Looks like it is coming from the 2nd sub-query ownbal

KH
[spoiler]Time is always against us[/spoiler]

maqza
Starting Member

7 Posts

Posted - 2011-02-26 : 04:50:05

Hi!
I am sorry to disturb u, it is a bit harder plz fix

Thanks in advance

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-02-26 : 04:56:32

i can't really fix. I don't know what do you want, i don't know how is the data looks like.

You can get rid of that error just by applying an aggregate like min() or max() on the 2nd sub-query. You have to see if this is what you really want.

KH
[spoiler]Time is always against us[/spoiler]

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-02-26 : 18:42:11

"A problem well stated is a problem half solved." -- Charles F. Kettering

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html

SELECTs inside a SELECT list is very slow.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

chris_n_osborne
Starting Member

34 Posts

Posted - 2011-02-27 : 11:07:42

quote:
Originally posted by maqza

Hi!
Select Symhis.Mktprc, Symhis.PrcDte, Symhis,Symcde,
(select max(VlnAsshis.VlnDte) from VlnAssHis
where
VlnAssHis.Symcde=Symhis.Symcde and
VlnAssHis.VlnDte<Symhis.PrcDte) VlnDte,
(Select Ownbal from VlnAssHis
where
VlnAssHis.Symcde=Symhis.Symcde
And
VlnDte=
(select max(VlnAsshis.VlnDte) from VlnAssHis
where
VlnAssHis.Symcde=Symhis.Symcde and
VlnAssHis.VlnDte<Symhis.PrcDte) ) ownbal from Symhis

I apologize to say, but the above code is unreadable.

Here it is, reformatted for readability:


  Select Symhis.Mktprc
        ,Symhis.PrcDte
        ,Symhis,Symcde
        ,(select max(VlnAsshis.VlnDte)
            from VlnAssHis
           where VlnAssHis.Symcde = Symhis.Symcde
             and VlnAssHis.VlnDte < Symhis.PrcDte
         ) VlnDte
        ,(Select Ownbal
            from VlnAssHis
           where VlnAssHis.Symcde = Symhis.Symcde
             And VlnDte =
                (select max(VlnAsshis.VlnDte)
                   from VlnAssHis
                  where VlnAssHis.Symcde = Symhis.Symcde
                    and VlnAssHis.VlnDte < Symhis.PrcDte
                 )
         ) ownbal
    from Symhis

From this we easily see the following line:


        ,Symhis,Symcde

The comma stands out in my format because you can see the punctuation in a column: period, period, comma; and the comma does not belong there.

In your original format, the comma's presence is not immediately obvious.

Try replacing the comma in between Symhis and Symcde with a period:


        ,Symhis.Symcde

As for returning more than one value, I see:


        ,(Select Ownbal

That could easily return more than one value.

maqza
Starting Member

7 Posts

Posted - 2011-02-28 : 01:49:49

HI!
Thanks for your help, actually table like this..
vlnasshis

vlndte
ownbal
mktcde
symcde
fndcde

symhis

prcdte
mktprc
symcde
mktcde

vlnasshis (master)
vlndte ownbal symcde mktcde fndcde
02-Aug-10 118976 2010 sa 200
15-Jun-10 129346 2010 sa 200
symhis (Details)
prcdte mktprc symcde mktcde
04-Aug-10 88.25 2010 sa
03-Aug-10 87.75 2010 sa
03-Jul-10 86 2010 sa
06-Jun-10 85 2010 sa

out put should be like this
as of 04-Aug-10
prcdte mktprc symcde mktcde vlndte ownbal
04-Aug-10 88.25 2010 sa 02-Aug-10 118976
as of 03-Jul-10

prcdte mktprc symcde mktcde vlndte ownbal
03-Jul-10 86 2010 sa 15-Jun-10 129346
Thanks in advance

kazi
Starting Member

8 Posts

Posted - 2011-02-28 : 02:36:12

You should have to use alias like Symhis.Symcde instead Symcde
and please describe the primery key of "vlnasshis" and relation with master and detail table

Kazi

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources