Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2006
    Posts
    13

    Unanswered: Inaccurate results in stored procedure

    I have a very strange problem regarding a section of my stored procedure giving inaccurate results.

    The section of the code in question is:
    Code:
     case reprint_type
      when 'J'
        then
    begin
    select a.modified_by
    into user_id
    from adfapp.job_history a
    where a.job_id = job_id
    and a.state_name = 'Reprint'
    and a.time_stamp = (select max(b.time_stamp) from adfapp.job_history b where b.job_id = job_id and b.state_name = 'Reprint')
    with ur;
    end;
    For example, when this stored procedure is ran with a job_id of 513145, the value user_id is 'jackson'... which I know is the incorrect user id.

    However, when I just run this simple select statement, I get the correct user id:
    Code:
    select a.modified_by
    from adfapp.job_history a
    where a.job_id = 513145
    and a.state_name = 'Reprint'
    and a.time_stamp = (select max(b.time_stamp) from adfapp.job_history b where b.job_id = 513145 and b.state_name = 'Reprint')
    What could be the cause of this?
    The entire stored proc is attached for reference...

    Thanks for your help!
    Attached Files Attached Files

  2. #2
    Join Date
    Jul 2006
    Posts
    13
    I seem to have fixed the problem but I am not sure why...

    all I did was change the variable job_id to a different name, e.g. jobid

    Code:
     
    declare jobid integer;     
    case reprint_type
      when 'J'
        then
    begin
    select a.modified_by
    into user_id
    from adfapp.job_history a
    where a.job_id = jobid
    and a.state_name = 'Reprint'
    and a.time_stamp = (select max(b.time_stamp) from adfapp.job_history b where b.job_id = jobid and b.state_name = 'Reprint')
    with ur;
    end;
    Can someone maybe explain why this happens?
    Does DB2 get confused when a column name and variable have the same name?
    Last edited by Yukke; 10-23-07 at 11:01.

  3. #3
    Join Date
    Apr 2007
    Location
    Chicago
    Posts
    68
    It's all about scope. DB2 resolved the reference of job_id within the context of your SELECT statement. In other words, your predicate was comparing the value of column a.job_id to the value of column job_id, which in your statement will always result in true. You changed the variable name to jobid which is not a column on the table, and now DB2 resolves the reference to your stored proc variable.

  4. #4
    Join Date
    Apr 2007
    Location
    Chicago
    Posts
    68
    One more thought. You should be able to reference the original variable name and distiquish it from the table column name if you label your BEGIN and qualify the reference. I think it's better to use different variable names, but this might be something you'd want to try. For clarity, you could also prefix the stored proc variables and parms with v and p (vJob_Id, pParm1)

    Code:
    lab1: BEGIN
    declare job_id integer;
    
    select a.modified_by
    into user_id
    from adfapp.job_history a
    where a.job_id = lab1.job_id

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    This sounds like a maintenance nightmare. The fact that the OP decided to try changing the name of the variable suggests it should in fact have a different name, not only to make the code work properly, but to make it understandable and maintainable by others.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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