how to give default value to subquery in mysql?

Question

In my query, can i give some default value to subquery?

One of my subquery might not have any value.. in that case it returns null as whole result. Can I assign some default value if even one of subquery is null?

select 
    t1 . *, t2.speed, t3.Accelerometerx
from
    (SELECT 
        *
    FROM
        `table1`
    ) as t1,
    (SELECT 
       *
    FROM
        table1)
    as t2

possible duplicate of [MySql Query Replace NULL with Empty String in Select](http://stackoverflow.com/questions/9560723/mysql-query-replace-null-with-empty-string-in-select) — Jenn, Jul 14 '14 at 18:01

score 0 · Answer 1 · answered Jun 12 '14 at 08:46

0

Check the nvl function, it should solve your problem.

answered Jun 12 '14 at 08:46

user3722371

145
6

I have tried ifnull .. but didnt work: select t1 . *, t2.speed, ifnull(t3.Accelerometerx,"") ..... – user2595243 Jun 12 '14 at 08:52
What do you mean it doesn't work? It returns an error, or it just doesn't give you the results you want to get? – user3722371 Jun 12 '14 at 08:53
it doesnt return any value – user2595243 Jun 12 '14 at 08:54

score 0 · Answer 2 · answered Jun 12 '14 at 08:58

USE CASE/IF:

CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE

EXAMPLE, instead of:

where
    `vehicle_tripdetails`.`Param_ID` = '131'
        and trip_id = 127) as t1,
(SELECT 
    time_stamp, `vehicle_tripdetails`.`Param_Data` as speed
FROM
    `vehicle_tripdetails`
where
    `vehicle_tripdetails`.`Param_ID` = '13'
        and trip_id = 127) as t2,

You can use:

where
    `vehicle_tripdetails`.`Param_ID` = '131'
        and trip_id = 127) as t1,
CASE 
  WHEN (SELECT  time_stamp, `vehicle_tripdetails`.`Param_Data` as speed FROM
    `vehicle_tripdetails` where
    `vehicle_tripdetails`.`Param_ID` = '13'
        and trip_id = 127) 
  IS NOT NULL 
THEN (SELECT  time_stamp, `vehicle_tripdetails`.`Param_Data` as speed FROM
    `vehicle_tripdetails` where
    `vehicle_tripdetails`.`Param_ID` = '13'
        and trip_id = 127) 
ELSE
   'default value'
END CASE
AS t2

score 0 · Answer 3 · answered Jun 12 '14 at 11:00

Use ISNull in your select clause as below

select    t1.*,
          CASE ISNULL(t2.speed) WHEN 1 THEN 0 ELSE t2.speed END,
          CASE ISNULL(t3.xyz) WHEN 1 THEN 0 ELSE t3.xyz END

Here the default value is 0. This will work if you don't select all records using *

how to give default value to subquery in mysql?

3 Answers3