Re: Update to NULL

"Alan" <alan_at_erols.com> wrote in message news:<2hhcboFcln4hU1_at_uni-berlin.de>...
> "Turkbear" <john.g_at_dot.spamfree.com> wrote in message
> news:rnt6b0h3jvk4biau5em8642f11o9dg5bjt_at_4ax.com...
> > "Alan" <alan_at_erols.com> wrote:
> >
> > >I haven't run into this one before, and I am stumped (or having a bad
> day).
> > >I am trying to update one column in a table across a dblink to a value in
> > >one other table. The column is constrained to NOT NULL.
> > >
> > >Using Oracle 8.1.7.4 on Windows 2000 SP4.
> > >
> > >Here is the code:
> > >
> > >CREATE TABLE SMALLGROUP.AGENT_SALES_REP_LINK
> > >(
> > > SALES_REP_NBR VARCHAR2(2) NULL,
> > > RSO_ASSOC VARCHAR2(3) NULL,
> > > AGENCY_CODE VARCHAR2(6) NULL
> > >)
> > >;
> > >
> > >CREATE TABLE EXTRANET.CLIENT_LINK_RSL
> > >(
> > > TIN VARCHAR2(11) NOT NULL,
> > > USER_ID VARCHAR2(50) NOT NULL,
> > > RSL_IDENTIFIER VARCHAR2(17) NOT NULL,
> > > USER_TYPE VARCHAR2(3) NOT NULL,
> > > RSO VARCHAR2(25) NOT NULL,
> > > MGA_CODE VARCHAR2(10) NULL,
> > > GA_CODE VARCHAR2(10) NULL,
> > > REP VARCHAR2(2) NULL
> > >)
> > >;
> > >
> > >There are no Unique, PK or FK constarints created yet.
> > >
> > >
> > >SQL> UPDATE client_link_rsl_at_extranet cl
> > > 2 SET rso =
> > > 3 (
> > > 4 SELECT NVL(rso_assoc, 'UNK' )
> > > 5 FROM agent_sales_rep_link asrl
> > > 6 WHERE asrl.agency_code = cl.rsl_identifier
> > > 7 AND ROWNUM = 1
> > > 8 )
> > > 9 ;
> > >UPDATE client_link_rsl_at_extranet cl
> > >*
> > >ERROR at line 1:
> > >ORA-01407: cannot update ("EXTRANET"."CLIENT_LINK_RSL"."RSO") to NULL
> > >ORA-02063: preceding line from EXTRANET
> > >
> > >I also tried adding code so that the only the rows that have an rso_assoc
> > >value would be used in the update. Same results:
> > >
> > >SQL> UPDATE client_link_rsl_at_extranet cl
> > > 2 SET rso =
> > > 3 (
> > > 4 SELECT NVL(rso_assoc, 'UNK')
> > > 5 FROM agent_sales_rep_link asrl
> > > 6 WHERE agency_code = rsl_identifier
> > > 7 AND rep IS NOT NULL
> > > 8 AND rso IS NOT NULL
> > > 9 AND ROWNUM = 1
> > > 10 AND rsl_identifier IN
> > > 11 (
> > > 12 SELECT agency_code
> > > 13 FROM agent_sales_rep_link
> > > 14 WHERE rso_assoc IS NOT NULL
> > > 15 )
> > > 16 )
> > > 17 ;
> > >UPDATE client_link_rsl_at_extranet cl
> > >*
> > >ERROR at line 1:
> > >ORA-01407: cannot update ("EXTRANET"."CLIENT_LINK_RSL"."RSO") to NULL
> > >ORA-02063: preceding line from EXTRANET
> > >
> > >
> > >
> > >
> > >
> >
> > What output do you get when you run:
> > --------------------------------------------------
> > SELECT NVL(rso_assoc, 'UNK' )
> > FROM agent_sales_rep_link asrl,client_link_rsl_at_extranet cl
> > WHERE asrl.agency_code = cl.rsl_identifier
> > AND ROWNUM = 1
> > -------------------------------------------------------
> >
> >
>
>
> Tried that already. I do get one row, as expected, with a real value:
>
> 1 SELECT NVL(rso_assoc, 'UNK' )
> 2 FROM agent_sales_rep_link asrl,client_link_rsl_at_extranet cl
> 3 WHERE asrl.agency_code = cl.rsl_identifier
> 4* AND ROWNUM = 1
> 5 /
>
> NVL
> ---
> WAS
Here is my guess. There is no where clause condition on the update so when a row in client_link_rsl does not have a matching enty in agent_sales_rep_link no rows is returned. You have an empty cursor so the row level function nvl has no row to act upon resulting in an attemp to update the not null column rso to the result of an empty cursor, null. Add an 'exists' requirement to the update:

update
set rso = (select ... )
where exists (select ...)

HTH -- Mark D Powell -- Received on Tue May 25 2004 - 20:18:50 CDT