question

KP-5037 avatar image
0 Votes"
KP-5037 asked MelissaMa-msft commented

decode function conversion help

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

sql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @KP-5037,

Could you please validate the answer and provide any update?

If it is not working, please provide your complete query together with DDL and some sample data.

Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread. 

Thank you for understanding!

Best regards,
Melissa

0 Votes 0 ·
TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered KP-5037 commented
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I tried Case statement but ---'D',((sum(nvl(v.calc_foramount,0)))*c.currate) -- at this part i am getting err.

0 Votes 0 ·
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

Hi @KP-5037,

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.