Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2002
    Posts
    6

    Unanswered: db2 triggers with cursor declarations!

    Dear Sir, I am desperate to know why this cursor declaration fails within trigger defination:

    CREATE TRIGGER fgg_list_iu after insert on fgt_list REFERENCING NEW AS new FOR EACH ROW MODE DB2SQL
    BEGIN ATOMIC
    declare C1 CURSOR FOR SELECT DEPTNO, DEPTNAME, MGRNO FROM DEPT WHERE ADMRDEPT = 1;
    if (new.security not in ('0', '1')) then SIGNAL SQLSTATE '75000' ('TEST'); end if;
    END

    The result displayed is as follows:
    DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned:
    SQL0104N An unexpected token "FOR" was found following "C declare C1 CURSOR". Expected tokens may include: "<SQL_variable_declarations>". LINE NUMBER=3. SQLSTATE=42601

    -- probably FOR ROW statement clashing with C1 CURSOR FOR....
    Thks in advance!

  2. #2
    Join Date
    May 2002
    Posts
    3

    Question

    Hi,

    I am new to DB2-UDB Programming and would really appreciate if someone could tell me how to fetch the first record from a table.

    I have been trying with

    SELECT FirstName
    FROM Employee
    WHERE LastName = 'Travis'
    FETCH FIRST ROW ONLY;

    This does not seem to work.
    Please help me.

    Thanks in advance,
    Vidya.

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Do you get any error message??? ...

    Or it could be becuase of the case mismatch ... Did you try using 'TRAVIS' instead of 'Travis'

    Cheers

    Sathyaram

    Originally posted by svsvidya
    Hi,

    I am new to DB2-UDB Programming and would really appreciate if someone could tell me how to fetch the first record from a table.

    I have been trying with

    SELECT FirstName
    FROM Employee
    WHERE LastName = 'Travis'
    FETCH FIRST ROW ONLY;

    This does not seem to work.
    Please help me.

    Thanks in advance,
    Vidya.

  4. #4
    Join Date
    May 2002
    Posts
    3
    Hi Sathyaram,

    Actually I want to implement this in a stored procedure. I will give you the entire stored procedure. Please let me know if I have made any mistake. DB2 cribs when I do a BUILD.

    CREATE PROCEDURE TEST (IN mCATNO VARCHAR(10) )
    LANGUAGE SQL MODIFIES SQL DATA

    BEGIN

    SELECT intSR DECIMAL;

    SET intSR = (select STARTROW
    from TOTALS_RANGES
    where CATNO = mCATNO
    ORDER BY LEVEL_R DESC
    FETCH FIRST ROW ONLY);

    END

    Here, I want to fetch the value in column STARTROW (a data type = decimal column) from the first row of table TOTALS_RANGES.

    Do you have any idea why I am not able to run this stored procedure successfully? Please let me know.

    Thanks.
    Vidya.



    Originally posted by sathyaram_s
    Do you get any error message??? ...

    Or it could be becuase of the case mismatch ... Did you try using 'TRAVIS' instead of 'Travis'

    Cheers

    Sathyaram


  5. #5
    Join Date
    Apr 2002
    Posts
    18
    FETCH FIRST x ROWS ONLY is fairly new in terms of DB2. I believe it didn't come in until Version 7. Check it is supported by your version.

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Vidya,

    I belive that FETCH FIRST n ROWS is not permitted in a sub-query.

    I think the following CREATE PROCEDURE statement should work :


    CREATE PROCEDURE TEST (IN mCATNO varchar(10))
    LANGUAGE SQL MODIFIES SQL DATA

    BEGIN
    DECLARE intSR INTEGER ;

    SET intSR = (select startrow from (select rownumber() over (order by level_r desc) rownum,startrow

    from totals_ranges where catno=mCATNO) as a where rownum=1) ;
    END @

    Cheers

    Sathyaram



    Originally posted by svsvidya
    Hi Sathyaram,

    Actually I want to implement this in a stored procedure. I will give you the entire stored procedure. Please let me know if I have made any mistake. DB2 cribs when I do a BUILD.

    CREATE PROCEDURE TEST (IN mCATNO VARCHAR(10) )
    LANGUAGE SQL MODIFIES SQL DATA

    BEGIN

    SELECT intSR DECIMAL;

    SET intSR = (select STARTROW
    from TOTALS_RANGES
    where CATNO = mCATNO
    ORDER BY LEVEL_R DESC
    FETCH FIRST ROW ONLY);

    END

    Here, I want to fetch the value in column STARTROW (a data type = decimal column) from the first row of table TOTALS_RANGES.

    Do you have any idea why I am not able to run this stored procedure successfully? Please let me know.

    Thanks.
    Vidya.




  7. #7
    Join Date
    Mar 2002
    Posts
    17

  8. #8
    Join Date
    Mar 2002
    Posts
    34

    fetch first rows/ stored proc example

    select name
    from siebel.s_org_ext
    where cust_stat_cd = 'Inactive'
    fetch first row only

    select name
    from siebel.s_org_ext
    where cust_stat_cd = 'Inactive'
    fetch first 5 row only
    Attached Files Attached Files

  9. #9
    Join Date
    May 2002
    Posts
    3

    Fetch First Row

    Hi All,


    Thanks for all the help. I was able to do it without using a GROUP BY CLAUSE. Here is how.

    DECLARE mSalary DECIMAL;

    DECLARE curFIRST CURSOR FOR
    SELECT Salary
    FROM Employee_Table
    WHERE FirstName = 'Travis'
    FETCH FIRST ROW ONLY;

    Open curFIRST;
    Fetch Salary INTO mSalary;
    Close curFIRST;

    Cheers,
    Vidya.

Posting Permissions

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