SPL workaround???
Greetings all,
I have a stored procedure which explodes a bill of materials into a temp file,
then summarizes the quantities from the temp file into a more permanent file.
My problem is that the summarization step does not work. It does the regular
columns fine, but all of the SUMmed quantities wind up being 0. I had it
working for some products - but not for all, so I re-structured it slightly
and now it doesn't work for anything. I can execute the procedure from
dbaccess and then turn around and run the summarization step manually and it
works, just not from SPL. Is there somethine I don't know (ok - something I
SHOULD know about THIS) or a work around someone can suggest? Here is the
relevant code, Online 5.0 engine.
CREATE PROCEDURE expl_prods()
DEFINE par_part CHAR(16);
DEFINE part_no CHAR(16);
DEFINE qty decimal(8,4);
{ work space }
CREATE TEMP TABLE tmp_matl_list(parent CHAR(16),
part_n CHAR(16),
qty decimal(8,4));
CREATE INDEX tmp_idx ON tmp_matl_list (parent, part_n);
DELETE FROM bill_of_mtl_lw_lvl; { clear out old data }
FOREACH { find parents }
SELECT product_nr
INTO par_part
FROM products:product
{ explode them into temp }
FOREACH EXECUTE PROCEDURE prod_expl(par_part) INTO part_no, qty
INSERT INTO tmp_matl_list VALUES(par_part, part_no, qty); END FOREACH
END FOREACH
{ summarize results }
INSERT INTO bill_of_mtl_lw_lvl (prod_nr, part_nr, part_use_qt,
active_info_cd, load_date)
SELECT parent, part_n, sum(qty), 'Y', TODAY
FROM tmp_matl_list
GROUP BY parent, part_n
{ keep stats up to date }
UPDATE STATISTICS FOR TABLE bill_of_mtl_lw_lvl;
END PROCEDURE;
adv(thanks)ance
j.
_____________________________________________________________________________
Jack Parker yauib* - Hewlett Packard, BSMC Boise, Idaho, USA
jparker@hpbs3645.boi.hp.com
_____________________________________________________________________________
* yet another unix/informix bigot
_____________________________________________________________________________
Any opinions expressed herein are my own and not those of my employers.
_____________________________________________________________________________