SQL Server statement using: Select, from, and, as, case, when ,then , else, end successfully

Hello,

   I have not been able to create a the statement correctly without receiving SQL Server database Error: INcorrect syntax near the keyword 'case'.  Can you please advise?  Thank you.

This gives me the error:  SQL Server Database error:  Incorrect syntax near the word 'case' and when is highlighted:

(select  APP_PROSPECT.APP_PROSPECT_STATUS
case when
APP_PROSPECT.APP_PROSPECT_STATUS='APP' then 'APPL'
else 'INQR' end
from APP_PROSPECT where APP_PROSPECT.APPLICANTS_ID=PERSON.ID and APP_PROSPECT.POS=1)as 'Pros Pos1',

What I want to do is to have this sub select clause (Which works)
 
(select APP_PROSPECT.APP_PROSPECT_STATUS from APP_PROSPECT
where APP_PROSPECT.APPLICANTS_ID=PERSON.ID and APP_PROSPECT.POS=1) as 'Pros Pos1',
but, to include the condition:

when
APP_PROSPECT.APP_PROSPECT_STATUS='APP' then 'APPL'
else 'INQR'

Other examples (which I could not follow seem more complex than what I have.  Can you please assist?  Thank you.
mtroutAsked:
Who is Participating?
 
PortletPaulfreelancerCommented:
this appears to be a "correlated subquery" contained within a select clause

select
....
(Select case when APP_PROSPECT.APP_PROSPECT_STATUS='APP' then 'APPL' else 'INQR' end
from  APP_PROSPECT where APP_PROSPECT.APPLICANTS_ID=PERSON.ID and APP_PROSPECT.POS=1) as 'Pros Pos1',
...

and these must only return a single value (i.e. one column, one row) - you may want to include TOP 1 to be sure this mandatory condition is met.

another thing to consider is that subqueries in the select clause can contribute to poor performance.

an alternative might be something like this (putting a join into the from clause):

select
---
, case when X.APP_PROSPECT_STATUS='APP' then 'APPL' else 'INQR' end AS as 'Pros Pos1'
...
from PERSON
left join APP_PROSPECT AS X ON PERSON.ID = X.APPLICANTS_ID  and APP_PROSPECT.POS=1
0
 
mtroutAuthor Commented:
Hello,

         The following gives me two columns; one called Pros Pos1.  The column name I want, but not the data underneath.  It shows the before.  The second column has the heading [No name 1]; but it has the results I want, but not the column name.  I would just like to have the column name of Pros Pos1, with the data that is under the column heading called [No name 1].  Thank you.  



(select APP_PROSPECT.APP_PROSPECT_STATUS from APP_PROSPECT
where APP_PROSPECT.APPLICANTS_ID=PERSON.ID and APP_PROSPECT.POS=1) as 'Pros Pos1',
case when
APP_PROSPECT.APP_PROSPECT_STATUS='APP' then 'APPL'
else 'INQR'
end,

then I have other sub select clauses
(select...which all work well)
0
 
Surendra NathTechnology LeadCommented:
The only reason why it is failing is because you are missing comma here

I just added the comma in the below code, check it out.
(select  APP_PROSPECT.APP_PROSPECT_STATUS,
case when
APP_PROSPECT.APP_PROSPECT_STATUS='APP' then 'APPL'
else 'INQR' end
from APP_PROSPECT where APP_PROSPECT.APPLICANTS_ID=PERSON.ID and APP_PROSPECT.POS=1)as 'Pros Pos1',

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
mtroutAuthor Commented:
Hello Neo_jarvis,

I received the following error:
Lookup Error - SQL Server Database Error: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

But, I tried the following and received the results I was looking for.  However, I would prefer to start with (Select ... so that I can understand what's happening with the code.  Please tell me what to arrange.  Thank you so much.

case when

(APP_PROSPECT.APPLICANTS_ID=PERSON.ID and APP_PROSPECT.POS=1 and
APP_PROSPECT.APP_PROSPECT_STATUS='APP')
then 'APPL'
else 'INQR'
end as 'Pros Pos1',
0
 
Surendra NathTechnology LeadCommented:
give us your entire query...
it seems you have given us only a bit or a piece of it... to solve this issue.
0
 
mtroutAuthor Commented:
Neo_jarvis,

I used the following and it worked.


(Select case when APP_PROSPECT.APP_PROSPECT_STATUS='APP'then 'APPL'
else 'INQR' end
from  APP_PROSPECT where
APP_PROSPECT.APPLICANTS_ID=PERSON.ID and APP_PROSPECT.POS=1) as 'Pros Pos1',
0
 
Surendra NathTechnology LeadCommented:
cool.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.