This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(32, 81%, 13%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EKP%3C/text%3E%3C/svg%3E)
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
Hi @K P ,
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
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(67.2, 1%, 16%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ETP%3C/text%3E%3C/svg%3E)
I tried Case statement but ---'D',((sum(nvl(v.calc_foramount,0)))*c.currate) -- at this part i am getting err.
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.