On Tue, 23 Dec 2008 08:54:26 -0800, raj <raj13232.s@GMAIL.COM> wrote:
> MODIFY ora.one COMBINED_FINAL;
> BY state comp ID;
> WHERE state=&state AND comp="&comp" AND ID=&id;
> IF _IORC_ = %SYSRC(_SOK) THEN REPLACE;
>i am able to update if i copy the oracle table in sas dataset and then
>but when i update directly to the oracle table from sas its giving
>errors as oracle table as higher columns than what i am updating.
>ID=. S_ID=. TM_ID=. comp= state=. DWC_ID=. val=. val1=.
>COUNT=. MEAN=. STD_DEVIATION=. SCORE=. P_VA=.
>if i use sql the issue is,if data is not matching then its upating the
>values with missing.
>UPDATE ora.one c
>SET P_value =(SELECT s.P_value FROM COMBINED_FINAL s where c.ID=
>s.id and c.comp=s.compand c.state=s.state) ,
> Z_SCORE=(select s.Z_SCORE from COMBINED_FINAL s where c.ID= s.id
>and c.comp=s.compand c.state=s.state)
>how to update is their any solution to update and retaining the values
>if it is not present by using SAS or Proc sql
SQL is updating all rows of ORA.ONE because there is no WHERE clause in the
UPDATE statement to restrict it. Code something like this (untested) after
the SET clause:
where catx( '|' , id , comp , state ) in
(select catx( '|' , id , comp , state ) from COMBINED_FINAL)
That should work in SAS. Getting the process to run on the Oracle side is
going to be tricky at best.