[Search for users]
[Overall Top Noters]
[List of all Conferences]
[Download this site]
| Title: | Oracle Rdb - Still a strategic database for DEC on Alpha AXP! |
| Notice: | RDB_60 is archived, please use RDB_70 .. |
| Moderator: | NOVA::SMITHI SON |
|
| Created: | Fri Mar 18 1994 |
| Last Modified: | Fri May 30 1997 |
| Last Successful Update: | Fri Jun 06 1997 |
| Number of topics: | 5118 |
| Total number of notes: | 28246 |
5032.0. "-RDMS-F-BAD_SYM, %SQL-E-NOCMPBYRES on import of table with computed by columns" by svrav1.au.oracle.com::AMENDOZA () Mon Feb 17 1997 01:37
Hi,
My customer is doing an import of a table containing computed by columns
and is getting the following errors:
..................
IMPORTing table WAREHOUSE_TABLE
%SQL-F-NORELRES, unable to import table WAREHOUSE_TABLE
%RDB-E-NO_META_UPDATE, metadata update failed
-RDB-E-OBSOLETE_METADA, request references metadata objects that no longer exist
-RDMS-F-BAD_SYM, unknown field symbol - SAMPLE_RATE
%RDB-E-OBSOLETE_METADA, request references metadata objects that no longer exist
-RDMS-F-TABNOTDEF, relation WAREHOUSE_TABLE is not defined in database
%SQL-E-NOCMPBYRES, computed field SAMPLE_RATE in relation WAREHOUSE_TABLE not id
%RDB-E-NO_META_UPDATE, metadata update failed
-RDB-E-OBSOLETE_METADA, request references metadata objects that no longer exist
-RDMS-F-TABNOTDEF, relation WAREHOUSE_TABLE is not defined in database
%SQL-E-NOCMPBYRES, computed field FLOW_RATE in relation WAREHOUSE_TABLE not impd
%SYSTEM-S-NORMAL, normal successful completion
%SQL-E-NOCMPBYRES, computed field MDL_FACTOR in relation WAREHOUSE_TABLE not imd
%SYSTEM-S-NORMAL, normal successful completion
The script to reproduce the problem is provided below. The same
behaviour has been observed across all supported Rdb versions
(6.0-16, 6.1-10, 7.0-0).
thanks,
albert.
------------------------------------------------------------------
$!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
$!
$ set verify
$ SQL
create database filename EXPIMPTEST_DB
;
--
create table EXTRACT_CONTROL (
SAMPLING_SITE CHAR(10),
RECORD_TYPE CHAR(10),
CHR_EFFECTIVE_DATE CHAR(8),
AMB_SO2_HOURLY_LIMIT FLOAT,
AMB_SO2_DAILY_LIMIT FLOAT,
AMB_F_DAILY_LIMIT FLOAT,
SOU_F_DAILY_LIMIT FLOAT,
LAST_EXTRACT_DATE DATE VMS,
IMPLEMENTATION_DATE DATE VMS,
MODIFIED_ON DATE VMS,
MODIFIED_BY CHAR(10),
MODIFIABLE CHAR(1),
DAY_SHIFT_HOUR INTEGER,
NIGHT_SHIFT_HOUR INTEGER,
NIGHT_SHIFT_CURR_DAY CHAR(1),
F_LIMIT_VOLTAGE FLOAT,
F_CENTRE_VOLTAGE FLOAT,
MDL_FACTOR FLOAT
);
--
create view EXTRACT_CONTROL_VW (
SAMPLING_SITE,
RECORD_TYPE,
CHR_EFFECTIVE_DATE,
AMB_SO2_HOURLY_LIMIT,
AMB_SO2_DAILY_LIMIT,
AMB_F_DAILY_LIMIT,
SOU_F_DAILY_LIMIT,
LAST_EXTRACT_DATE,
IMPLEMENTATION_DATE,
MODIFIED_ON,
MODIFIED_BY,
MODIFIABLE,
DAY_SHIFT_HOUR,
NIGHT_SHIFT_HOUR,
NIGHT_SHIFT_CURR_DAY,
F_LIMIT_VOLTAGE,
F_CENTRE_VOLTAGE,
MDL_FACTOR,
EFFECTIVE_DATE,
END_DATE,
F_UPPER_LIMIT,
F_LOWER_LIMIT)
as select
SAMPLING_SITE,
RECORD_TYPE,
CHR_EFFECTIVE_DATE,
AMB_SO2_HOURLY_LIMIT,
AMB_SO2_DAILY_LIMIT,
AMB_F_DAILY_LIMIT,
SOU_F_DAILY_LIMIT,
LAST_EXTRACT_DATE,
IMPLEMENTATION_DATE,
MODIFIED_ON,
MODIFIED_BY,
MODIFIABLE,
DAY_SHIFT_HOUR,
NIGHT_SHIFT_HOUR,
NIGHT_SHIFT_CURR_DAY,
F_LIMIT_VOLTAGE,
F_CENTRE_VOLTAGE,
MDL_FACTOR,
--
-- EFFECTIVE_DATE
cast (CHR_EFFECTIVE_DATE as date vms),
--
-- END_DATE
(select cast (cast (cast (C1.CHR_EFFECTIVE_DATE as date vms) as
timestamp) - (interval '0.01' second) as date vms)
from EXTRACT_CONTROL C1
where C1.SAMPLING_SITE = EXTRACT_CONTROL.SAMPLING_SITE
and C1.RECORD_TYPE in ('LIMITS','HISTORY')
and C1.CHR_EFFECTIVE_DATE > EXTRACT_CONTROL.CHR_EFFECTIVE_DATE
order by C1.CHR_EFFECTIVE_DATE
limit to 1 row),
--
-- F_UPPER_LIMIT
F_CENTRE_VOLTAGE + F_LIMIT_VOLTAGE,
--
-- F_LOWER_LIMIT
F_CENTRE_VOLTAGE - F_LIMIT_VOLTAGE
from EXTRACT_CONTROL
;
--
create table DETECTION_LIMITS (
SAMPLING_SITE CHAR(10),
SAMPLING_POINT_DTL CHAR(10),
CATEGORY CHAR(30),
EFFECTIVE_DATE DATE VMS,
SAMPLE_RATE DOUBLE PRECISION,
FLOW_RATE DOUBLE PRECISION,
INSTRUMENT_ID CHAR(10),
END_DATE computed by
(select cast (cast (EFFECTIVE_DATE as timestamp) - (interval '0.01'
second) as date vms)
from DETECTION_LIMITS C1
where C1.SAMPLING_SITE = DETECTION_LIMITS.SAMPLING_SITE
and C1.SAMPLING_POINT_DTL = DETECTION_LIMITS.SAMPLING_POINT_DTL
and C1.CATEGORY = DETECTION_LIMITS.CATEGORY
and C1.EFFECTIVE_DATE > DETECTION_LIMITS.EFFECTIVE_DATE
order by C1.EFFECTIVE_DATE
limit to 1 row)
);
--
create table ENVIRONMENTAL_DATA (
SAMPLE_ID INTEGER,
SAMPLING_SITE CHAR(10),
SAMPLING_POINT_BROAD CHAR(10),
SAMPLING_POINT_DTL CHAR(10),
CATEGORY CHAR(30),
PERIOD CHAR(11),
SAMPLE_DATE DATE VMS,
SAMPLE_HOUR INTEGER,
SAMPLE_RESULT DOUBLE PRECISION,
SAMPLE_STATUS CHAR(1)
);
create table COUNTERS (
COUNTER_NAME VARCHAR(31),
COUNTER_VALUE INTEGER
);
--
create table WAREHOUSE_TABLE (
SAMPLING_SITE CHAR(10),
SAMPLING_POINT_BROAD CHAR(10),
SAMPLING_POINT_DTL CHAR(10),
CATEGORY CHAR(30),
PERIOD CHAR(11),
SAMPLE_DATE DATE VMS,
SAMPLE_HOUR INTEGER,
SAMPLE_RESULT DOUBLE PRECISION,
SAMPLE_MAXIMUM DOUBLE PRECISION,
SAMPLE_COUNT INTEGER,
SAMPLE_ORIGINAL_STATUS CHAR(1),
SAMPLE_RATE computed by
(select SAMPLE_RATE
from DETECTION_LIMITS C1
where C1.SAMPLING_SITE = WAREHOUSE_TABLE.SAMPLING_SITE
and C1.SAMPLING_POINT_DTL = WAREHOUSE_TABLE.SAMPLING_POINT_DTL
and C1.CATEGORY = WAREHOUSE_TABLE.CATEGORY
and C1.EFFECTIVE_DATE <= WAREHOUSE_TABLE.SAMPLE_DATE
and (C1.END_DATE >= WAREHOUSE_TABLE.SAMPLE_DATE or
C1.END_DATE is null
)
),
FLOW_RATE computed by
(select FLOW_RATE
from DETECTION_LIMITS C1
where C1.SAMPLING_SITE = WAREHOUSE_TABLE.SAMPLING_SITE
and C1.SAMPLING_POINT_DTL = WAREHOUSE_TABLE.SAMPLING_POINT_DTL
and C1.CATEGORY = WAREHOUSE_TABLE.CATEGORY
and C1.EFFECTIVE_DATE <= WAREHOUSE_TABLE.SAMPLE_DATE
and (C1.END_DATE >= WAREHOUSE_TABLE.SAMPLE_DATE or
C1.END_DATE is null
)
),
MDL_FACTOR computed by
(select C1.MDL_FACTOR
from EXTRACT_CONTROL_VW C1
where C1.SAMPLING_SITE = WAREHOUSE_TABLE.SAMPLING_SITE
and C1.RECORD_TYPE in ('LIMITS','HISTORY')
and C1.EFFECTIVE_DATE <= WAREHOUSE_TABLE.SAMPLE_DATE
and (C1.END_DATE >= WAREHOUSE_TABLE.SAMPLE_DATE or
C1.END_DATE is null
)
),
MDL computed by
case
when (SAMPLE_RATE <> 0)
then
(0.003 * FLOW_RATE * MDL_FACTOR) / SAMPLE_RATE
end,
SAMPLE_ADJUSTED_RESULT computed by
case
when (PERIOD = 'Hour' and
MDL is not null
)
then
case
when cast (SAMPLE_RESULT as bigint(2)) > cast (MDL as
bigint(2))
then SAMPLE_RESULT
else MDL / 2
end
else
SAMPLE_RESULT
end
);
--
commit;
--
disconnect all;
--
export database file expimptest_db into expimptest.rbr;
--
import database from expimptest.rbr file test.rdb;
--
exit;
$!
$ set noverify
$ exit
$!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
| T.R | Title | User | Personal
Name | Date | Lines |
|---|
| 5032.1 | | M5::JHAYTER | | Mon Feb 17 1997 10:19 | 2 |
|
check out note 5019
|
| 5032.2 | | NOVA::SMITHI | Don't understate or underestimate Rdb! | Mon Feb 17 1997 11:27 | 11 |
| IMPORT always creates tables before views. So a table which references a VIEW
van not be IMPORTED.
I suggest they drop that column before the EXPORT. And manually define it
after the IMPORT is successful.
They are welcome to submit a low priority bug to have IMPORT handle this case,
however, it will not be trivial and will only make it into future versions of
Rdb (if at all).
Ian
|