CODEWITHSUNDEEP
sqloracle-databasenullzero
Nomad
Nomad

Reputation: 268

Oracle SQL Saying NULL instead of 0 (ZERO)

I have a query where it is subtracting from two columns and then dividing to generate a percentage complete. If the area hasn't been started it propduces NULL I would like it to instead show 00 or 0.

Here is my Query:

substr(count(process_locations.count_attempt_id)/count(processes.process_id),2,2) as "Percentage Complete"

I have try NVL, and COALESE, but am unsure where to place them I have tried a few combinations, but i Receive errors.

I have also tried a CASE WHEN ELSE END, but it still said null.

Upvotes: 3

Views: 1701

Answers (2)

Isaac
Isaac

Reputation: 625

Well, an unpopulated "area" is null unless the field is specified as "not null." If you have permissions, you might want to do that. Otherwise use NVL to substitute any NULLS with zeros.

Upvotes: 0

Rahul Tripathi
Rahul Tripathi

Reputation: 172528

You may try like this:-

NVL(substr(count(process_locations.count_attempt_id)/count(processes.process_id),2,2),0) as "Percentage Complete"

Upvotes: 1

Related Questions