EXISTS

Well, finally I lost one day to find a solution for a simple problem. I

wanted to derive two tables... that seemed to be simple... but it isn't.

My last question for today:

Why does my sql query return the error: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS

The Query:

SELECT

Configurations.AlarmId, Configurations.Mode, Configurations.Activated,

Configurations.Empty, Configurations.UserName, Configurations.datetime,

(SELECT ConfigurationId, Setpoint1, Alarm

FROM

dbo.ExtendedValues(Configurations.ConfigurationId) AS ExtendedValues)

AS Expr1

FROM Configurations INNER JOIN

Controllers ON Configurations.ConfigurationId =

Controllers.ActiveConfig INNER JOIN

Tanques ON Controllers.ControllerId = Tanques.ControllerId

WHERE (Tanques.TanqueId = 1)

Where do I have to enter EXISTS?

Thanks for every hint!!

P.D.: The ExtendedValues Function:

CREATE FUNCTION dbo.ExtendedValues

(

@.ConfigurationId int = -1

)

RETURNS TABLE

AS

RETURN SELECT *

FROM ConfigurationsTanques

WHERE ConfigurationsTanques.ConfigurationId = @.ConfigurationId

You have put a sub-query into your Select list. Such a sub-query can only return one value, not three the way you have. The Exists error message is misleading in this case.

I think I see how you got into this position. You are trying to call the ExtendedValues() function for each value of Configurations, but that is not legal either. Functions that return tables can only be placed in the From clause and must take constant parameters.

Unless the function that you included is just a stub and the real one is more complex, you do not need a function in this case. You only need to add the ConfigurationTanques table into your From clause.