pl/sql for loop with select statement using variable make wrong result?

Question

I was using a variable city_no in the select statement, but I found that the result is different between using direct value and the variable city_no even their values is the same one. The output was 377 when I used the direct value '020', and 16 was the output when I used the variable . I don't know why is different or is there any problem with my select statment ? below is the sql code :

    city_no:='020';
    need_kinds := 0;
    for need_item in (select n.rootcode, n.need2018, n.got2018
                        from jq_temp_hy_need_2018 n
                       where n.city_no = city_no
                         and (n.need2018 - n.got2018) > 0) loop
      current_need_map(need_item.rootcode) := need_item.need2018 -
                                              need_item.got2018;
      already_get_map(need_item.rootcode) := need_item.got2018;
      need_kinds:=need_kinds+1;
    end loop;
    -- out put 377
   dbms_output.put_line(need_kinds);

when I changed the condition of select statement to n.city_no = '020' , the output value became 16, actually I know the count(1) is 16 when n.city_no = '020' .

    city_no:='020';
    need_kinds := 0;
    for need_item in (select n.rootcode, n.need2018, n.got2018
                        from jq_temp_hy_need_2018 n
                       where n.city_no = '020'
                         and (n.need2018 - n.got2018) > 0) loop
      current_need_map(need_item.rootcode) := need_item.need2018 -
                                              need_item.got2018;
      already_get_map(need_item.rootcode) := need_item.got2018;
      need_kinds:=need_kinds+1;
    end loop;
    -- out put 16
    dbms_output.put_line(need_kinds);

Frank Ockenfuss · Accepted Answer · 2017-11-09 10:24:27Z

2

The name of your variable is same as column name in your table, therefore the column is used instead of the variable in your select statement.

Rename your variable to something unique to fix the problem.

answered Nov 9, 2017 at 10:24

Frank Ockenfuss

2,04312 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

J. Chomel · Accepted Answer · 2017-11-09 10:08:20Z

0

You may have an issue when value is NULL, or an implicit conversion.

You should try by casting both sides with TO_CHAR, and use NVL:

-- change your where condition to this:
where TO_CHAR( NVL(n.city_no, '#@') ) = TO_CHAR(city_no)

answered Nov 9, 2017 at 10:08

J. Chomel

8,44215 gold badges44 silver badges72 bronze badges

1 Comment

Jswq Over a year ago

it still output 377 when I changed the condition to yours, and the colum city_no type is VARCHAR2(16) and is not null

Collectives™ on Stack Overflow

pl/sql for loop with select statement using variable make wrong result?

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related