Hi ,

Using DB2v9.5 ,

while passing the parameter like columnname in procedure getting the wrong output.

here is the below Table & procedure

Table db2admin.TEST1(INDI_RPT_VALUE integer);

Procedure
---------------------------------------------------------------------
CREATE PROCEDURE SSOGDF.test(IN P_INDI_COL VARCHAR(50))
LANGUAGE SQL
BEGIN

DECLARE V_TICKET_MR INTEGER;
DECLARE INDI_COL VARCHAR(50);
DECLARE V_STMT VARCHAR(500);
DECLARE V_SQL VARCHAR(500);
DECLARE at_end INT DEFAULT 0;
DECLARE not_found CONDITION FOR SQLSTATE '02000';

DECLARE test5_cur CURSOR FOR V_STMT ;

SET INDI_COL = P_INDI_COL;

Begin

DECLARE GLOBAL TEMPORARY TABLE SESSION.WTKT_TMP(
INDI_COL_VALUE INTEGER
)
WITH REPLACE ON COMMIT PRESERVE ROWS NOT LOGGED;

INSERT INTO SESSION.WTKT_TMP
SELECT count(INDI_COL)
FROM SSOGDF.TICKET_FACT
WHERE INDI_COL is not null
end ;

SET V_SQL = 'SELECT INDI_COL_VALUE FROM SESSION.WTKT_TMP';

PREPARE V_STMT FROM V_SQL;
BEGIN
DECLARE CONTINUE HANDLER FOR not_found SET at_end = 1;

OPEN test5_cur;
in_loop :
LOOP
FETCH test5_cur INTO V_TICKET_MR;
IF at_end = 1 then
leave in_loop;
END IF;

INSERT INTO db2admin.TEST1(INDI_RPT_VALUE)
VALUES (V_TICKET_MR);

