updating 'LIST' elment from SPL
Hi, everybody,
The following example taken from "9.21 SQL TUTORIAL"
doesn't work neither with 9.21 nor with 9.40; Similar example
taken from '9.40 tutorial' also doesn't work with 9.40:
------------------------------
CREATE TABLE manager
(
mgr_name VARCHAR(30),
department VARCHAR(12),
dept_no SMALLINT,
direct_reports SET( VARCHAR(30) NOT NULL ),
projects LIST( ROW ( pro_name VARCHAR(15),
pro_members SET( VARCHAR(20) NOT NULL ) ) NOT NULL),
salary INTEGER
);
INSERT INTO manager(mgr_name, department, direct_reports, projects)
VALUES
(
'Sayles', 'marketing',
"SET {'Simonian', 'Waters', 'Adams', 'Davis', 'Jones'}","LIST{ " ||
"ROW ('voyager_project', SET{'Simonian', 'Waters','Adams', 'Davis'}),
" ||
"ROW ('horizon_project', SET{'Freeman', 'Jacobs','Walker', 'Smith',
'Cannan'}), " ||
"ROW ('saphire_project', SET{'Villers', 'Reeves','Doyle', 'Strongin'})
" ||
"}"
);
CREATE PROCEDURE update_pro(mgr VARCHAR(30), pro VARCHAR(15))
DEFINE p COLLECTION;
-- In Informix example, the SPL line below simply said "DEFINE r
ROW;"
-- that line produced the following syntax error "999: Not
implemented yet."
DEFINE r ROW(pro_name VARCHAR(15), pro_members SET(VARCHAR(20) NOT
NULL));
LET r = ROW("project", "SET{'member'}");
SELECT projects INTO p FROM manager
WHERE mgr_name = mgr;
FOREACH cursor1 FOR
SELECT * INTO r FROM TABLE(p)
IF (r.pro_name == 'horizon_project') THEN
-- The following operator gives runtime error:
UPDATE TABLE(p) SET pro_name = pro
WHERE CURRENT OF cursor1;-- Executing this procedure produces error -217:
-- Column (pro_name) not found in any table in the query (or SLV is
undefined).
EXIT FOREACH;
END IF;
END FOREACH
UPDATE manager SET projects = p
WHERE mgr_name = mgr;
END PROCEDURE;
EXECUTE PROCEDURE update_pro('Sayles', 'my_project');-----------------------------------------------
I know, that the workaround is to convert list explicitly to a temporary
table, make an update on that table and convert it back to a list.
Nevertheless, it looks very strange to me that an example from 'tutorial'
doesn't work. May be, situation can be corrected by minor change in
the 'update' SQL statement?
-------------
best regards,
Alexey
sending to informix-list