Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Question

Select TS.Session# AS SessionId,    
(SELECT Dbo.DecryptString(LastName) + '', '' + Dbo.DecryptString(FirstName) as PatientName 
FROM Persons.Informations WHERE PersonId =
(SELECT PersonId FROM Patients.Informations 
WHERE PatientId = TST.PatientId))
From

Transactions.Session TS
INNER JOIN
Transactions.Payment TP 
ON TS.SessionId = TP.SessionId
INNER JOIN
Transactions.SessionTransaction TST 
ON TST.PaymentId = TP.PaymentId

this is how my query looks..What is wrong with my query?

Answer 1

Your query is returning two column in the select instead of concatenating them like it appears you are trying to do. Try changing the query to single quotes like this:

Select TS.Session# AS SessionId,    
(SELECT Dbo.DecryptString(LastName) + ', ' + Dbo.DecryptString(FirstName) as PatientName 
FROM Persons.Informations WHERE PersonId =
(SELECT PersonId FROM Patients.Informations 
WHERE PatientId = TST.PatientId))
From
Transactions.Session TS
INNER JOIN
Transactions.Payment TP 
ON TS.SessionId = TP.SessionId
INNER JOIN
Transactions.SessionTransaction TST 
ON TST.PaymentId = TP.PaymentId

Answer 2

Barry Marshall's correct. Instead of 1 single quote there are 2 single quotes and so it's not concatenating and that SELECT statement is returning 2 columns (instead of one)

Dbo.DecryptString(LastName),Dbo.DecryptString(FirstName)

Try this example -

SELECT 'a'+'',''+'b'
SELECT 'a'+' , '+'b'

---

Narsimha

Answer 3

Hi Bindu.Vissa,

Try this,

Select TS.Session# AS SessionId,    
(SELECT  Dbo.DecryptString(TOP 1 LastName) + ', ' + Dbo.DecryptString(TOP 1 FirstName) as PatientName
FROM Persons.Informations WHERE PersonId =
(SELECT TOP 1 PersonId FROM Patients.Informations
WHERE PatientId = TST.PatientId))
From

Transactions.Session TS
INNER JOIN
Transactions.Payment TP
ON TS.SessionId = TP.SessionId
INNER JOIN
Transactions.SessionTransaction TST
ON TST.PaymentId = TP.PaymentId