| Line 163... |
Line 163... |
| 163 |
/* Some convenience views that return only current entries (ie, removed_date is null) */
|
163 |
/* Some convenience views that return only current entries (ie, removed_date is null) */
|
| 164 |
|
164 |
|
| 165 |
create or replace view current_client as select * from client where removed_date is null;
|
165 |
create or replace view current_client as select * from client where removed_date is null;
|
| 166 |
create or replace view current_site as select * from site where removed_date is null;
|
166 |
create or replace view current_site as select * from site where removed_date is null;
|
| 167 |
create or replace view current_device as select * from device where removed_date is null;
|
167 |
create or replace view current_device as select * from device where removed_date is null;
|
| - |
|
168 |
create or replace view view_device_systems as select * from device where device_type_id in (select device_type_id from device_type where show_as_system = 'Y');
|
| - |
|
169 |
|
| 168 |
|
170 |
|
| 169 |
/* A couple of additional convenience views */
|
171 |
/* A couple of additional convenience views */
|
| 170 |
create or replace view current_systems as select * from device where removed_date is null and device.device_type_id in (select device_type_id from device_type where show_as_system = 'Y');
|
172 |
create or replace view current_systems as select * from device where removed_date is null and device.device_type_id in (select device_type_id from device_type where show_as_system = 'Y');
|
| 171 |
create or replace view client_site as select concat( client.name, ' - ', ifnull(site.name,'None' )) name, client.client_id,site.site_id from client left outer join site using (client_id);
|
173 |
create or replace view client_site as select concat( client.name, ' - ', ifnull(site.name,'None' )) name, client.client_id,site.site_id from client left outer join site using (client_id);
|
| 172 |
create or replace view client_systems as select device.device_id,concat(device.name, ' (', client_site.name, ')') 'Device' from device join client_site using (site_id) join device_type using (device_type_id) where device_type.show_as_system = 'Y';
|
174 |
create or replace view client_systems as select device.device_id,concat(device.name, ' (', client_site.name, ')') 'Device' from device join client_site using (site_id) join device_type using (device_type_id) where device_type.show_as_system = 'Y';
|