CODEWITHSUNDEEP
sqloracle-database
Bob
Bob

Reputation: 83

oracle update table field with a defined function doesn't work

I'm perplexed. I need to update a table with 'fips' information based on the 'zip' code. I created the following oracle function:

create or replace function fnc_get_rebid_by_zip
(
  p_zip in varchar2  
) return number as 
returnit number;
begin
      select c.id into returnit from zip_to_fips_map zfm
      left join sales_regions sr on zfm.county_fips = sr.fips
      left join customers c on sr.ind_user_id = c.das_emplid
      where zfm.zip_code = substr(p_ZIP,1,5);
      RETURN returnit;

      EXCEPTION
      WHEN NO_DATA_FOUND
          THEN return 0;
      WHEN OTHERS
          THEN return 0;

end fnc_get_rebid_by_zip;

I ran the following update sql statement.

update customers c
set rep_id = fnc_get_rebid_by_zip(c.zip)

It doesn't update the rep_id. Shouldn't it?

The function itself appears to be working properly. I run:

select customers.id, fnc_get_rebid_by_zip(zip) newrepid, 
    customers.rep_id, customers.zip from customers

And I get the following. I believe the 'EXCEPTION NO_DATA...' should return 0 if not found and it appears so.

        ID   NEWREPID     REP_ID ZIP      
---------- ---------- ---------- ----------
       100        134          0 46240      
       102        134          0 46250      
       110        135          0 52325      
       113          0          0 12365      
       140          0          0 11111      
       141          0          0 99999      
       109        127          0 12345      
       105        127          0 12345-1234 
       106        134          0 46256      
       138          0          0 12312      
       111        134          0 46038      
       112        134          0 46235      
       118        127          0 12345      
       107          0          0 12314      
       115        127          0 12345      
       117        134          0 46240-1226 
       119          0          0 12365      
       139          0          0 12352      
       114          0          0 12312      
       142        134          0 46038      
       116          0          0 88888

For posterity: the following script did the trick:

merge into customers c
using (select zfm.zip_code, c.id client_id
         from zip_to_fips_map zfm
         left join sales_regions sr on zfm.county_fips = sr.fips
         left join customers c on sr.ind_user_id = c.das_emplid) upd
  on (upd.zip_code = substr(c.zip,1,5))
when matched then update
set c.rep_id = upd.client_id;

Upvotes: 1

Views: 110

Answers (2)

Dmitriy
Dmitriy

Reputation: 5565

Do you really need function? You can use MERGE statment for this update operation. It's much better way:

merge customers c
using (select zfm.zip_code, c.id client_id
         from zip_to_fips_map zfm
         left join sales_regions sr on zfm.county_fips = sr.fips
         left join customers c on sr.ind_user_id = c.das_emplid) upd
  on (zfm.zip_code = c.zip)
when matched then update
set c.rep_id = upd.client_id;

I'm not sure that this is exact query that you need, but you can change it.

Also you should make query like this:

select *
  from customers c full join
      (select zfm.zip_code, c.id client_id
         from zip_to_fips_map zfm
              left join sales_regions sr on zfm.county_fips = sr.fips
              left join customers c on sr.ind_user_id = c.das_emplid) upd
      on (zfm.zip_code = c.zip)

to define why no rows was updated. May be your query in function returns 0 rows, or it returns the same values that you already have.

Upvotes: 1

Multisync
Multisync

Reputation: 8797

Select in the function may return NULL. Maybe you need something like this:

  select nvl(c.id, 0) into returnit from zip_to_fips_map zfm
  left join sales_regions sr on zfm.county_fips = sr.fips
  left join customers c on sr.ind_user_id = c.das_emplid
  where zfm.zip_code = substr(p_ZIP,1,5);
  RETURN returnit;

Upvotes: 0

Related Questions