Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Posts
    107

    Unanswered: can variable name be same as column name?

    Hi Friends,

    I am using Db2v8.1.
    Will DB2 allow me to declare a variable name same as the column name? I tried it. It gets successfully created. Here it is:
    ------------------------------------------------------------------
    CREATE PROCEDURE SIMPLEFOR(OUT dept SMALLINT)
    LANGUAGE SQL
    BEGIN
    FOR v2 AS
    SELECT dept FROM staff
    DO
    SET dept = dept + dept;
    END FOR ;
    END
    @
    ------------------------------------------------------------------

    If I used the column name inside the for loop, how will DB2 differentiate between the column name and the variable name?
    I expect some error messages to avoid these kind of conflicts. Please let me know if i am not correct.

    Thanks & Regards,
    Jake

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Jake,
    I believe that it is a scope issue. The "dept" inside the FOR loop is not the same ase the "dept" parameter. The columns defined in the select of the FOR loop are only valid within the FOR loop.

    HTH

    Andy

  3. #3
    Join Date
    Feb 2004
    Posts
    107
    Hi Andy,

    Thanks for your help. You are correct. It's the scoping which comes into play if there is ambiguity.

    If the parameter name is accidently same as the column name, then it will generate unexcepted result.
    So the lesson is
    "Never use the parameter name that is same as the column name."

    Once again thanks for solving this.

    With Regards,
    Jake

    Originally posted by ARWinner
    Jake,
    I believe that it is a scope issue. The "dept" inside the FOR loop is not the same ase the "dept" parameter. The columns defined in the select of the FOR loop are only valid within the FOR loop.

    HTH

    Andy

  4. #4
    Join Date
    Dec 2002
    Posts
    134

    Re: can variable name be same as column name?

    While it's not a good practice to have identical names and parameters, you can resolve your problem via params/fields qualifiers

    CREATE PROCEDURE SIMPLEFOR(OUT dept SMALLINT)
    LANGUAGE SQL
    BEGIN
    FOR v2 AS
    SELECT dept FROM staff
    DO
    SET SIMPLEFOR.dept = SIMPLEFOR.dept + v2.dept;
    END FOR ;
    END
    @


    If the proc above not just an example, you can re-write it to
    select sum(staff.dept) into SIMPLEFOR.dept from staff

  5. #5
    Join Date
    Feb 2004
    Posts
    107

    Re: can variable name be same as column name?

    Hi Chuzhoi,

    Thanks for spending your valuable time to answer the question. It works fine after I qualified the names, as you mentioned. I wrote this procedure for learning the behaviour of the DB2. Anyway, thanks for the query.

    With Regards,
    Jake

    Originally posted by chuzhoi
    While it's not a good practice to have identical names and parameters, you can resolve your problem via params/fields qualifiers

    CREATE PROCEDURE SIMPLEFOR(OUT dept SMALLINT)
    LANGUAGE SQL
    BEGIN
    FOR v2 AS
    SELECT dept FROM staff
    DO
    SET SIMPLEFOR.dept = SIMPLEFOR.dept + v2.dept;
    END FOR ;
    END
    @


    If the proc above not just an example, you can re-write it to
    select sum(staff.dept) into SIMPLEFOR.dept from staff

Posting Permissions

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