select list with subquery error

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)

select list with subquery error

Author

Topic

infodemers
Posting Yak Master

183 Posts

Posted - 2010-02-10 : 14:31:53

Can anyone help me with the following query please?

Select NPANXXModifiedDate, NPA, NNX, BellSwitch,  ExchangeName, ExchangeLIR, NPANNXAssignmentStartDate,
	WSP, CLEC, SPC, NXXPOI, [Real POI], CLECWSPSwitch, PointCode, SwitchCompanyName, XNodeCLLI,
	TollTandem, TollCentre, BARC,  
	(Select top 1 CLLI, [Subject] from (Select NoteId, CLLI, [Subject],ModifiedDate From Note group by CLLI, Subject, NoteId,ModifiedDate ) as t
	where left(CLLI,3) =  NPA and right(CLLI,3) = NNX Order by NoteId desc),
	InsertedDate,ModifiedDate,ModifiedBy 
	from viewReportTranslationNPANXX
	where NPANXXModifiedDate between @FromDate and @EndDate and npa = @npa

I get this error :
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-10 : 14:37:04

whats the purpose of subquery? i cant see any relationship of that with main query

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

infodemers
Posting Yak Master

183 Posts

Posted - 2010-02-10 : 14:45:09

Hi visakh16,

Let's say I have the following table
NPA | NNX | Name | Start Date | End Date | Subject |
405 | 123 | TOPI | 2010-02-10 | 2012-02-02 | |
438 | 644 | MODE | 2005-08-01 | 2010-03-01 | |

I wish to get the latest subject from table Note for every NPA NNX from the above table. There might be a way to do it with a LEFT OUTER JOIN instead

quote:
Originally posted by visakh16

whats the purpose of subquery? i cant see any relationship of that with main query

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-10 : 14:50:32

do you mean subject that is effective as of date considered? (current date between Start Date & End Date)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

infodemers
Posting Yak Master

183 Posts

Posted - 2010-02-10 : 15:02:29

No,

Like if there is 5 Subjects for one of the NPA NNX, then I want the last created one.

Thanks!

quote:
Originally posted by visakh16

do you mean subject that is effective as of date considered? (current date between Start Date & End Date)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

infodemers
Posting Yak Master

183 Posts

Posted - 2010-02-10 : 15:33:20

Hi,
I got it to work like the following....Thanks for trying to help me!

Select * from (Select NPANXXModifiedDate, NPA, NNX, BellSwitch,  ExchangeName, ExchangeLIR, NPANNXAssignmentStartDate,
	WSP, CLEC, SPC, NXXPOI, [Real POI], CLECWSPSwitch, PointCode, SwitchCompanyName, XNodeCLLI,
	TollTandem, TollCentre, BARC,  
	(Select top 1 [Subject] from (Select NoteId, CLLI, [Subject],ModifiedDate From Note group by CLLI, Subject, NoteId,ModifiedDate ) as t
	where left(CLLI,3) =  NPA and right(CLLI,3) = NNX Order by NoteId desc) as [Remarks],
	InsertedDate,ModifiedDate,ModifiedBy 
	from viewReportTranslationNPANXX) as t
	where NPANXXModifiedDate between @FromDate and @EndDate and npa = @npa

quote:
Originally posted by infodemers

No,

Like if there is 5 Subjects for one of the NPA NNX, then I want the last created one.

Thanks!

quote:
Originally posted by visakh16

do you mean subject that is effective as of date considered? (current date between Start Date & End Date)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources