Internal SQL Server error for nested query

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)

Internal SQL Server error for nested query

Author

Topic

katarina07
Starting Member

31 Posts

Posted - 2008-05-29 : 12:15:28

Hi,

I have a following query:


select cu.Currencies_ShortName

from dbo.Currencies cu 
		inner join
				(
select p1.Pairs_Shortname as f1, right(p1.Pairs_Shortname,3) as Currency1,

/*v1*/ -- max(SpotBid) as SpotBid, max(SpotAsk) as SpotAsk

/*v2*/ SpotBid, SpotAsk 

from dbo.Pairs p1 inner join dbo.PairsQuotes pq1 

on p1.Pairs_Id=pq1.Pairs_Id 
and pq1.PriceDate=(select max(PriceDate) from dbo.PairsQuotes)
								
where p1.Pairs_Shortname like 'EUR%' 

/*v1*/ -- group by p1.Pairs_Shortname, right(p1.Pairs_Shortname,3)

)  as sr 
		on cu.Currencies_ShortName=sr.Currency1

which would work like /*v2*/, but not like /*v1*/ - there would be a message:
Server: Msg 8624, Level 16, State 3, Line 1
Internal SQL Server error.

Is Group By not manageable in a nested query, or is it some other problem?

thx
Katarina

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-29 : 12:25:52

If you are using GROUP BY to group your data on one or more fields you need to apply aggregate functions on all other columns used in select list other than the ones on which you group. Thats the reason its complaining when you dont give max.

katarina07
Starting Member

31 Posts

Posted - 2008-05-29 : 12:51:00

Hi,

you misunderstood me.

One alternative is this query, which works:


select cu.Currencies_ShortName

from dbo.Currencies cu 
		inner join
				(
select p1.Pairs_Shortname as f1, right(p1.Pairs_Shortname,3) as Currency1,

/*v2*/ SpotBid, SpotAsk 

from dbo.Pairs p1 inner join dbo.PairsQuotes pq1 

on p1.Pairs_Id=pq1.Pairs_Id 
and pq1.PriceDate=(select max(PriceDate) from dbo.PairsQuotes)
								
where p1.Pairs_Shortname like 'EUR%' 

)  as sr 
		on cu.Currencies_ShortName=sr.Currency1

Second alternative is this query, which doesnt work:


select cu.Currencies_ShortName

from dbo.Currencies cu 
		inner join
				(
select p1.Pairs_Shortname as f1, right(p1.Pairs_Shortname,3) as Currency1,

/*v1*/ max(SpotBid) as SpotBid, max(SpotAsk) as SpotAsk

from dbo.Pairs p1 inner join dbo.PairsQuotes pq1 

on p1.Pairs_Id=pq1.Pairs_Id 
and pq1.PriceDate=(select max(PriceDate) from dbo.PairsQuotes)
								
where p1.Pairs_Shortname like 'EUR%' 

/*v1*/ group by p1.Pairs_Shortname, right(p1.Pairs_Shortname,3)

)  as sr 
		on cu.Currencies_ShortName=sr.Currency1