Thanks, Mark. Very understandable explanation.
"Mark D Powell" <Mark.Powell_at_eds.com> wrote in message
news:2687bb95.0405251718.32a7622_at_posting.google.com...
> "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 Wed May 26 2004 - 08:30:39 CDT
