SELECT COALESCE(SUM(CASE WHEN V_COLOR=’R’ THEN B_VOL ELSE 0 END), 0) AS b_r,
COALESCE(SUM(CASE WHEN V_COLOR=’G’ THEN B_VOL ELSE 0 END), 0) AS b_g,
COALESCE(SUM(CASE WHEN V_COLOR=’B’ THEN B_VOL ELSE 0 END), 0) AS b_b
А у меня вот так 🙂 WITH imported AS (
INSERT INTO contract_info AS ci (number, contract_date)
SELECT
ici.number,
ici.contract_date
FROM imp_contract_info ici
WHERE ici.imp_session_guid = p_imp_session_guid AND ici.imp_status = 'added'::gsc.import_status
ON CONFLICT ON CONSTRAINT contract_info_pkey
DO UPDATE SET contract_date = EXCLUDED.contract_date
RETURNING ci.number)
/* update import status from returning result */
UPDATE gsc.imp_contract_info ici
SET imp_status = 'imported'::gsc.import_status
WHERE ici.imp_session_guid = p_imp_session_guid and ici.number = ANY(select i.number from imported i);
Из последнего:
AFTER TABLE word ADD COLUMN class_id INT
UPDATE word SET class_id = 2057 WHERE type = ‘verb’
UPDATE word SET class_id = 2058 WHERE type = ‘noun’
UPDATE word SET class_id = 2059 WHERE type = ‘adj’
… А у меня — вот так примерно:
SELECT COALESCE(SUM(CASE WHEN V_COLOR=’R’ THEN B_VOL ELSE 0 END), 0) AS b_r,
COALESCE(SUM(CASE WHEN V_COLOR=’G’ THEN B_VOL ELSE 0 END), 0) AS b_g,
COALESCE(SUM(CASE WHEN V_COLOR=’B’ THEN B_VOL ELSE 0 END), 0) AS b_b
🙂
А-а-а!!!
Не люблю MS SQL 🙂
Мож это Оракл:)
А у меня вот так 🙂
WITH imported AS (INSERT INTO contract_info AS ci (number, contract_date)
SELECT
ici.number,
ici.contract_date
FROM imp_contract_info ici
WHERE ici.imp_session_guid = p_imp_session_guid AND ici.imp_status = 'added'::gsc.import_status
ON CONFLICT ON CONSTRAINT contract_info_pkey
DO UPDATE SET contract_date = EXCLUDED.contract_date
RETURNING ci.number)
/* update import status from returning result */
UPDATE gsc.imp_contract_info ici
SET imp_status = 'imported'::gsc.import_status
WHERE ici.imp_session_guid = p_imp_session_guid and ici.number = ANY(select i.number from imported i);
Из последнего:
AFTER TABLE word ADD COLUMN class_id INT
UPDATE word SET class_id = 2057 WHERE type = ‘verb’
UPDATE word SET class_id = 2058 WHERE type = ‘noun’
UPDATE word SET class_id = 2059 WHERE type = ‘adj’
Может, всё же
ALTER TABLE? 🙂Ну да, конечно ALTER =)