A colleague of mine has experienced a problem when running the SQL below (on a TRU64 box running ORACLE 8.0.5.0.0) (the set values are being set to NULLs. However, the update has been performed correctly on a box running 8.0.5.1.0. Does anyone know where I can find some information on what fixes the patch provides?......or has anyone experienced similar problems? Thanks.
SQLWKS> update
2> (select
3> er.capture_date capture_date, er.moc_id, er.index_id index_id,
4> er.last_date last_date,
5> peru.capture_date source_capture_date, peru.moc_id source_moc_id,
6> peru.index_id source_index_id, sysdate source_last_date
7> from external_ref er, prof_external_ref_update peru
8> where er.external_key = peru.external_key and
9> er.external_key_type = peru.external_key_type and
10> er.data_source_id = peru.data_source_id)
11> set moc_id = source_moc_id, index_id = source_index_id,
12> capture_date = source_capture_date, last_date = source_last_date
13>
1 row processed.
Here is a complete sequence showing the table before, during, & after update.........
I am trying to use a view in an update statement to modify a table. I thought this was working with other tables but when I run it with the example below, it updates the master table to NULLs rather than the corresponding values from the update table.
Before Update:
SQLWKS> select * from external_ref
2>
MOC_ID INDEX_ID EXTERNAL_KEY DATA_SOURCE_ID EXTERNAL_KEY_TYP FIRST_DATE CAPTURE_DATE LAST_DATE
---------------- ---------------- -------------------------------------------------------------------------------- ---------------- ---------------- -------------------- -------------------- --------------------
P2W1J2PSX00E01 P2V1CUXNE1XX01 OSL 01D01 28-DEC-99 15-DEC-99 28-DEC-99
Result of query:
SQLWKS> (select
2> er.capture_date capture_date, er.moc_id, er.index_id index_id,
3> er.last_date last_date,
4> peru.capture_date source_capture_date, peru.moc_id source_moc_id,
5> peru.index_id source_index_id, sysdate source_last_date
6> from external_ref er, prof_external_ref_update peru
7> where er.external_key = peru.external_key and
8> er.external_key_type = peru.external_key_type and
9> er.data_source_id = peru.data_source_id)
10>
CAPTURE_DATE MOC_ID INDEX_ID LAST_DATE SOURCE_CAPTURE_DATE SOURCE_MOC_ID SOURCE_INDEX_ID SOURCE_LAST_DATE
-------------------- ---------------- ---------------- -------------------- -------------------- ---------------- ---------------- --------------------
15-DEC-99 P2W1J2PSX00E01 28-DEC-99 16-DEC-99 P2W1J2PSX00E01 28-DEC-99
Update Execution:
SQLWKS> update
2> (select
3> er.capture_date capture_date, er.moc_id, er.index_id index_id,
4> er.last_date last_date,
5> peru.capture_date source_capture_date, peru.moc_id source_moc_id,
6> peru.index_id source_index_id, sysdate source_last_date
7> from external_ref er, prof_external_ref_update peru
8> where er.external_key = peru.external_key and
9> er.external_key_type = peru.external_key_type and
10> er.data_source_id = peru.data_source_id)
11> set moc_id = source_moc_id, index_id = source_index_id,
12> capture_date = source_capture_date, last_date = source_last_date
13>
1 row processed.
After Update:
SQLWKS> select * from external_ref
2>
MOC_ID INDEX_ID EXTERNAL_KEY DATA_SOURCE_ID EXTERNAL_KEY_TYP FIRST_DATE CAPTURE_DATE LAST_DATE
---------------- ---------------- -------------------------------------------------------------------------------- ---------------- ---------------- -------------------- -------------------- --------------------
P2V1CUXNE1XX01 OSL 01D01 28-DEC-99 28-DEC-99
Doug Yates
Acxiom Corp.
dyates_at_acxiom.com
501-342-3292
"Only two things are infinite, the universe and human stupidity,
and I'm not sure about the former." - Albert Einstein
--------------------------------------------------------------------------
Before we have the paperless office, we have the paperless toilet!
Received on Mon Jan 03 2000 - 19:33:53 NZDT