CODEWITHSUNDEEP
oracle-databasevariablesfor-loopselectplsql
Jswq
Jswq

Reputation: 776

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

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);

Upvotes: 0

Views: 2114

Answers (2)

Frank Ockenfuss
Frank Ockenfuss

Reputation: 2043

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.

Upvotes: 2

J. Chomel
J. Chomel

Reputation: 8395

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)

Upvotes: 0

Related Questions