END LOOP;
CLOSE test5_cur;
END;
END@
---------------------------------------------------------------------
getting the wrong out (It's total number of null value)while inserting the data into global table, it should be total number of not null values.
While passing the parameter in procedure it's string (colname name)
& it's not reading in where condition.

please give some idea about this

ThankX

I misunderstood the issue. Removed this post.

Please explain how you determine that. Your procedure does not have any OUT parameters, and the cursor is closed before the return, so what exactly is the "wrong out"?

Thanks for u'r reply

While inserting the global temporary table itself it's counting the total number of record not counting the particular column value.

is there any other syntex to pass the parameter (column name)?

Please help me

This may work.

EXECUTE IMMEDIATE
'INSERT INTO SESSION.WTKT_TMP
SELECT count(*)
FROM SSOGDF.TICKET_FACT
WHERE ' || P_INDI_COL || ' is not null';

Thanks it's working but this one is not working

DECLARE GLOBAL TEMPORARY TABLE SESSION.WEKLYTKT_TMP(
UNIQUE_POOL_ID VARCHAR(25)
,SERVICELINEID VARCHAR(5)
,SLCOMPID VARCHAR(5)
,YEAR INTEGER
,WEEK INTEGER
,INDI_COL_VALUE INTEGER
)
WITH REPLACE ON COMMIT PRESERVE ROWS NOT LOGGED;

set v_exec_sql = 'INSERT INTO SESSION.WEKLYTKT_TMP
SELECT UNIQUE_POOL_ID,SERVICELINEID,SLCOMPID,YEAR,WEEK,CO UNT('CONCAT P_INDI_COL CONCAT ')
FROM SSOGDF.TICKET_VW
WHERE UPPER(UNIQUE_POOL_ID) = UPPER(' CONCAT P_UNIQUEPOOLID CONCAT ')
AND UPPER(SERVICELINEID) = UPPER(' CONCAT P_SERVICELINEID CONCAT ')
AND UPPER(SLCOMPID) = UPPER(' CONCAT P_SLCOMPID CONCAT ')
AND WEEKSDATE BETWEEN ' CONCAT V_FRMDATE CONCAT '
AND ' CONCAT V_TODATE CONCAT '
GROUP BY UNIQUE_POOL_ID,SERVICELINEID,SLCOMPID,YEAR,WEEK';

EXECUTE IMMEDIATE v_exec_sql;

i am getting the following error
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0440N No authorized routine named "CONCAT" of type "FUNCTION" having
compatible arguments was found. LINE NUMBER=56. SQLSTATE=42884

SQL0440N No authorized routine named "CONCAT" of type "FUNCTION " having compatible arguments was found.


Please suggest me

Arguments of "CONCAT" should be character strings.

Check data types of V_FRMDATE, V_TODATE, so on.
If some of them were not CHAR/VARCHAR, cast them to CHAR explicitly.
In other words, surround them with CHAR().

DB2 9.7 will convert more data types automatically.

Thanks tonkuma,

Actually V_FRMDATE, V_TODATE, is the Timestamp data type

i have made as char(v_frmdate),char (v_todate) it 's working but while calling the procdure it's given the error

================================================
SQL0104N An unexpected token ".00" was found following "EEN
2009-01-01-00.00". Expected tokens may include: "AND". SQLSTATE=42601

SQL0104N An unexpected token ".00" was found following "EEN 2009-01-01-00.00". Expected tokens may include: "AND ".
=================================================

Please suggest

Try this.

..... BETWEEN ''' CONCAT CHAR(V_FRMDATE) CONCAT '''
AND ''' CONCAT CHAR(V_TODATE) CONCAT ''' .....

or

..... BETWEEN DATE(''' CONCAT CHAR(V_FRMDATE) CONCAT ''')
AND DATE(''' CONCAT CHAR(V_TODATE) CONCAT ''') .....

Thanks for u'r reply , i have tried but i am getting the run time error. can u give me some idea about how to pass the host variable in Exceute Immediate statement.
Run Time error
==================================================
CALL SSOGDF.P1('SSO-SMD-UIAGN-51','SMD','UIAGN',1,53,2009,2010,'SEV1','SEV1_REPO RT')
SQL0206N "SSO" is not valid in the context where it is used. SQLSTATE=42703

SQL0206N "SSO " is not valid in the context where it is used.

Explanation:

This error can occur in the following cases:
* For an INSERT or UPDATE statement, the specified column is not a
column of the table, or view that was specified as the object of the
insert or update.
* For a SELECT or DELETE statement, the specified column is not a
column of any of the tables or views identified in a FROM clause in
the statement.
* For an assignment statement, the reference name does not resolve to
the name of a column or variable.
* For an ORDER BY clause, the specified column is a correlated column
reference in a subselect, which is not allowed.
* For a CREATE TRIGGER, CREATE METHOD or CREATE FUNCTION statement:
* The reference "<name>" does not resolve to the name of a column,
local variable or transition variable.
* The condition name "<name>" specified in the SIGNAL statement has
not been declared.

* For a CREATE TRIGGER statement:
* A reference is made to a column of the subject table without using
an OLD or NEW correlation name.
* The left hand side of an assignment in the SET transition-variable
statement in the triggered action specifies an old transition
variable where only a new transition variable is supported.

* For a CREATE FUNCTION statement with a PREDICATES clause:
* The RETURN statement of the SQL function references a variable
that is not a parameter or other variable that is in the scope of
the RETURN statement.
* The FILTER USING clause references a variable that is not a
parameter name or an expression name in the WHEN clause.
* The search target in an index exploitation rule does not match
some parameter name of the function that is being created.
* A search argument in an index exploitation rule does not match
either an expression name in the EXPRESSION AS clause or a
parameter name of the function being created.

* For a CREATE INDEX EXTENSION statement, the RANGE THROUGH clause or
the FILTER USING clause references a variable that is not a parameter
name that can be used in the clause.

The statement cannot be processed.

User response:

Verify that the names are specified correctly in the SQL statement. For
a SELECT statement, ensure that all the required tables are named in the
FROM clause. For a subselect in an ORDER BY clause, ensure that there
are no correlated column references. If a correlation name is used for a
table, verify that subsequent references use the correlation name and
not the table name.

For a CREATE TRIGGER statement, ensure that only new transition
variables are specified on the left hand side of assignments in the SET
transition-variable statement and that any reference to columns of the
subject table have a correlation name specified.

sqlcode: -206

sqlstate: 42703

================================================== ============

CALL SSOGDF.P1('SSO-SMD-UIAGN-51','SMD','UIAGN',1,53,2009,2010,'SEV1','SEV1_REPO RT')
If first parameter is column name, SSO-SMD-UIAGN-51 is not valid column name.

It was pointed out in error message you received, like this...
You may want to specify '"SSO-SMD-UIAGN-51"'.