PL/SQL variable problem (newbie!!!) 1

[SteveBurch]

&nbsp;&nbsp;&nbsp;Hi,<br><br>&nbsp;&nbsp;&nbsp;I'm a pl/sql newbie hitting a problem with a declared variable. Hopefully I'm doing something stupid and someone will be able to point it out. The variable in question from the code below is &quot;total_mrp&quot; - basically, as soon as I refer to it within the code(adding it to another field) it seems to 'disappear' - I've put displays in and until this line it shows up ok (as zero), but after the display brings nothing back, but the code loops the whole way through the cursor, so it's obviously going through the line but nothing is happening and it's not falling over either.<br>&nbsp;&nbsp;&nbsp;If anyone has any ideas, or if I canm give any further info, please let me know.<br>&nbsp;&nbsp;&nbsp;Thanks in advance,<br><br>&nbsp;&nbsp;&nbsp;Steve Burch<br><br><br>begin<br>&nbsp;&nbsp;declare cursor c_shortfall is<br>&nbsp;&nbsp;select part_nbr, mrp_qty, (1 - (mrp_qty / demand_qty )) * 100<br>Shortfall<br>&nbsp;&nbsp;from shortfall<br>&nbsp;where demand_qty != 0<br>&nbsp;order by Shortfall desc<br>&nbsp;&nbsp;&nbsp;for update;<br>&nbsp;&nbsp;shortfall_rec c_shortfall%rowtype;<br>&nbsp;&nbsp;total_mrp NUMBER (13,4) := 0;<br>begin<br>&nbsp;&nbsp;open c_shortfall;<br>&nbsp;&nbsp;loop<br>&nbsp;&nbsp;&nbsp;&nbsp;fetch c_shortfall into shortfall_rec;<br>&nbsp;&nbsp;&nbsp;&nbsp;exit when c_shortfall%notfound;<br>&nbsp;&nbsp;&nbsp;&nbsp;total_mrp:= total_mrp + shortfall_rec.mrp_qty;<br>&nbsp;&nbsp;&nbsp;&nbsp;update shortfall set mrp_tot_qty = total_mrp<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;where current of c_shortfall;<br>&nbsp;&nbsp;end loop;<br>&nbsp;&nbsp;close c_shortfall;<br>end;<br>end;

 

[CRoberts]

Looks like the classic hidden NULL value problem.&nbsp;&nbsp;I do not believe you declared variable is at fault.&nbsp;&nbsp;The problem, however, maybe that Mrp_qty has NULL entries.&nbsp;&nbsp;To eliminate this possibly, use NVL(Mrp_Qty, 0) in your query to exchange any NULL entries with a zero.&nbsp;&nbsp;NULLs can create weird results sometimes.&nbsp;&nbsp;See if that does not clear up your problem.<br><br>The same problem may arise in your WHERE clause.&nbsp;&nbsp;If any NULL values exist in demand_qty, then demand_qty != 0 will never return these rows.&nbsp;&nbsp;Again, you must use an NVL function or a IS NULL phrase.&nbsp;&nbsp;Hope this helps.<br>&nbsp;<br>&nbsp;

 

[SteveBurch]

<br>&nbsp;&nbsp;&nbsp;Thanks for that - it works great now, and that's something I'll need to look out for in the future, amongst many other potential pitfalls as well, I presume!<br><br>&nbsp;&nbsp;&nbsp;Steve Burch (now a wiser two day old pl/sql programmer)

 

[tmh]

Can anyone give me an example of the script to use with the NVL function? I am having trouble reading in data from a csv file when a row of the data contains a null value. It is skipping the entire row. If I can force the nulls to 0, I think it would import the data into my table... I am actually getting the data out of the file, but it is not importing it into the access database.

Thanks!