Re: Update to NULL

"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