CODEWITHSUNDEEP
oracle-databaseplsqlsplit
Mustafa
Mustafa

Reputation: 63

Use when parameter is null in plsql

Hi guys I have created a stored procedure. when i use it pacente_no = 5 purun_kod = 2,3,1 and ppolice_no = 6 it is working but when i use pacente_no = 5 and ppolice_no = 6 without purun_kod is not working i mean not error just not turn response. Thanks.

function police_ara(pacente_no      in musteri_rol.musteri_rol_kod%type,
                      purun_kod       in police.urun_kod%type,
                      ppolice_no      in police.police_no%type,) return sys_refcursor is
    vret sys_refcursor;
    v_urun_kod varchar(1000);      
  begin
    v_urun_kod := purun_kod;
  open vret for
      with policeler as
       (select distinct
                        ph.police_hareket_id ph_police_hareket_id,
                        p.urun_kod,
                        (select max(pho.police_hareket_id) from police_hareket_otr pho
            where pho.police_hareket_id = ph.police_hareket_id
           and (pho.durum_kod1 = 0 or pho.durum_kod2 = 0 or pho.durum_kod3 = 0 or pho.durum_kod4 = 0)) pho_police_hareket_id,
                        ph.police_id,

         from police p

         inner join police_hareket ph
            on ph.police_id = p.police_id
           and (ph.ekbelge_no>0 or (select count(1)
                  from police_kotasyon pk
                 where pk.police_hareket_id = ph.police_hareket_id 
                   and pk.kotasyon_seviyesi = 3
                   and rownum = 1)>0)
            on (mr.musteri_rol_id = pa.acente_id)
          left join ekbelge ek
            on (ek.ekbelge_kod = ph.ekbelge_kod)
          join (select regexp_substr(v_urun_kod, '[^,]+', 1, level) urun_kod
                  from dual
                  connect by level <= regexp_count(v_urun_kod, ',') + 1
                 ) x on p.urun_kod = x.urun_kod 
         where   (p.police_no = ppolice_no or ppolice_no is null)
             and (p.pacente_no = pacente_no or pacente_no is null)
           --and (p.urun_kod = purun_kod or purun_kod is null) ////THİS LİNEEE\\\\
      select 
             urun_kod,
             police_no,          
        from policeler
        left join police_musteri pm_sg
          on pm_sg.police_hareket_id = ph_police_hareket_id
         and pm_sg.rol_id = pck_const_rol.sigortali
         and pm_sg.sira_no = 1
        left join musteri m_sg
          on (m_sg.musteri_id = pm_sg.musteri_id)
    return vret;
  end;

Upvotes: 1

Views: 54

Answers (1)

F.Lazarescu
F.Lazarescu

Reputation: 1385

It's because you use that value purun_kod, in the inner join:

 ...
 join (select regexp_substr(v_urun_kod, '[^,]+', 1, level) urun_kod
                  from dual
                  connect by level <= regexp_count(v_urun_kod, ',') + 1
                 ) x on p.urun_kod = x.urun_kod 
 ...

If you don't give a value, your inner join will fail.

UPDATE 1

Try to change that join with the next one:

 ...
 join (select regexp_substr(v_urun_kod, '[^,]+', 1, level) urun_kod
              from dual
              connect by level <= regexp_count(v_urun_kod, ',') + 1
             ) x on p.urun_kod = nvl(x.urun_kod,p.urun_kod)
 ...

I putted a NVL, so that if the column X.URUN_KOD is null, nothing happens.

Upvotes: 1

Related Questions