Just in case anyone thinks they could use this code in the future, be sure to
save it!
We're bringing in a CSV file, and one of the fields is one huge comma separated
list of values, anywhere from 1 to 30 values in the list. I need to load them
into a multi-row table with one item per row. I knew I could do a declare
cursor, but I like to avoid them if I can. So I instead use a small While
loop. The downside is that it will treat every row as if it has the maximum #
of values, and so will load null rows into my temporary table. But I easily
use "where skilldescription is not null" when I insert into my permanent table.
"iskills" is the name of the column.
SELECT (MAX(itemcnt(iskills))) INTO vCount FROM tmpImport
SET VAR vLoop INT = 1
WHILE vLoop <= .vCount THEN
INSERT INTO tmpImportSkills (ilineid, skilldescription) +
SELECT ilineid, (SSUB(iskills,.vLoop)) FROM tmpImport WHERE iskills IS NOT
NULL
SET VAR vLoop = (.vLoop + 1)
ENDWHILE
-- strip off leading space
UPDATE tmpImportSkills SET skilldescription = (SGET(skilldescription,80,2)) +
WHERE (SGET(skilldescription,1,1)) = ' '
Karen
