SHARE
TWEET

Untitled

a guest Sep 17th, 2019 91 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. create or replace view sim_info_view
  2. as
  3. select
  4.     si.id_sim_info,
  5.     si.id_log_info,
  6.     date_format(si.begin_time, '%Y-%m-%d') as 'sim_date',
  7.     date_format(si.begin_time, '%T') as 'begin_time',
  8.     date_format(si.end_time, '%T') as 'end_time',
  9.     ss.id_sim_stat,
  10.     ss.name as 'sim_state_name',
  11.     si.id_batt_left,
  12.     si.id_batt_right,
  13.     si.name as 'sim_name',
  14.     replace (
  15.         concat(
  16.            
  17.             '[',
  18.             (case when sum(case when sd.current is not null then 1 else 0 end) - count(*) = 0 then '"sim_current",' else '' end),
  19.             (case when sum(case when sd.batt_left_volt is not null then 1 else 0 end) - count(*) = 0 then '"sim_batt_left_volt",' else '' end),
  20.             (case when sum(case when sd.batt_right_volt is not null then 1 else 0 end) - count(*) = 0 then '"sim_batt_right_volt",' else '' end),
  21.             (case when sum(case when sd.batt_left_temp is not null then 1 else 0 end) - count(*) = 0 then '"sim_batt_left_temp",' else '' end),
  22.             (case when sum(case when sd.batt_right_temp is not null then 1 else 0 end) - count(*) = 0 then '"sim_batt_right_temp",' else '' end),
  23.             (case when sum(case when ld.motor_curr is not null then 1 else 0 end) - count(*) = 0 then '"log_motor_curr",' else '' end),
  24.             (case when sum(case when ld.batt_left_volt is not null then 1 else 0 end) - count(*) = 0 then '"log_batt_left_volt",' else '' end),
  25.             (case when sum(case when ld.batt_right_volt is not null then 1 else 0 end) - count(*) = 0 then '"log_batt_right_volt",' else '' end),
  26.             ']'
  27.  
  28.         ),
  29.         ',]',
  30.         ']'
  31.     ) as 'no_empty_data'
  32. from
  33.     sim_data sd
  34.         join sim_info si
  35.             using(id_sim_info)
  36.             join sim_stat ss
  37.                 using(id_sim_stat)
  38.                 join log_data ld
  39.                     using(id_log_data)
  40. where
  41.     si.id_sim_info > 0
  42. group by
  43.     sd.id_sim_info
  44. order by
  45.     sd.id_sim_info
  46. desc
  47. ;
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top