Subquery not working giving error - Only one expression can be specified

Question

Subquery not working giving error - Only one expression can be specified

Guzman-658444

Old Hand

Points: 347
More actions
December 19, 2008 at 6:01 am

#204215

I would like to get this script to work, it keeps saying:
"Only one expression can be specified in the select list when the subquery is not introduced with EXISTS."
Here is the query:
SELECT * from tblcaseinfo WHERE substring(tblcaseinfo.caseid,4,2) IN
(SELECT * from tblLocalEvaluatorActivities
WHERE tblLocalEvaluatorActivities.UserID = 362 AND tblLocalEvaluatorActivities.Activity = 'CNIC')

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

arun.samuel (12/19/2008)
I would like to get this script to work, it keeps saying:
"Only one expression can be specified in the select list when the subquery is not introduced with EXISTS."
Here is the query:
SELECT * from tblcaseinfo WHERE substring(tblcaseinfo.caseid,4,2) IN
(SELECT * from tblLocalEvaluatorActivities
WHERE tblLocalEvaluatorActivities.UserID = 362 AND tblLocalEvaluatorActivities.Activity = 'CNIC')

You've specified SELECT * from tblLocalEvaluatorActivities. Change '*' to a single column or expression which will match to substring(tblcaseinfo.caseid,4,2).

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

In addition to what Chris has said you may want to do this instead as it performs a bit better:

[font="Courier New"]SELECT

*

FROM

tblcaseinfo T

WHERE

EXISTS (SELECT 1 FROM tblLocalEvaluatorActivities

WHERE tblLocalEvaluatorActivities.UserID = 362 AND

tblLocalEvaluatorActivities.Activity = 'CNIC' AND

columnThatMatches = SUBSTRING(tblcaseinfo.caseid,4,2)

) [/font]

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Guzman-658444 Old Hand Points: 347 More actions · Answer 3

Guzman-658444

Old Hand

Points: 347

December 19, 2008 at 7:12 am

#915257

I will try your suggestions.

Thanks,

Arun

Guzman-658444 Old Hand Points: 347 More actions · Answer 4

Guzman-658444

Old Hand

Points: 347

December 19, 2008 at 7:26 am

#915276

Great. It works! 🙂

Thanks a bunch.

Arun

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

arun.samuel (12/19/2008)
Great. It works! 🙂
Thanks a bunch.
Arun

Arun, please could you post your final working code? Not only does it finish the thread nicely, it may also stand as a good code example for when another user has the same or a similar problem.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden