hi guys, i need urgent help
i run the following in warehouse design studio to deploy a stored procedure to db2
CREATE PROCEDURE OKMART_ASSOC_PROC ( IN SUPPORT INTEGER,
IN LENGTH INTEGER )
DYNAMIC RESULT SETS 1
------------------------------------------------------------------------
-- SQL Stored Procedure
-- SUPPORT
-- LENGTH
------------------------------------------------------------------------
P1: BEGIN
-- Declare cursor
DECLARE cursor1 CURSOR WITH RETURN FOR
SELECT * FROM OKMART_BI.OKMART_RULES;
SET SCHEMA "OKMARTASSOC";
CREATE VIEW "IWSCHEMA9B5CCD".INPUT_04_0(
"opcode",
"sale_key",
"DayKey",
"sale_time",
"cashier_num",
"ticket_num",
"item_code",
"quantity_sold",
"item_price",
"amount_taken"
) AS
SELECT Q348."opcode" AS "opcode", Q348."sale_key" AS "sale_key",
Q348."DayKey" AS "DayKey", Q348."sale_time" AS "sale_time",
Q348."cashier_num" AS "cashier_num", Q348."ticket_num" AS "ticket_num",
Q348."item_code" AS "item_code", Q348."quantity_sold" AS "quantity_sold",
Q348."item_price" AS "item_price", Q348."amount_taken" AS "amount_taken"
FROM OKMART_BI.UNIT_LINE_ITEM_SLS_FACT Q348;
CREATE VIEW "IWSCHEMA9B5CCD".NAMEMAP_04_0(
"item_code",
"dept_id",
"subdept_id",
"description",
"rcpt_desc",
"internal_id"
) AS
SELECT Q380."item_code" AS "item_code", Q380."dept_id" AS "dept_id",
Q380."subdept_id" AS "subdept_id", Q380."description" AS "description",
Q380."rcpt_desc" AS "rcpt_desc", Q380."internal_id" AS "internal_id"
FROM OKMART_BI.PRODUCT_DIM Q380;
CALL IDMMX.BuildRuleModel('OKMart_Analytics.IM_ASSOC_04',
'INPUT_04_0',
'sale_key',
'SUPPORT',
25.0,
'LENGTH',
'DM_addNmp(''nameMap'',''NAMEMAP_04_0'',''item_code'',''description''),DM_setFldNmp(''item_code'',''nameMap''),DM_setFldUsageType(''amount_taken'',2),DM_setFldUsageType(''ticket_num'',2),DM_setFldUsageType(''DayKey'',2),DM_setFldUsageType(''sale_time'',2),DM_setFldUsageType(''quantity_sold'',2),DM_setFldUsageType(''item_code'',1),DM_setFldUsageType(''cashier_num'',2),DM_setFldUsageType(''item_price'',2),DM_setFldUsageType(''opcode'',2),DM_setMaxNumRules(10000),DM_setAlgorithm(''SIDE'',''<NumBins>5</NumBins>'')');
CREATE VIEW $MODELPORT$INPUT_07_0 (
$LOC$MODEL,
$LOC$TABLENAME,
$LOC$MODELCOLNAME,
$LOC$IDCOLNAME,
$LOC$ID) AS
(SELECT MODEL, 'IDMMX.RULEMODELS', 'MODEL', 'MODELNAME', MODELNAME
FROM IDMMX.RULEMODELS
WHERE MODELNAME = 'OKMart_Analytics.IM_ASSOC_04');
CREATE VIEW INPUT_09_0 AS
(SELECT
RESULT.ID,
RESULT.BODYID,
RESULT.HEAD,
RESULT.HEADNAME,
RESULT.LENGTH,
SUBSTR(RESULT.BODYTEXT,1,1024) AS BODYTEXT,
RESULT.SUPPORT,
RESULT.CONFIDENCE,
RESULT.LIFT
FROM $MODELPORT$INPUT_07_0 AS MODEL,
TABLE (IDMMX.DM_GETRULES(MODEL.$LOC$MODEL)) RESULT);
INSERT INTO "OKMART_BI"."OKMART_RULES" (
"ID",
"BODYID",
"HEAD",
"HEADNAME",
"LENGTH",
"BODYTEXT",
"SUPPORT",
"CONFIDENCE",
"LIFT")
SELECT
INPUT_09_0."ID" AS "ID",
INPUT_09_0."BODYID" AS "BODYID",
INPUT_09_0."HEAD" AS "HEAD",
INPUT_09_0."HEADNAME" AS "HEADNAME",
INPUT_09_0."LENGTH" AS "LENGTH",
INPUT_09_0."BODYTEXT" AS "BODYTEXT",
INPUT_09_0."SUPPORT" AS "SUPPORT",
INPUT_09_0."CONFIDENCE" AS "CONFIDENCE",
INPUT_09_0."LIFT" AS "LIFT"
FROM INPUT_09_0;
DROP VIEW INPUT_09_0;
DROP VIEW $MODELPORT$INPUT_07_0;
DROP VIEW "IWSCHEMA9B5CCD".NAMEMAP_04_0;
DROP VIEW "IWSCHEMA9B5CCD".INPUT_04_0;
-- Cursor left open for client application
OPEN cursor1;
END P1
i want to intergrate this procedure with cognos, so i define parameters in the framework manager that will be used as input by the user. but when i try to invoke the stored procedure i get the following error
RQP-DEF-0177 An error occurred while performing operation 'sqlExecute' status='-51'.
UDA-SQL-0144 An arithmetic exception was detected.
[IBM][CLI Driver][DB2/NT64] SQL0420N Invalid character found in a character string argument of the function "DOUBLE". SQLSTATE=22018
--------------------------------------------------------------------------------
would you please help me.
First question would be: Does the stored procedure work when invoked from DB2? In other words: Is this a Cognos issue or a DB2 issue?
i really had not put that into consideration,thanks for pointing it out. so i ran the sql from the database whilst in warehouse design studio and it gives me the same error
OKMARTASSOC.OKMART_ASSOC_PROC - Run started.
Data returned in result sets is limited to the first 50 rows.
Data returned in result set columns is limited to the first 100 bytes or characters.
OKMARTASSOC.OKMART_ASSOC_PROC - Calling the stored procedure.
OKMARTASSOC.OKMART_ASSOC_PROC - Exception occurred while running:
A database manager error occurred.SQLCODE: -420, SQLSTATE: 22018 - Invalid character found in a character string argument of the function "DOUBLE".. SQLCODE=-420, SQLSTATE=22018, DRIVER=4.11.77
OKMARTASSOC.OKMART_ASSOC_PROC - Roll back completed successfully.
OKMARTASSOC.OKMART_ASSOC_PROC - Run failed.
so i think it is a DB2 error.