Home » SQL & PL/SQL » SQL & PL/SQL » HELP Please!!!!
icon8.gif  HELP Please!!!! [message #186597] Tue, 08 August 2006 12:36 Go to next message
aa0609
Messages: 3
Registered: August 2006
Location: IL
Junior Member
Any help, suggestions, etc.... will be greatly appreciated.

I have the following proc that is partially working...I can't figure out why the else is not being applied to those records that fit within the correct date, but have no corresponding value on the reference table. Instead, those records are being updated with ' '. Mad




CREATE OR REPLACE Procedure ME_ASSIGN
(v_TABLE_NAME In VARCHAR2 := NULL
)
AS
sql_stmt varchar2(32767);
where_clause varchar2(32767);
BEGIN

where_clause := ' ' ;

sql_stmt := 'UPDATE ' || v_TABLE_NAME || ' ' ||
' SET ' || v_TABLE_NAME||'.fieldx = (' ||
' Select ' ||
' CASE ' ||
' WHEN ' ||v_TABLE_NAME||'.fieldrp NOT IN (''val1'',''val2'',''val3'',''val4'') '||
' THEN '' '' '||
' WHEN reference_table. fieldf IS NOT NULL '||
' AND '||v_TABLE_NAME||'.yrmo_dt >= ''01-JAN-2005'''|| ' THEN reference_table.fieldf '||
' ELSE ''UNK'' '||
' END '||
' FROM ' || ' reference_table ' ||
' where ' || v_TABLE_NAME ||'.fieldrp = reference_table.fieldrp ' ||
' AND reference_table.sbc_fielducd = '||v_TABLE_NAME ||'.fieldre ' ||
' AND reference_table.fieldactive = ''Y'') ';

Execute immediate (sql_stmt);
commit;


EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('When others exception raised:'||sqlerrm );
RAISE;
END;
/



Thanks in advance
Re: HELP Please!!!! [message #186603 is a reply to message #186597] Tue, 08 August 2006 12:52 Go to previous messageGo to next message
scottwmackey
Messages: 505
Registered: March 2005
Senior Member
This is what the query would look like if it weren't dynamic. I don't have the time to mess with all of formatting but you can get the feeling for what you need to do.
UPDATE v_table_name
SET v_table_name.fieldx = (SELECT CASE
                                       WHEN v_table_name
                                       .fieldrp NOT IN
                                            (', ' val1 ', ', ', ' val2 ', ', ', ' val3 ', ', ', ' val4 ', ') THEN
                                        ', ' ', '
                                       WHEN reference_table. fieldf IS NOT NULL AND v_table_name.yrmo_dt >= ', '
                                        01 - jan - 2005 ', ' THEN
                                        reference_table.fieldf
                                       ELSE
                                        ', ' unk ', '
                                   END
                            FROM reference_table
                            WHERE v_table_name.fieldrp = reference_table.fieldrp
                            AND reference_table.sbc_fielducd = v_table_name.fieldre
                            AND reference_table.fieldactive = ', ' y ', ')
WHERE EXISTS (SELECT *
              FROM reference_table
              WHERE v_table_name.fieldrp = reference_table.fieldrp
              AND reference_table.sbc_fielducd = v_table_name.fieldre
              AND reference_table.fieldactive = ', ' y ', ');;
Re: HELP Please!!!! [message #186616 is a reply to message #186603] Tue, 08 August 2006 14:49 Go to previous messageGo to next message
aa0609
Messages: 3
Registered: August 2006
Location: IL
Junior Member
Thanks I tried it, but it gives me the following error: inserted value too large for column
Re: HELP Please!!!! [message #186631 is a reply to message #186616] Tue, 08 August 2006 15:42 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
So enlarge it!

ALTER TABLE table_name MODIFY column_name DATATYPE (new_size);

such as

ALTER TABLE emp MODIFY ename VARCHAR2(300);

Or, shorten the value you're inserting into the existing column.
Re: HELP Please!!!! [message #186648 is a reply to message #186631] Tue, 08 August 2006 18:08 Go to previous message
aa0609
Messages: 3
Registered: August 2006
Location: IL
Junior Member
Let me try to explain what I am trying to accomplish. I need to update a field of an existing table based on the values of a reference table and if there is no match on the reference table then update field with UNK.
I was doing that in SQL server with no problems by using a case statement and a left outer join. We are migrating to Oracle and my SQL server proc will not work on Oracle if I leave the left outer join I get a “single row subquery returns more than one row” error….If I take the left outer join out and just modify the where clause to where ' || v_TABLE_NAME ||'.fieldrp = reference_table.fieldrp ' ||
' AND reference_table.sbc_fielducd = '||v_TABLE_NAME ||'.fieldre ' ||
' AND reference_table.fieldactive = ''Y'') then I get a ORA-01407: cannot update () to NULL for those rows that don’t have a match on the reference table..

I am not sure how to accomplish this???
Previous Topic: Transfer users (merged)
Next Topic: Outer join query blowing away my temp tablespace
Goto Forum:
  


Current Time: Fri Dec 09 15:43:38 CST 2016

Total time taken to generate the page: 0.23336 seconds