I am calculating the value for a column in a view using cast. I want to apply the first when statement, then the else statement but I get the error: [Error] Execution (370: 68): ORA-00907: missing right parenthesis
I am trying to assign 1 as the position sequence, if there is only 1 distinct position per person. I want to apply the calculation in the else statement to calculate all others.
Here is my code below
CAST (
case
when
--addition when distinct position for person = 1 then 1
exists (select count(distinct(position)), count(*)
from REPORT_DETAILS
where REPORT_DETAILS.PERSON_ID = src.PERSON_ID
group by PERSON_ID
having count(*) =1)
Then 1
Else
(SUBSTR (position,
1,
INSTR (position, '-') - 1) =
PRIMARY_POSITION_NUMBER
AND SUBSTR (position,
INSTR (position, '-') + 1) =
PRIMARY_POSITION_NUMBER_SUFFIX
)END
AS INTEGER)
POSN_SEQUENCE,
Aucun commentaire:
Enregistrer un commentaire