Results 1 to 1 of 1
  1. #1
    Join Date
    Jun 2003
    Location
    Chennai
    Posts
    26

    Thumbs up Unanswered: as400 stored procedure probs.

    hi all,

    I have a problem in executing stored procedure in as400. I could able to compile and run in linx v8.1 environment. I could be able to compile in as400 but i could not able to view the output in as400 v5r1.

    I have added the text below where the select query selected rows from the client centre , and if the select stmt. is included in the stored procedure shows zero records.

    I have compiled and executed the as400 stored procedure from the client(linux db2v8.1) which is actually connected to as400 v5r1.
    ----------------------------------------------------------------------------------

    db2 -td@ -vf proc_mesa
    CREATE PROCEDURE ecapsebbs.relmaintsl(in datecriteria date)
    language sql
    BEGIN
    DECLARE v_stmt VARCHAR (2500);
    DECLARE C1 CURSOR WITH RETURN FOR S1;
    SET v_stmt= 'select relmaint.relno,businessdate,fieldname,deptcode,use rgrp,rel.salutationcode,rel.firstname,rel.middlena me,rel.lastname,relmaint.newvalue, relmaint.oldvalue, OldCity.name as OldCityName,NewCity.name as NewCityName, OldCntry.name as OldCntryName, NewCntry.name as NewCntryName from ecapsebbs.relmaint as relmaint left outer join ecapsebbs.rel as rel on (rel.relationshipno = relmaint.relno) left outer join ecapsebbs.reladdr as reladdr on (reladdr.relationshipno = relmaint.relno and reladdr.addtypecode=''CAI'') left outer join ecapsebbs.city as OldCity on (relmaint.oldvalue = OldCity.citycode) left outer join ecapsebbs.city as NewCity on (relmaint.Newvalue = NewCity.citycode) left outer join ecapsebbs.cntry as OldCntry on (relmaint.oldvalue = OldCntry.COUNTRYCODE) left outer join ecapsebbs.cntry as NewCntry on (relmaint.Newvalue = NewCntry.COUNTRYCODE) where relmaint.businessdate > ?';
    PREPARE S1 FROM v_stmt;
    OPEN C1 USING DateCriteria;
    END
    DB20000I The SQL command completed successfully.

    db2 "call ecapsebbs.relmaintsl('2003-08-11'
    )"

    Return Status = 0

    DIRECT SELECT QUERY
    --------------------------
    db2 -vtf 1
    select relmaint.relno,businessdate,fieldname,deptcode,use rgrp,rel.salutationcode,rel.firstname,rel.middlena me,rel.lastname,relmaint.newvalue, relmaint.oldvalue, OldCity.name as OldCityName,NewCity.name as NewCityName, OldCntry.name as OldCntryName, NewCntry.name as NewCntryName from ecapsebbs.relmaint as relmaint left outer join ecapsebbs.rel as rel on (rel.relationshipno = relmaint.relno) left outer join ecapsebbs.reladdr as reladdr on (reladdr.relationshipno = relmaint.relno and reladdr.addtypecode='CAI') left outer join ecapsebbs.city as OldCity on (relmaint.oldvalue = OldCity.citycode) left outer join ecapsebbs.city as NewCity on (relmaint.Newvalue = NewCity.citycode) left outer join ecapsebbs.cntry as OldCntry on (relmaint.oldvalue = OldCntry.COUNTRYCODE) left outer join ecapsebbs.cntry as NewCntry on (relmaint.Newvalue = NewCntry.COUNTRYCODE) where relmaint.businessdate >'2003-08-11'

    output
    ---------

    51 record(s) selected.
    Last edited by msundaram; 11-15-03 at 10:46.
    MS

Posting Permissions

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