CODEWITHSUNDEEP
mysqlsql
Electronic Circuit
Electronic Circuit

Reputation: 335

SQL how to make null or empty to become integer zero

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.

Upvotes: 0

Views: 169

Answers (2)

Sachu
Sachu

Reputation: 7766

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'

Upvotes: 2

potashin
potashin

Reputation: 44581

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)
<...>

Upvotes: 4

Related Questions