OraFAQ Forum: SQL & PL/SQL » HELP Please!!!!

Home » SQL & PL/SQL » SQL & PL/SQL » HELP Please!!!!

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

HELP Please!!!! [message #186597]

Tue, 08 August 2006 12:36

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

Report message to a moderator

Re: HELP Please!!!! [message #186603 is a reply to message #186597]

Tue, 08 August 2006 12:52

scottwmackey
Messages: 515
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 ', ');;

Report message to a moderator

Re: HELP Please!!!! [message #186616 is a reply to message #186603]

Tue, 08 August 2006 14:49

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

Report message to a moderator

Re: HELP Please!!!! [message #186631 is a reply to message #186616]

Tue, 08 August 2006 15:42

Littlefoot
Messages: 21823
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.

Report message to a moderator

Re: HELP Please!!!! [message #186648 is a reply to message #186631]

Tue, 08 August 2006 18:08

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???

Report message to a moderator

Previous Topic:	Transfer users (merged)
Next Topic:	Outer join query blowing away my temp tablespace

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Dec 03 21:00:56 CST 2024