Conference orarep::nomahs::repository

1075.0. "CDD-E-DTYPE_REQUIRED...but it's there" by M5::JAKUHN (jakuhn@us.oracle.com) Thu Mar 27 1997 22:52

    Hello all,
    I've been working on some integrate issues for a customer and stumbled
    upon this. a customer has views that reference views that reference 
    a table (script is below). When I integrate I get:
    
    %CDD-E-DTYPE_REQUIRED, field GROUP_ID must have a datatype forinclusion 
    in an Rdb/VMS database
    
    Ok. The view (ST_UNION) seems to work ok in SQL. and GROUP_ID seems to
    have a definition.?.
    
    SQL> show view st_union ;
    Information for table ST_UNION
    
    Columns for view ST_UNION:
    Column Name                     Data Type        Domain
    -----------                     ---------        ------
    GROUP_ID                        CHAR(12)
    AMOUNT                          BIGINT
     Source:
             select
             C2.GROUP_ID, C2.AMOUNT
             from CHECK_BAL_S_PLUS C2
             union all
             select C3.GROUP_ID, C3.AMOUNT
             from CHECK_BAL_S_MINUS C3
      
    
    I dunno. Here is a script below to reproduce:
    ( CDD 6.1-3)
    
    create database filename st_union ;
    create table ACCOLADE_ENTRIES (
    PROGRAM_ID
        CHAR (4),
    ACCOUNT_ID
        CHAR (5),
    AGENCY_ID
        CHAR (3),
    CONTRACT_ID
        CHAR (6),
    AGREEMENT_ID
        CHAR (7),
    FACILITY_ID
        CHAR (6),
    COST_CENTER_ID
        CHAR (4),
    PROCEDURE_ID
        CHAR (4),
    FUND_ID
        CHAR (5),
    CLIENT_ID
        CHAR (8),
    SOURCE_CODE
         CHAR (8),
    ENTRY_TYPE
        CHAR (2),
    ENTRY_GROUP_ID
        CHAR (12),
    ENTRY_GROUP_SEQ_NO
        CHAR (3),
    TRANSACTION_CODE
        CHAR (3),
    EFFECTIVE_DATE
        DATE VMS,
    ENTRY_AMOUNT
        BIGINT,
    ENTRY_SIGN
        CHAR (2),
    ENTRY_INVALID_RECORD
        CHAR (1),
    CREATION_DATE
        DATE VMS,
    CREATED_BY
        CHAR (12),
    MODIFY_DATE
         DATE VMS,
--        default CURRENT_TIMESTAMP,
    MODIFIED_BY
        CHAR (12),
    constraint ACCOLADE_ENTRIES_PRIMARY1
        primary key (PROGRAM_ID, ACCOUNT_ID, AGENCY_ID, CONTRACT_ID,
        AGREEMENT_ID, FACILITY_ID, COST_CENTER_ID, PROCEDURE_ID, FUND_ID,
        CLIENT_ID, ENTRY_GROUP_ID, ENTRY_GROUP_SEQ_NO)
        deferrable);

create view CHECK_BAL_S_PLUS
    (GROUP_ID,
     AMOUNT) as
    select
        C2.ENTRY_GROUP_ID,
        sum(C2.ENTRY_AMOUNT)
    from ACCOLADE_ENTRIES C2
    where (C2.ENTRY_SIGN = 'S+')
    group by C2.ENTRY_GROUP_ID;

create view CHECK_BAL_S_MINUS
    (GROUP_ID,
     AMOUNT) as
    select
        C2.ENTRY_GROUP_ID,
        sum((C2.ENTRY_AMOUNT * -1))
    from ACCOLADE_ENTRIES C2
    where (C2.ENTRY_SIGN = 'S-')
    group by C2.ENTRY_GROUP_ID;
 

create view ST_UNION
    (GROUP_ID,
     AMOUNT) as
    select
    C2.GROUP_ID, C2.AMOUNT
    from CHECK_BAL_S_PLUS C2
    union all
    select C3.GROUP_ID, C3.AMOUNT
    from CHECK_BAL_S_MINUS C3;
 commit;
    disco all ;
    integrate database filename st_union create pathname st_union;