Re: Update to NULL

From: Turkbear <john.g_at_dot.spamfree.com>
Date: Tue, 25 May 2004 11:43:14 -0500
Message-ID: <rnt6b0h3jvk4biau5em8642f11o9dg5bjt@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

----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==---- http://www.newsfeed.com The #1 Newsgroup Service in the World! >100,000 Newsgroups ---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =---