-4

I am trying the code below but the SQL throws an error

case
 when (b.ModuleType in (20) and rtrim(substring(a.PrdAcctId,1,8))='RD')
 then (a.Balance1+(a.Balance2-(a.Balance3+a.Balance4))
 when (b.ModuleType in (20) and rtrim(substring(a.PrdAcctId,1,8))<>'RD')
 then a.Balance1
else a.Balance4 end totbal

That's the error message:

Msg 156, Level 15, State 1, Line 25 Incorrect syntax near the keyword 'when'. Msg 102, Level 15, State 1, Line 27 Incorrect syntax near 'totbal

What am I doing wrong here?

3
  • Hi, and welcome to dba.se! Maybe you could help us out and provide the text of the error? Jul 23 '21 at 5:25
  • Msg 156, Level 15, State 1, Line 25 Incorrect syntax near the keyword 'when'. Msg 102, Level 15, State 1, Line 27 Incorrect syntax near 'totbal'
    – hemu_k
    Jul 23 '21 at 5:40
  • so stupid of me thanksss sir
    – hemu_k
    Jul 23 '21 at 5:43
2

You need to add and extra ) to the end of the first then

  then (a.Balance1+(a.Balance2-(a.Balance3+a.Balance4))

becomes

  then (a.Balance1+(a.Balance2-(a.Balance3+a.Balance4)))

here is a sample of it:

select
    case
     when (b.ModuleType in (20) and rtrim(substring(a.PrdAcctId,1,8))='RD')
     then (a.Balance1+(a.Balance2-(a.Balance3+a.Balance4)))
     when (b.ModuleType in (20) and rtrim(substring(a.PrdAcctId,1,8))<>'RD')
     then a.Balance1
    else a.Balance4 end totbal
from
    (select 'RD' as prdAcctID,2 as balance1,3 as balance2,4 as balance3,5 as balance4) as a
    inner join (select 'RD' as prdAcctID, 20 as ModuleType)b
        on a.prdAcctID = b.prdAcctID
2
  • thanksss sir thanks a lot
    – hemu_k
    Jul 23 '21 at 5:44
  • you're welcome!
    – Sabin B
    Jul 23 '21 at 5:44

Not the answer you're looking for? Browse other questions tagged or ask your own question.