Solved

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

Posted on 2013-06-20
7
505 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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 48

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

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

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

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

738 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