Good morning, Here is my question : I have a table "MyTable", and among available columns, I have : A) a "RowNumber" field. Its function is obvious. B) 6 numeric fields, let's call them C1, C2, .... C6. My goal is to create an additional column, called "COMBINAISON", where, for each row, only the name(s) of non-zero columns would appear. For example : on first row, if all 6 fields are equal to zero, then return "" on first row. On second row, if only C2 and C5 respect the condition "> 0", then return "C2, C5" on second row. On third row, if only C3 > 0, then return "C3" on third row. And so on... Here is what I have reached. No syntax error, but the query runs for a very long time. Thank you for your help. /* We create a copy, as the table can't
update itself : */
create table COPIE_DE_LA_TABLE
AS select *
FROM MyTable;
/* "ALTER + UPDATE" in order to
define combinations : */
ALTER TABLE MaTable
ADD COMBINAISON CHAR(100);
UPDATE MyTable
SET COMBINAISON =
(SELECT
CASE WHEN LENGTH(liste) > 0
THEN SUBSTR(liste, 1,
LENGTH(liste) - 1)
ELSE ''
END
FROM ( select
CASE WHEN C1 > 0
THEN 'C1,' ELSE '' END
|| CASE WHEN C2 > 0
THEN 'C2,' ELSE '' END
|| CASE WHEN C3 > 0
THEN 'C3,' ELSE '' END
|| CASE WHEN C4 > 0
THEN 'C4,' ELSE '' END
|| CASE WHEN C5 > 0
THEN 'C5,' ELSE '' END
|| CASE WHEN C6 > 0
THEN 'C6,' ELSE '' END
AS liste
/* And here comes the moment to
use the copy : */
FROM COPIE_DE_LA_TABLE
WHERE MyTable.RowNumber
= COPIE_DE_LA_TABLE.RowNumber
) )
;
... View more
