Sub query throwing an 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)

Sub query throwing an error

Author

Topic

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2009-05-19 : 12:06:58

Greetings experts:

This is a butt kicker.

I was just trying to determine the current the fname based on the most current date and the query I am using is throwing the following error:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC SQL Server Driver][SQL Server]Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

I believe I am getting this error because there are duplicate phase numbers in the tblMeterReaders table.

Can you please tell me the best way to use the query?

Code is below and many thanks in advance.

Select fName from tblMeters WHERE id =
(Select PhaseNum from tblMeteReaders WHERE StartDate =
(Select Max(StartDate) from tblMeteReaders WHERE meterid=17 AND ReaderId=3))

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-05-19 : 12:12:31

Try this:


Select distinct m.fName from tblMeters m 
join (Select PhaseNum, StartDate from tblMeteReaders) mr1
	on mr1.PhaseNum = m.id
join (Select Max(StartDate) StartDate from tblMeteReaders WHERE meterid=17 AND ReaderId=3) mr2
	on mr2.StartDate = m.StartDate

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-05-19 : 12:56:30

Some other options:

Select fName from tblMeters WHERE id = 
(Select TOP 1 PhaseNum from tblMeteReaders WHERE StartDate = 
(Select Max(StartDate) from tblMeteReaders WHERE meterid=17 AND ReaderId=3)) 

Select fName from tblMeters WHERE id = 
(Select MIN(PhaseNum) from tblMeteReaders WHERE StartDate = 
(Select Max(StartDate) from tblMeteReaders WHERE meterid=17 AND ReaderId=3)) 

Select fName from tblMeters WHERE id = 
(Select MAX(PhaseNum) from tblMeteReaders WHERE StartDate = 
(Select Max(StartDate) from tblMeteReaders WHERE meterid=17 AND ReaderId=3)) 

Select fName from tblMeters WHERE id = 
(Select DISTINCT PhaseNum from tblMeteReaders WHERE StartDate = 
(Select Max(StartDate) from tblMeteReaders WHERE meterid=17 AND ReaderId=3)) 

SELECT 
	m.fName 
FROM 
	tblMeters m 
INNER JOIN 
	(
		SELECT TOP 1 PhaseNum
		FROM tblMeteReaders 
		WHERE meterid=17 AND ReaderId=3
		GROUP BY PhaseNum
		ORDER BY MAX(StartDate) DESC
	) mr
	ON mr.PhaseNum = m.id

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-19 : 13:10:45

quote:

Originally posted by RickD

Try this:


Select distinct m.fName from tblMeters m 
join (Select PhaseNum, StartDate from tblMeteReaders) mr1
	on mr1.PhaseNum = m.id
join (Select Max(StartDate) StartDate from tblMeteReaders WHERE meterid=17 AND ReaderId=3) mr2
	on mr2.StartDate = mr1.StartDate

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-05-20 : 04:02:37

quote:

Originally posted by visakh16

quote:

Originally posted by RickD

Try this:


Select distinct m.fName from tblMeters m 
join (Select PhaseNum, StartDate from tblMeteReaders) mr1
	on mr1.PhaseNum = m.id
join (Select Max(StartDate) StartDate from tblMeteReaders WHERE meterid=17 AND ReaderId=3) mr2
	on mr2.StartDate = mr1.StartDate

Yeah, thanks..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources