Solved

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

Posted on 2013-06-20
7
506 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
Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

 
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 300 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

718 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