SQL how to make null or empty to become integer zero

Question

This is my query

  SELECT dp.comment,
     dp.tjah,
     dp.tjih,
     dp.tjor
     FROM dp
     LEFT OUTER JOIN rd ON (dp.uid = rd.uid)
     LEFT OUTER JOIN ri ON (rd.uid = ri.uid)
     LEFT OUTER JOIN rh ON (ri.uid = rh.uid)
     LEFT OUTER JOIN dv ON (rh.name = dv.name)
     WHERE rd.report_datetime='2014-06-20'
     LIMIT 5

This is the output

comment             tjsb                       tjih                       tjor 
------------------  --------------------------  --------------------  ---------------------
HWDP 31 FT                                  16                (NULL)                 (NULL)
15 ft pup joint                         (NULL)                (NULL)                     22
drill pipe range 3                          24                     0                    593
drill collar 31 ft                      (NULL)                    15                 (NULL)
pony DC 10 ft                           (NULL)                (NULL)                      2

As you can see there are plenty of null value how to modify the query so that the null becomes 0 or 0.00 and it's an integer or double value not string.

which db r u using?

Sachu
– Sachu

2016-07-11 10:00:24 +00:00
Commented Jul 11, 2016 at 10:00 — Sachu
– Sachu, Commented Jul 11, 2016 at 10:00
Are u using MySQL RDBMS?

souslov
– souslov

2016-07-11 10:04:05 +00:00
Commented Jul 11, 2016 at 10:04 — souslov
– souslov, Commented Jul 11, 2016 at 10:04

souslov · Accepted Answer · 2016-07-11 10:01:55Z

4

You can use coalesce:

SELECT dp.comment
     , coalesce(dp.total_joints_at_shore_base, 0)
     , coalesce(dp.total_joints_in_hole, 0)
     , coalesce(dp.total_joints_on_rig, 0)
<...>

answered Jul 11, 2016 at 10:01

souslov

44.7k11 gold badges92 silver badges113 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Electronic Circuit Over a year ago

This is the perfect answer although its slower

Electronic Circuit Over a year ago

What if the value totally empty or nothing is it consider null if not how to make it zero

souslov Over a year ago

@rookie_coder: then I guess you need to be build a custom expression using case or if, to include all possible options.

Sachu · Accepted Answer · 2016-07-11 10:05:38Z

2

IN MYSQL USE IFNULL()

SELECT dp.comment,
     ifnull(dp.total_joints_at_shore_base,0),
     ifnull(dp.total_joints_in_hole,0),
     ifnull(dp.total_joints_on_rig,0)
     FROM drill_pipe_daily_summary dp
     LEFT OUTER JOIN report_daily rd ON (dp.daily_uid = rd.daily_uid)
     LEFT OUTER JOIN rig_information ri ON (rd.rig_information_uid = ri.rig_information_uid)
     LEFT OUTER JOIN region_history rh ON (ri.rig_information_uid = rh.rig_information_uid)
     LEFT OUTER JOIN division dv ON (rh.region_name = dv.division_name)
     WHERE rd.report_datetime='2014-06-20'

edited Jul 11, 2016 at 10:05

answered Jul 11, 2016 at 10:01

Sachu

7,7549 gold badges59 silver badges101 bronze badges

2 Comments

Electronic Circuit Over a year ago

it says -----> Error Code: 1582 Incorrect parameter count in the call to native function 'isnull'

Sachu Over a year ago

r u using MYSQL then use IFNULL instead of ISNULL

Collectives™ on Stack Overflow

SQL how to make null or empty to become integer zero

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related