?
Solved

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

Posted on 2013-06-20
7
Medium Priority
?
507 Views
Last Modified: 2013-06-21
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
Comment
Question by:mtrout
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 

Author Comment

by:mtrout
ID: 39264095
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
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39264215
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
 

Author Comment

by:mtrout
ID: 39264256
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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39264344
give us your entire query...
it seems you have given us only a bit or a piece of it... to solve this issue.
0
 

Author Comment

by:mtrout
ID: 39264395
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
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39264439
cool.
0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 1200 total points
ID: 39264727
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

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question