1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
|
SELECT
eo.mch_code AS moteur,
eo.mch_name AS description,
eo.sup_mch_code AS emplacement,
MAX(CASE WHEN ts.attribute = 'CODEFORME' THEN ts.VALUE_TEXT END) AS codeforme,
MAX(CASE WHEN ts.attribute = 'VITESSE' THEN ts.VALUE_TEXT END) AS vitesse,
MAX(CASE WHEN ts.attribute = 'VITESSE1' THEN ts.VALUE_TEXT END) AS vitesse1,
MAX(CASE WHEN ts.attribute = 'VITESSE2' THEN ts.VALUE_TEXT END) AS vitesse2,
MAX(CASE WHEN ts.attribute = 'PUISSANCE1' THEN ts.VALUE_TEXT END) AS puissance1,
MAX(CASE WHEN ts.attribute = 'PUISSANCE2' THEN ts.VALUE_TEXT END) AS puissance2,
MAX(CASE WHEN ts.attribute = 'MARQUE' THEN ts.VALUE_TEXT END) AS marque,
TO_CHAR(tor.rowversion,'YYYYMMDDHH24MISS') AS objversion
FROM
IFSAPP.technical_object_reference_tab tor
INNER JOIN IFSAPP.technical_specification_tab ts
ON ts.technical_spec_no = tor.technical_spec_no
AND ts.technical_class = tor.technical_class
INNER JOIN IFSAPP.equipment_object_tab eo
ON tor.key_ref = IFSAPP.Client_SYS.Get_Key_Reference('EquipmentObject', 'CONTRACT', eo.contract, 'MCH_CODE', eo.mch_code)
WHERE eo.obj_level IS NULL
AND EXISTS (SELECT 1 FROM IFSAPP.user_allowed_site_pub WHERE eo.contract = site)
GROUP BY eo.mch_code, eo.mch_name, eo.sup_mch_code, tor.rowversion
ORDER BY eo.mch_code |
Partager