vendredi 13 novembre 2020

If statement with calculation in else statement

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