• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 513
  • Last Modified:

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.
0
mtrout
Asked:
mtrout
  • 3
  • 3
1 Solution
 
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
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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
 
PortletPaulCommented:
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

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now