sqloracle-databasejoinunionora-00001

Merging two columns from two tables in PL/SQL


I'm having tremendous difficulty trying to merge two columns from two tables in PL/SQL. I have been working since 9:00am on this and well I give up, please help me.

Purpose

I would like to create a new table (call it temp). That merges information from two other columns in two different tables.

Code So far

CREATE TABLE temp
    (
        temptimeKey           CHAR(24) NOT NULL ,
        temptimeHour          INTEGER NULL ,
        temptimeMinute        INTEGER NULL ,
        temptimeSecond        INTEGER NULL ,
        temptimeMonth         INTEGER NULL ,
        temptimeDay           INTEGER NULL ,
        temptimeYear          INTEGER NULL ,
        temptimeQuarter       INTEGER NULL ,
        CONSTRAINT  XPKTEMPTIME PRIMARY KEY (temptimeKey)
    );

    insert into temp
    SELECT 
        TO_CHAR(busFareDate, 'MM/DD/YYYY HH:MM:SS Q'),
        TO_NUMBER(TO_CHAR(busFareDate, 'HH12')),
        TO_NUMBER(TO_CHAR(busFareDate, 'MI')),
        TO_NUMBER(TO_CHAR(busFareDate, 'SS')), 
        TO_NUMBER(TO_CHAR(busFareDate, 'MM')),
        TO_NUMBER(TO_CHAR(busFareDate, 'DD')),
        TO_NUMBER(TO_CHAR(busFareDate, 'YYYY')),
        TO_NUMBER(TO_CHAR(busFareDate, 'Q'))
    FROM 
        bus_fare
    UNION
    SELECT
        TO_CHAR(trainFareDate, 'MM/DD/YYYY HH:MM:SS Q'),
        TO_NUMBER(TO_CHAR(trainFareDate, 'HH12')),
        TO_NUMBER(TO_CHAR(trainFareDate, 'MI')),
        TO_NUMBER(TO_CHAR(trainFareDate, 'SS')), 
        TO_NUMBER(TO_CHAR(trainFareDate, 'MM')),
        TO_NUMBER(TO_CHAR(trainFareDate, 'DD')),
        TO_NUMBER(TO_CHAR(trainFareDate, 'YYYY')),
        TO_NUMBER(TO_CHAR(trainFareDate, 'Q'))
    FROM 
        train_fare;

    drop table temp cascade constraints;

So far, this code only gives me a violation of unique constraint:

ERROR at line 1:
ORA-00001: unique constraint (OPS$FNAVA.XPKTEMPTIME) violated

Can you spot what I am doing wrong? (any help is welcome)


Solution

  • Try this:

    insert into temp
    SELECT 
        TO_CHAR(busFareDate, 'MM/DD/YYYY HH:MI:SS Q'),
        TO_NUMBER(TO_CHAR(busFareDate, 'HH12')),
        TO_NUMBER(TO_CHAR(busFareDate, 'MI')),
        TO_NUMBER(TO_CHAR(busFareDate, 'SS')), 
        TO_NUMBER(TO_CHAR(busFareDate, 'MM')),
        TO_NUMBER(TO_CHAR(busFareDate, 'DD')),
        TO_NUMBER(TO_CHAR(busFareDate, 'YYYY')),
        TO_NUMBER(TO_CHAR(busFareDate, 'Q'))
    FROM (
            SELECT busFareDate 
            FROM bus_fare 
            GROUP BY busFareDate
            UNION
            SELECT trainFareDate 
            FROM train_fare 
            GROUP BY trainFareDate) AS Dates
    GROUP BY TO_CHAR(busFareDate, 'MM/DD/YYYY HH:MI:SS Q'),
            TO_NUMBER(TO_CHAR(busFareDate, 'HH12')),
            TO_NUMBER(TO_CHAR(busFareDate, 'MI')),
            TO_NUMBER(TO_CHAR(busFareDate, 'SS')), 
            TO_NUMBER(TO_CHAR(busFareDate, 'MM')),
            TO_NUMBER(TO_CHAR(busFareDate, 'DD')),
            TO_NUMBER(TO_CHAR(busFareDate, 'YYYY')),
            TO_NUMBER(TO_CHAR(busFareDate, 'Q'));