Почему мой SQL-синтаксис не работает для создания представления?

Может ли кто-нибудь сказать, почему SQL продолжает давать ошибки при попытке создать представление?

create view VAR_PEAKING as 
 select MEASURE_1 as PKN_1, MEASURE_2 as PKN_2, MEASURE_3 as PKN_3
 from LIVE_MEAS
 WHERE VARIABLE = 'PKN',
 select MEASURE_1 as PKS_1, MEASURE_2 as PKS_2, MEASURE_3 as PKS_3
 from LIVE_MEAS
 WHERE VARIABLE = 'PKS'
1 ответ

Предполагая, что оба SELECT возвращают строки, как это? (UNION, а не запятая)

create view VAR_PEAKING as 
select 
 MEASURE_1 as PKN_1,
 MEASURE_2 as PKN_2,
 MEASURE_3 as PKN_3,
 NULL AS pks_1,
 NULL AS pks_2,
 NULL AS pks_3
from LIVE_MEAS
WHERE VARIABLE = 'PKN'
UNION
select
 NULL AS pkn_1,
 NULL AS pkn_2,
 NULL AS pkn_3,
 MEASURE_1 as PKS_1,
 MEASURE_2 as PKS_2,
 MEASURE_3 as PKS_3
from LIVE_MEAS
WHERE VARIABLE = 'PKS'

Любая вероятность того, что вы разместите образцы данных, структуры данных и желаемые результаты?

Это может быть то, что вы ищете:

create view VAR_PEAKING as 
SELECT
 a.MEASURE_1 as PKN_1,
 a.MEASURE_2 as PKN_2,
 a.MEASURE_3 as PKN_3, 
 b.MEASURE_1 as PKS_1, 
 b.MEASURE_2 as PKS_2, 
 b.MEASURE_3 as PKS_3
from LIVE_MEAS a JOIN LIVE_MEAS b ON a.[col]=b.[col]
WHERE a.VARIABLE = 'PKN' AND b.VARIABLE = 'PKS'

licensed under cc by-sa 3.0 with attribution.