Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2007
    Posts
    2

    Unanswered: Error on Stored Procedure

    Hi,

    I'm getting the following error for the code below. Can someone help me with this?

    ADMINISTRATOR.GetHoursForAppStatus: 18: [IBM][CLI Driver][DB2/NT] SQL0206N "APP.CREATEDATE" is not valid in the context where it is used. LINE NUMBER=18. SQLSTATE=42703

    Code:
    CREATE PROCEDURE ADMINISTRATOR.GetHoursForAppStatus (  )
    ------------------------------------------------------------------------
    -- SQL Stored Procedure
    ------------------------------------------------------------------------
    P1: BEGIN
    
        DECLARE v_appid Char(10);
        DECLARE v_appdescription varchar(255);
        DECLARE v_createdate date;
        DECLARE v_openclosed varchar(20);
        DECLARE v_statusid integer;
        DECLARE v_enterdatetime date;
        DECLARE v_exitdatetime date;
        DECLARE v_duration bigint;
        DECLARE v_p1 varchar(50);
        DECLARE v_p2 varchar(50);
    
        DECLARE c1 CURSOR FOR
            SELECT app.appid, app.appdescription, app.createdate, app.openclosed, appSt.statusid, appSt.enterdatetime, appSt.exitdatetime
            FROM emerchapplication app
            left join eMerchApplicationStatus appSt on appSt.appId = app.appId
            WHERE openclosed = 'Open'
            ORDER BY appid;
    
        declare global temporary table Temp1
         (appid char(10), appdescription  varchar(255), createdate date, openclosed varchar(20), statusid integer,
         statusduration varchar (20), v_indicator varchar(10))
         on commit preserve rows not logged ;
    
        OPEN c1;
        fetch_loop:
        LOOP
            FETCH c1 INTO
                    v_appid, v_appdescription, v_createdate, v_openclosed, v_statusid, v_enterdatetime, v_exitdatetime;
    
                     SET v_p1 = cast(year(v_enterdatetime) as char) || '-' || cast(month(v_enterdatetime) as char) || '-' || cast((DAY(v_enterdatetime) + 1) as char) || ' 00:00:00';
    	         SET v_p2 = cast(year(v_exitdatetime) as char) || '-' || cast(MONTH(v_exitdatetime) as char) || '-' || cast(DAY(v_exitdatetime) as char) || ' 00:00:00';
    
    	IF (DAY(v_enterdatetime) = DAY(v_exitdatetime) and MONTH(v_enterdatetime) = MONTH(v_exitdatetime) and YEAR(v_enterdatetime) = YEAR(v_exitdatetime)) THEN
    		SET v_duration = 0;
    	ELSE
    	--	SET v_duration = SELECT SUM(nc.workingHours) AS tempDuration from NABCalendar nc where nc.calDate>cast(v_p1 as date) and nc.calDate<cast(v_p2 as date);
    		SET v_duration = (SELECT SUM(nc.workingHours) AS tempDuration from NABCalendar nc where nc.calDate>cast(v_p1 as date) and nc.calDate<cast(v_p2 as date));
    	END IF;
    
            insert into SESSION.Temp1 (appid, appdescription, createdate, openclosed, statusid, statusduration, v_indicator)
            values(v_appid, v_appdescription, v_createdate, v_openclosed, v_statusid, v_duration, v_indicator);
        END LOOP fetch_loop;
        CLOSE c1;
    
        DECLARE SP_SQL1 CURSOR WITH RETURN FOR
        SELECT * FROM SESSION.TEMP1;
        OPEN SP_SQL1;
    
    END P1

  2. #2
    Join Date
    Dec 2007
    Posts
    2
    P.S. We are using Win Server 2003 Enterprise, DB2 UDB 8.1 Enterprise Fix Pack 15

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Which columns has your table named EMERCHAPPLICATION? If there is no column CREATEDATE, then the error is obvious.

    Did you check all the reasons given in the long explanation for SQL0206 already?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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