Link to home
Start Free TrialLog in
Avatar of NiceMan331
NiceMan331

asked on

handling error of null

hi to all

regarding this question
here

 select count(SUP_NO) into sn from sup_tran
         	where 
         	TRAN_DATE=vs.DOC_DATE
          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
         	where 
         	TRAN_DATE=vs.DOC_DATE
          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
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of NiceMan331
NiceMan331

ASKER

ok
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
thanx