pb6与pb9的sql解析各不同

//数据窗口中的sql
  SELECT *
    FROM inp_reg_rcpt_no_rec 
   WHERE (( inp_reg_rcpt_no_rec.CASHER_NO = :as_operator ) OR
     ( inp_reg_rcpt_no_rec.WIN_NO = :as_win_no )) AND 
         ( inp_reg_rcpt_no_rec.USING_STATUS <> '停用' )  
ORDER BY inp_reg_rcpt_no_rec.PROVIDE_DATE DESC  

 

//pb9的解析
SELECT *
    FROM inp_reg_rcpt_no_rec 
   WHERE ( ( inp_reg_rcpt_no_rec.CASHER_NO = :as_operator ) OR
     ( inp_reg_rcpt_no_rec.WIN_NO IS NULL ) ) AND 
         ( inp_reg_rcpt_no_rec.USING_STATUS <> '停用' )  
ORDER BY inp_reg_rcpt_no_rec.PROVIDE_DATE DESC;

:as_operator = '0180'

 

//pb6的解析
SELECT *
    FROM inp_reg_rcpt_no_rec 
   WHERE ( ( inp_reg_rcpt_no_rec.CASHER_NO = :as_operator ) OR

                ( inp_reg_rcpt_no_rec.WIN_NO = :as_win_no ) ) AND  
                 ( inp_reg_rcpt_no_rec.USING_STATUS <> '停用' )  
  ORDER BY inp_reg_rcpt_no_rec.PROVIDE_DATE DESC;

:as_operator = '0180'
:as_win_no = <NULL>

 

显而易见，出现的问题：当win_no的; 值为空时，pb9的解析包括了这部分结果集，而pb6的解析括了这部分结果集，为了清晰明确sql和所提的数据，将sql语句改为：

 

SELECT *
    FROM inp_reg_rcpt_no_rec 
   WHERE

  ( ( inp_reg_rcpt_no_rec.CASHER_NO =  nvl (:as_operator,inp_reg_rcpt_no_rec.CASHER_NO) ) OR

 ( inp_reg_rcpt_no_rec.WIN_NO = nvl(:as_win_no,inp_reg_rcpt_no_rec.WIN_NO ) ) ) AND  
                 ( inp_reg_rcpt_no_rec.USING_STATUS <> '停用' )  
  ORDER BY inp_reg_rcpt_no_rec.PROVIDE_DATE DESC;

:as_operator = '0180'
:as_win_no = <NULL>