mtrout
asked on
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='AP P' 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='AP P' then 'APPL'
else 'INQR'
Other examples (which I could not follow seem more complex than what I have. Can you please assist? Thank you.
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_
case when
APP_PROSPECT.APP_PROSPECT_
else 'INQR' end
from APP_PROSPECT where APP_PROSPECT.APPLICANTS_ID
What I want to do is to have this sub select clause (Which works)
(select APP_PROSPECT.APP_PROSPECT_
where APP_PROSPECT.APPLICANTS_ID
but, to include the condition:
when
APP_PROSPECT.APP_PROSPECT_
else 'INQR'
Other examples (which I could not follow seem more complex than what I have. Can you please assist? Thank you.
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.
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',
ASKER
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_I D=PERSON.I D and APP_PROSPECT.POS=1 and
APP_PROSPECT.APP_PROSPECT_ STATUS='AP P')
then 'APPL'
else 'INQR'
end as 'Pros Pos1',
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_I
APP_PROSPECT.APP_PROSPECT_
then 'APPL'
else 'INQR'
end as 'Pros Pos1',
give us your entire query...
it seems you have given us only a bit or a piece of it... to solve this issue.
it seems you have given us only a bit or a piece of it... to solve this issue.
ASKER
Neo_jarvis,
I used the following and it worked.
(Select case when APP_PROSPECT.APP_PROSPECT_ STATUS='AP P'then 'APPL'
else 'INQR' end
from APP_PROSPECT where
APP_PROSPECT.APPLICANTS_ID =PERSON.ID and APP_PROSPECT.POS=1) as 'Pros Pos1',
I used the following and it worked.
(Select case when APP_PROSPECT.APP_PROSPECT_
else 'INQR' end
from APP_PROSPECT where
APP_PROSPECT.APPLICANTS_ID
cool.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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_
where APP_PROSPECT.APPLICANTS_ID
case when
APP_PROSPECT.APP_PROSPECT_
else 'INQR'
end,
then I have other sub select clauses
(select...which all work well)