If q_rec.myfield = null 1

[mts176]

I am trying to write a script that when returned it checks a certain field. If that field is null then I would put an id into a variable. If the field is not null then I would do a "select into" myvariable from mytable where somefield = q_rec.somefield.

declare nextclassID number;
nextgroupID number;
nextroomID number;
newAddedBy number;
newLastEditBy number;
CURSOR q IS 
select class_id, course_id, class_date, room_id, unique_id, webevent_id, start_time, end_time, cancelled, class_recur_id, comments, 
pre_instructions_fac, post_instructions_fac, pre_instructions_part, post_instructions_part, added_by, add_date, last_edited_by, 
last_edit_date, conflict_ignore, class_group_id, new_room_id, new_course_id
from classes@linkwcl, map_courses mcr, map_id0_room mir
 where wcl.course_id = mcr.old_course_id
 and wcl.room_id = mir.old_room_id; 
BEGIN 
 FOR q_rec IN q LOOP 
 
SELECT class_id_seq.NEXTVAL INTO  nextclassID FROM DUAL; 
SELECT class_group_id_seq.NEXTVAL INTO nextgroupID FROM DUAL; 
SELECT CLASS_ROOM_SEQ.NEXTVAL INTO nextroomID FROM DUAL; 

if q_rec.added_by <> null then
    SELECT new_user_id INTO newAddedBy FROM map_users where old_user_id = q_rec.added_by; 
else
   newAddedBy := 366;
end if;

if q_rec.last_edited_by <> null then
   SELECT new_user_id INTO newLastEditBy FROM map_users where old_user_id = q_rec.last_edited_by; 
else
   newLastEditBy := 366;
end if;

I am a big newbie when it comes to oracle and pl/sql.
Any help would be appreciated.

 

[SantaMufasa]

MTS,

You have stumbled onto "The Great Oracle-NULL" debate. Everything becomes easier to understand if you acknowledge that, in Oracle:

1) a NULL is the absence of any value...an unknown value. Oracle stores such a situation in a field whose data bits do not exist...there are no bits...no data stored.

2) the "=" operator checks to see if the bits of the operand to the left of the "=" operator match the bits of the operand to the right of the "=" operator.

To clarify, if, when you see the term NULL, you say the phrase, "an unknown value", then that might help.

For example, let's analyse the most "obvious" of all NULL comparisons: "...IF NULL = NULL". One would think that the answer is "TRUE", but if you rephrase the question to ask, "IS an unknown value equal to an unknown value?", the answer is not "TRUE"; it is not "FALSE"; the answer is, in fact, "UNKNOWN". The only time that boolean "TRUE" code executes is if the result is "TRUE"; "UNKNOW" is not close enough.

Therefore, if there is a possibility that the value of an expression could be "UNKNOWN" (i.e., NULL), then the correct question to ask, in the Oracle World is, "IF <expression> IS NULL" (or conversely "IF <expression> IS NOT NULL".

Let us know if this explanation helps.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[mts176]

I believe you have helped me before SantaMufasa and I am always amazed at the quickness of your responses and your knowledge of the subject matter. Sometimes 1 star just doesn't seem enough. I thank you for that in depth explanation of oracles null.

I tried to use the "IF <expression> IS NOT NULL" for a value in a record set that I know should be empty and it correct 366 was placed in the variable. I also used the same expression for a value in a record set that I know has a value in it and 366 was still placed in the variable.

I probably didn't explain myself clearly on my first attempt. The record sets are 1 of 2 things either has a number in it or has nothing in it. How would I go about using an "If statement" with these 2 known (or sometimes "UNKOWN") pieces of information.

 

[cranebill]

If decode(expression,Null,0,expression) = 0
then variable := whatever
else whatever else
end if;

crude code but will work

 

[SantaMufasa]

Bill, I'm sad to disclose that "DECODE" is not available in PL/SQL (ergo, an "IF" statement). The function that Oracle explicitly built for NULL transformations is "NVL()". It is available in both SQL and PL/SQL:

If NVL(<expression>,0) = 0
then variable := whatever
else whatever else
end if;

MTS, could you please post a copy-and-paste of your subject code ("...I tried to use the "IF <expression> IS NOT NULL"...)? That way we can assist more specifically.


Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[cranebill]

My bad, however nvl would still work in that situation.

 

[mts176]

This is the code that I am trying to use

declare nextclassID number;
nextgroupID number;
nextroomID number;
newAddedBy number;
newLastEditBy number;
CURSOR q IS 
select class_id, course_id, class_date, room_id, unique_id, webevent_id, start_time, end_time, cancelled, class_recur_id, comments, 
pre_instructions_fac, post_instructions_fac, pre_instructions_part, post_instructions_part, added_by, add_date, last_edited_by, 
last_edit_date, conflict_ignore, class_group_id, new_room_id, new_course_id
from classes@wiser wcl, map_courses mcr, map_id0_room mir
 where wcl.course_id = mcr.old_course_id
 and wcl.room_id = mir.old_room_id
 and wcl.class_id = 123789; 
BEGIN 
 FOR q_rec IN q LOOP 
 
SELECT class_id_seq.NEXTVAL INTO  nextclassID FROM DUAL; 
SELECT class_group_id_seq.NEXTVAL INTO nextgroupID FROM DUAL; 
SELECT CLASS_ROOM_SEQ.NEXTVAL INTO nextroomID FROM DUAL; 
IF q_rec.added_by IS NOT NULL then
    SELECT new_user_id INTO newAddedBy FROM map_users where old_user_id = q_rec.added_by; 
else
   newAddedBy := 366;
end if;
IF q_rec.last_edited_by IS NOT NULL then
   SELECT new_user_id INTO newLastEditBy FROM map_users where old_user_id = q_rec.last_edited_by; 
else
   newLastEditBy := 366;
end if;

 

[mts176]

I just tried the "NVL" and got an error message box:

ORA-01403: no data found
ORA-06512: as line 27

 

[mts176]

Thank you all I got it to work. Here is the code I used.

declare nextclassID number;
nextgroupID number;
nextroomID number;
newAddedBy number;
newLastEditBy number;
oldLastEditBy number;
oldAddedBy number;
CURSOR q IS 
select class_id, course_id, class_date, room_id, unique_id, webevent_id, start_time, end_time, cancelled, class_recur_id, comments, 
pre_instructions_fac, post_instructions_fac, pre_instructions_part, post_instructions_part, NVL(added_by, 0) as added_by, add_date, NVL(wcl.last_edited_by, 0) as last_edited_by, 
last_edit_date, conflict_ignore, class_group_id, new_room_id, new_course_id
from classes@wiser wcl, map_courses mcr, map_id0_room mir
 where wcl.course_id = mcr.old_course_id
 and wcl.room_id = mir.old_room_id; 
BEGIN 
 FOR q_rec IN q LOOP 
 
SELECT class_id_seq.NEXTVAL INTO  nextclassID FROM DUAL; 
SELECT class_group_id_seq.NEXTVAL INTO nextgroupID FROM DUAL; 
SELECT CLASS_ROOM_SEQ.NEXTVAL INTO nextroomID FROM DUAL;

oldAddedBy := q_rec.added_by;
oldLastEditBy := q_rec.last_edited_by;

If oldAddedBy = 0 then
   newAddedBy := 366;
else
   SELECT new_user_id INTO newAddedBy FROM map_users where old_user_id = q_rec.added_by; 
end if;
If oldLastEditBy = 0 then
   newLastEditBy := 366; 
else
  SELECT new_user_id INTO newLastEditBy FROM map_users where old_user_id = q_rec.last_edited_by;   
end if;