plsql - oracle produce different result for two same queries when only change field,value -


i have sql query . when declare variable , run query produce 39 rows when use literal value instead of variable , run query produce 3 rows. how can ?

here query , results variables

declare      fromnode number :=1;     currentestimate number :=0; begin   ts in (select  e.fromnode,e.tonode,e.weight       ts_dijnodeestimate n        inner join ts_edge e on n.id=e.tonode       n.done=0 , e.fromnode=fromnode  , (currentestimate+e.weight)<n.estimate)       loop         dbms_output.put_line(ts.fromnode || ',' || ts.tonode || ',' || ts.weight);       end loop;  end; 

the result is

1,2,1306 1,5,2161 1,6,2661 2,3,919 2,4,629 3,2,919 3,4,435 3,5,1225 3,7,1983 4,2,629 4,3,435 5,3,1225 5,6,1483 5,7,1258 6,5,1483 6,7,1532 6,8,661 7,3,1983 7,5,1258 7,6,1532 7,9,2113 7,12,2161 8,6,661 8,9,1145 8,10,1613 9,7,2113 9,8,1145 9,10,725 9,11,383 9,12,1709 10,8,1613 10,9,725 10,11,338 11,9,383 11,10,338 11,12,2145 12,7,2161 12,9,1709 12,11,2145 

with literal instead of variable:

declare      fromnode number :=1;     currentestimate number :=0; begin   ts in (select  e.fromnode,e.tonode,e.weight       ts_dijnodeestimate n        inner join ts_edge e on n.id=e.tonode       n.done=0 , e.fromnode=1  , (0+e.weight)<n.estimate)       loop         dbms_output.put_line(ts.fromnode || ',' || ts.tonode || ',' || ts.weight);       end loop;  end; 

the result is

1,2,1306 1,5,2161 1,6,2661 

the desired result second result.

you should change name of variable fromnode my_fromnode or that.

in firt query inside e.fromnode=fromnode comparing column itself , returns true.


Comments