SHARE
TWEET

Untitled

a guest Sep 17th, 2019 83 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.     ss.id_sim_stat,
  7.     ss.name as 'sim_state_name',
  8.     si.id_batt_left,
  9.     si.id_batt_right,
  10.     si.name as 'sim_name',
  11.     si.begin_time,
  12.     si.end_time,
  13.     replace (
  14.         concat(
  15.            
  16.             '[',
  17.             (case when sum(case when sd.current is not null then 1 else 0 end) - count(*) = 0 then '"sim_current",' else '' end),
  18.             (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),
  19.             (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),
  20.             (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),
  21.             (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),
  22.             (case when sum(case when ld.motor_curr is not null then 1 else 0 end) - count(*) = 0 then '"log_motor_curr",' else '' end),
  23.             (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),
  24.             (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),
  25.             ']'
  26.  
  27.         ),
  28.         ',]',
  29.         ']'
  30.     ) as 'no_empty_data'
  31. from
  32.     sim_data sd
  33.         join sim_info si
  34.             using(id_sim_info)
  35.             join sim_stat ss
  36.                 using(id_sim_stat)
  37.                 join log_data ld
  38.                     using(id_log_data)
  39. where
  40.     si.id_sim_info > 0
  41. group by
  42.     sd.id_sim_info
  43. order by
  44.     sd.id_sim_info
  45. desc
  46. ;
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