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)
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'));