decode function conversion help

K P 61 Reputation points
2021-09-24T12:33:11.99+00:00

I m converting a script from oracle to sql - cannot come over with decode function. please help. below it is.
round(abs( decode(v.transcurr,'USD',sum(nvl(v.calc_foramount,0)), decode( c.rate_type,'I',((sum(nvl(v.calc_foramount,0)))/c.currate),'D',((sum(nvl(v.calc_foramount,0)))*c.currate) ) ) )) usd_equiv

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,552 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Tom Phillips 17,716 Reputation points
    2021-09-24T20:03:47.59+00:00
  2. MelissaMa-MSFT 24,176 Reputation points
    2021-09-27T01:59:19.103+00:00

    Hi @K P ,

    Welcome to Microsoft Q&A!

    decode(v.transcurr,'USD',sum(nvl(v.calc_foramount,0)), decode( c.rate_type,'I',((sum(nvl(v.calc_foramount,0)))/c.currate),'D',((sum(nvl(v.calc_foramount,0)))*c.currate) )

    Please refer below to replace above part and check whether it is working. 

    case when v.transcurr='USD' then sum(isnull(v.calc_foramount,0))  
else  
    case when c.rate_type='I' then ((sum(isnull(v.calc_foramount,0)))/c.currate)  
	     when c.rate_type='D' then ((sum(isnull(v.calc_foramount,0)))*c.currate)  
	end  
end

    Best regards,
    Melissa

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments