SQL Error Missing keyword

I'm getting the following case statement error when trying to execute the code below

java.sql.SQLSyntaxErrorException: ORA-00905: missing keyword



Select

A.C_ALJ_APPEAL_NUM AS "Appeal Number"
,A.C_RECONSIDER_NUM AS "Level 2 Appeal Number"



,Case When DT.C_NAME IS NULL Then W.C_NAME
,Else W.C_NAME ||'  '||'('|| DT.C_NAME ||')' AS "Appeal Status"
,End


, TO_CHAR(coalesce(A.C_COMPLETE_REQUEST_RECVD_DT,A.C_REQUEST_RECV_DT), 'MM-DD-YYYY') as "Dt Comp Rqst Recd"
, ' ' as "Begin Hearing Date"
,TO_CHAR (A.C_DLM_DATE, 'MM-DD-YYYY')  AS "Dec Ltr Mailed"
, ' ' as "New Appeal Number"
,ALJ.C_Team_NAme AS "Adjudicator Login"






From

((T_APL A
inner join T_RF_STATE W on A.C_WORKFLOW_STATE = W.ID)
left outer join  T_ALJ ALJ on  ALJ.ID = A.C_ALJ_TEAM
left outer join T_APPEAL_DECISION_WRITING ADW on A.id = ADW.ID_PARENT)
left outer join T_DECISION_TYPE DT on adw.C_DECISION_TYPE = DT.ID

WHERE

A.C_WORKFLOW_STATE not in (136,140,139,143,137,138,130,144) --Draft RFH, Invalid, Not Promotable, Promote Failed ,Waiting for MAS , Validation in Progress ,Un-Promoted, process in MAS    

order by A.C_ALJ_TEAM

]
shieldscoAsked:
Who is Participating?
 
Pawan KumarConnect With a Mentor Database ExpertCommented:
Please try this-

The case statement was not correct.


Select
 A.C_ALJ_APPEAL_NUM AS "Appeal Number"
,A.C_RECONSIDER_NUM AS "Level 2 Appeal Number"
,Case When DT.C_NAME IS NULL Then W.C_NAME Else W.C_NAME ||'  '||'('|| DT.C_NAME ||')' END AS "Appeal Status"
,TO_CHAR(coalesce(A.C_COMPLETE_REQUEST_RECVD_DT,A.C_REQUEST_RECV_DT), 'MM-DD-YYYY') as "Dt Comp Rqst Recd"
,' ' as "Begin Hearing Date"
,TO_CHAR (A.C_DLM_DATE, 'MM-DD-YYYY')  AS "Dec Ltr Mailed"
,' ' as "New Appeal Number"
,ALJ.C_Team_NAme AS "Adjudicator Login"
From 
(
(T_APL A inner join T_RF_STATE W on A.C_WORKFLOW_STATE = W.ID) 
left outer join  T_ALJ ALJ on  ALJ.ID = A.C_ALJ_TEAM
left outer join T_APPEAL_DECISION_WRITING ADW on A.id = ADW.ID_PARENT)
left outer join T_DECISION_TYPE DT on adw.C_DECISION_TYPE = DT.ID
WHERE
A.C_WORKFLOW_STATE not in (136,140,139,143,137,138,130,144) 
order by A.C_ALJ_TEAM 

Open in new window

0
 
Bill PrewCommented:
You can also user NVL2 rather than CASE for what you are doing, I find it a bit tidier in this context, so:

Case When DT.C_NAME IS NULL Then W.C_NAME Else W.C_NAME ||'  '||'('|| DT.C_NAME ||')' END AS "Appeal Status"

Open in new window

becomes:

NVL2(DT.C_NAME, W.C_NAME||'  '||'('||DT.C_NAME||')', DT.C_NAME) AS "Appeal Status"

Open in new window


»bp
0
 
shieldscoAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.