• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 335
  • Last Modified:

handling error of null

hi to all

regarding this question

 select count(SUP_NO) into sn from sup_tran
          AND TRAN_NO1=to_char(d)
          AND (LINE_NO=vs.LINE_NO or line_no is null);
         if sn > 0 then
         	select nvl(SUP_NO,0) into sn from h2002.sup_tran
          AND TRAN_NO1=d
          AND (LINE_NO=vs.LINE_NO or line_no is null);
          SELECT initcap(ESUP_NAME) into benf FROM h2002.SUP_FILE  WHERE SUP_NO=NVL(sn,0);
          end if;

Open in new window

the statement fail if there is data
when i disable the 1st condition
the second select return correct data
i think it is because of the parameter d
d should be  varchar2
but why it fail in the 1st statement & success in the 2nd , i dont know
1 Solution
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I will do a "guess" and say: renamed your variables AND use aliases.

should be


so, the variables should have some prefix to identify it's a variable, and all columns should be prefixed with the table name or better alias. I presume that vs is a cursor or the like ...
SELECT count(st.SUP_NO) 
  INTO v_sn 
FROM sup_tran st
          AND st.TRAN_NO1=to_char(v_d)
          AND (st.LINE_NO=vs.LINE_NO or st.line_no is null);
         if v_sn > 0 then           
         	select nvl(SUP_NO,0) 
                  INTO v_sn 
                 FROM h2002.sup_tran st
         	WHERE st.TRAN_DATE=vs.DOC_DATE
                   AND st.TRAN_NO1= v_d
                AND (st.LINE_NO=vs.LINE_NO or st.line_no is null)
                SELECT initcap(ESUP_NAME) 
                      into benf 
              FROM h2002.SUP_FILE  sf 
              WHERE sf-SUP_NO=NVL(v_sn,0);
          end if; 

Open in new window

NiceMan331Author Commented:
it works
i know the mistake
i forgot to use : h2002.  before table name
h2002.sup_trans  instead of sup_tran
any how , please give me chance to try more , if there is no other mistakes , i will accept and close it
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now