CODEWITHSUNDEEP
sqlvertica
excelguy
excelguy

Reputation: 1624

SQL, Then, Replace

Getting an error in on THEN in my query.

case WHEN (I.stackoverflow in 'VBA', 'SQL') 
        THEN replace(I.superuser, '~', '.') 
     WHEN (I.stackoverflow in 'Java', 'design') 
        THEN substr(position_name, (instr(position_name, '_')+2), (len(position_name)-instr(position_name, '_'))) 
     WHEN (I.stackoverflow in 'finance') 
       THEN ((position_name||'.')||currency) 
     WHEN (I.stackoverflow in 'philosophy', 'gaming') 
        THEN ((blah||'.')||currency) 
     WHEN (I.stackoverflow in 'personal finance') 
        THEN cast('text', money) 
     ELSE replace(I.superuser, '~', '.') 
END AS stackexchange

If I comment out the THEN I get an error on WHEN (which appears after my then)

error is:

SQLState:  42601
ErrorCode: 4856

So far I have changed my bracketing approach. Was getting an error on VBA now I.superuser is red in my query.

updated:

case WHEN I.stackoverflow in ('VBA', 'SQL') 
        THEN replace(I.superuser, '~', '.') 
     WHEN I.stackoverflow in ('Java', 'design') 
        THEN substr(position_name, (instr(position_name, '_')+2), (len(position_name)-instr(position_name, '_'))) 
     WHEN I.stackoverflow in ('finance') 
       THEN ((position_name||'.')||currency) 
     WHEN I.stackoverflow in ('philosophy', 'gaming') 
        THEN ((blah||'.')||currency) 
     WHEN I.stackoverflow in ('personal finance') 
        THEN cast('text', money) 
     ELSE replace(I.superuser, '~', '.') 
END AS stackexchange

Upvotes: 0

Views: 350

Answers (2)

Eric
Eric

Reputation: 3257

If you indent your code properly, it's easy to see why your code is not working.

select 
    case 
        when I.Stack in ('VBA','SQL','Excel') THEN replace (I.Overflow, '~', '.')
    end -- missing

Upvotes: 0

tysonwright
tysonwright

Reputation: 1525

Your CASE statement is missing an ELSE and an END:

select 
    case when I.Stack in ('VBA','SQL','Excel') 
    THEN replace (I.Overflow, '~', '.')
    ELSE <what do you want to do when your case isn't true?>
    END
    AS <it helps to have a column alias for logic like this>
FROM
    sometable AS I

Upvotes: 1

Related Questions