Results 1 to 11 of 11
  1. #1
    Join Date
    Dec 2009
    Posts
    15

    Unanswered: Using DB2v9.5,getting wrong output from the procedure

    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

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I misunderstood the issue. Removed this post.
    Last edited by tonkuma; 03-04-10 at 08:25. Reason: Removed.

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by saching View Post
    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.
    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"?
    ---
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    Dec 2009
    Posts
    15
    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

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    This may work.

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

  6. #6
    Join Date
    Dec 2009
    Posts
    15
    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

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Using DB2v9.5
    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.

  8. #8
    Join Date
    Dec 2009
    Posts
    15
    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

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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 ''') .....
    Last edited by tonkuma; 03-04-10 at 10:55. Reason: Change "TIMESTAMP" to "DATE".

  10. #10
    Join Date
    Dec 2009
    Posts
    15
    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

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

  11. #11
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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...
    .....
    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
    .....
    You may want to specify '"SSO-SMD-UIAGN-51"'.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •