CODEWITHSUNDEEP
sqloracle-databasepercentage
Nomad
Nomad

Reputation: 268

Oracle SQL displaying 100% as 0%

I have a query that shows percentage of a job being to completion. Here is my query:

NVL(substr(countprocess_locations.count_attempt_id)/count(processes.process_id),2,2),0)||'%' as "Percentage Complete"

Everything has worked fine and beautifully, BUT I recently had a count hit 100% mathematically, but in the output of the query it says 0. I've tried playing with 2,2),0 and no difference so far :/

Any help would be greatly appreciated.

Sample

enter image description here

Upvotes: 1

Views: 197

Answers (1)

Jesse
Jesse

Reputation: 1935

With a working example breaking it down.

159 / 180 = 0.883333

SUBSTR first converts it to text, when doing so Oracle drops the leading zero

.883333

SUBSTR then takes two characters starting at the second

.*88*3333

Then you concatenate '%' to it

88%

When you have 100% it flows such:

2000 / 2000 = 1

Converts to text

1

Takes two characters starting at the second

NULL

Then your NVL returns 0.

Instead of SUBSTR you should * 100 and then ROUND or if you really want to just drop the digits instead of rounding use TRUNC.

NVL(ROUND((countprocess_locations.count_attempt_id)/count(processes.process_id))*100,0),0)||'%'

Upvotes: 4

Related Questions