Home » SQL & PL/SQL » SQL & PL/SQL » Join two permanent tables with condition (Oracle 11g, 11.2.0.1.0, Windows Server 2008)
| Join two permanent tables with condition [message #574728] |
Tue, 15 January 2013 08:38  |
 |
marcopo
Messages: 24
Registered: December 2012
|
Junior Member |
|
|
Hi,
I have two permanent tables. I want to add the column DELTA_STROM from the table "Delta" into the table "TEST2". Here, the value of the field "POWER_DELTA" in the table "TEST2" depends on the field "Trade_Date".
If the time of the timestamp in table "Test2" is smaller than 12:40 than the value (DELTA_STROM from DELTA) of the day must be added into the field. If the time is huger than 12:40, the value of the next day must be added into the field "POWER_DELTA". All values for "Power_DELTA" for every day are in table "Delta" in the field "DELTA_STROM".
I just added the right values of "Power_Delta" into the table "TEST2" manually to give an understanding.
Best Regards and thank you very much
Marco
DATUM DELTA_STROM
-------- -----------
01.01.12 1.92
02.01.12 5.78
03.01.12 0.73
04.01.12 2.84
05.01.12 11.41
06.01.12 -2.76
07.01.12 0.43
08.01.12 1.25
09.01.12 -0.21
TRADE_DATE POWER_DELTA
---------------------------- -----------
02.01.12 11:21:41,720000000 5.78
02.01.12 12:44:01,236000000 0.73
02.01.12 14:05:01,845000000 0.73
02.01.12 16:21:01,345000000 0.73
02.01.12 17:25:41,470000000 0.73
03.01.12 10:31:01,376000000 0.73
03.01.12 11:55:11,798000000 0.73
03.01.12 16:32:47,612000000 2.84
04.01.12 08:24:01,486000000 2.84
04.01.12 12:24:21,189000000 2.84
04.01.12 17:46:21,123000000 11.41
CREATE TABLE "TEST2"
( "TRADE_DATE" TIMESTAMP (6),
"POWER_DELTA" NUMBER
);
CREATE TABLE "DELTA"
( "DATUM" DATE,
"DELTA_STROM" NUMBER(*,2)
);
INSERT INTO "INFODB"."TEST2" (TRADE_DATE, POWER_DELTA) VALUES (TO_TIMESTAMP('02.01.12 11:21:41,720000000', 'DD.MM.RR HH24:MI:SS,FF'), '5,78')
INSERT INTO "INFODB"."TEST2" (TRADE_DATE, POWER_DELTA) VALUES (TO_TIMESTAMP('02.01.12 12:44:01,236000000', 'DD.MM.RR HH24:MI:SS,FF'), '0,73')
INSERT INTO "INFODB"."TEST2" (TRADE_DATE, POWER_DELTA) VALUES (TO_TIMESTAMP('02.01.12 14:05:01,845000000', 'DD.MM.RR HH24:MI:SS,FF'), '0,73')
INSERT INTO "INFODB"."TEST2" (TRADE_DATE, POWER_DELTA) VALUES (TO_TIMESTAMP('02.01.12 16:21:01,345000000', 'DD.MM.RR HH24:MI:SS,FF'), '0,73')
INSERT INTO "INFODB"."TEST2" (TRADE_DATE, POWER_DELTA) VALUES (TO_TIMESTAMP('02.01.12 17:25:41,470000000', 'DD.MM.RR HH24:MI:SS,FF'), '0,73')
INSERT INTO "INFODB"."TEST2" (TRADE_DATE, POWER_DELTA) VALUES (TO_TIMESTAMP('03.01.12 10:31:01,376000000', 'DD.MM.RR HH24:MI:SS,FF'), '0,73')
INSERT INTO "INFODB"."TEST2" (TRADE_DATE, POWER_DELTA) VALUES (TO_TIMESTAMP('03.01.12 11:55:11,798000000', 'DD.MM.RR HH24:MI:SS,FF'), '0,73')
INSERT INTO "INFODB"."TEST2" (TRADE_DATE, POWER_DELTA) VALUES (TO_TIMESTAMP('03.01.12 16:32:47,612000000', 'DD.MM.RR HH24:MI:SS,FF'), '2,84')
INSERT INTO "INFODB"."TEST2" (TRADE_DATE, POWER_DELTA) VALUES (TO_TIMESTAMP('04.01.12 08:24:01,486000000', 'DD.MM.RR HH24:MI:SS,FF'), '2,84')
INSERT INTO "INFODB"."TEST2" (TRADE_DATE, POWER_DELTA) VALUES (TO_TIMESTAMP('04.01.12 12:24:21,189000000', 'DD.MM.RR HH24:MI:SS,FF'), '2,84')
INSERT INTO "INFODB"."TEST2" (TRADE_DATE, POWER_DELTA) VALUES (TO_TIMESTAMP('04.01.12 17:46:21,123000000', 'DD.MM.RR HH24:MI:SS,FF'), '11,41')
INSERT INTO "INFODB"."DELTA" (DATUM, DELTA_STROM) VALUES (TO_DATE('01.01.12', 'DD.MM.RR'), '1,92')
INSERT INTO "INFODB"."DELTA" (DATUM, DELTA_STROM) VALUES (TO_DATE('02.01.12', 'DD.MM.RR'), '5,78')
INSERT INTO "INFODB"."DELTA" (DATUM, DELTA_STROM) VALUES (TO_DATE('03.01.12', 'DD.MM.RR'), '0,73')
INSERT INTO "INFODB"."DELTA" (DATUM, DELTA_STROM) VALUES (TO_DATE('04.01.12', 'DD.MM.RR'), '2,84')
INSERT INTO "INFODB"."DELTA" (DATUM, DELTA_STROM) VALUES (TO_DATE('05.01.12', 'DD.MM.RR'), '11,41')
INSERT INTO "INFODB"."DELTA" (DATUM, DELTA_STROM) VALUES (TO_DATE('06.01.12', 'DD.MM.RR'), '-2,76')
INSERT INTO "INFODB"."DELTA" (DATUM, DELTA_STROM) VALUES (TO_DATE('07.01.12', 'DD.MM.RR'), '0,43')
INSERT INTO "INFODB"."DELTA" (DATUM, DELTA_STROM) VALUES (TO_DATE('08.01.12', 'DD.MM.RR'), '1,25')
INSERT INTO "INFODB"."DELTA" (DATUM, DELTA_STROM) VALUES (TO_DATE('09.01.12', 'DD.MM.RR'), '-0,21')
|
|
|
|
| Re: Join two permanent tables with condition [message #574731 is a reply to message #574728] |
Tue, 15 January 2013 09:14  |
 |
Michel Cadot
Messages: 54236
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Please provide a VALID test case.
Try 1:
SQL> CREATE TABLE "TEST2"
2 ( "TRADE_DATE" TIMESTAMP (6),
3 "POWER_DELTA" NUMBER
4 );
Table created.
SQL>
SQL>
SQL> CREATE TABLE "DELTA"
2 ( "DATUM" DATE,
3 "DELTA_STROM" NUMBER(*,2)
4 );
Table created.
SQL>
SQL>
SQL> INSERT INTO "INFODB"."TEST2" (TRADE_DATE, POWER_DELTA) VALUES (TO_TIMESTAMP('02.01.12 11:21:41,720000000', 'DD.MM.RR HH24:MI:SS,FF'), '5,78')
2 INSERT INTO "INFODB"."TEST2" (TRADE_DATE, POWER_DELTA) VALUES (TO_TIMESTAMP('02.01.12 12:44:01,236000000', 'DD.MM.RR HH24:MI:SS,FF'), '0,73')
3 INSERT INTO "INFODB"."TEST2" (TRADE_DATE, POWER_DELTA) VALUES (TO_TIMESTAMP('02.01.12 14:05:01,845000000', 'DD.MM.RR HH24:MI:SS,FF'), '0,73')
4 INSERT INTO "INFODB"."TEST2" (TRADE_DATE, POWER_DELTA) VALUES (TO_TIMESTAMP('02.01.12 16:21:01,345000000', 'DD.MM.RR HH24:MI:SS,FF'), '0,73')
5 INSERT INTO "INFODB"."TEST2" (TRADE_DATE, POWER_DELTA) VALUES (TO_TIMESTAMP('02.01.12 17:25:41,470000000', 'DD.MM.RR HH24:MI:SS,FF'), '0,73')
6 INSERT INTO "INFODB"."TEST2" (TRADE_DATE, POWER_DELTA) VALUES (TO_TIMESTAMP('03.01.12 10:31:01,376000000', 'DD.MM.RR HH24:MI:SS,FF'), '0,73')
7 INSERT INTO "INFODB"."TEST2" (TRADE_DATE, POWER_DELTA) VALUES (TO_TIMESTAMP('03.01.12 11:55:11,798000000', 'DD.MM.RR HH24:MI:SS,FF'), '0,73')
8 INSERT INTO "INFODB"."TEST2" (TRADE_DATE, POWER_DELTA) VALUES (TO_TIMESTAMP('03.01.12 16:32:47,612000000', 'DD.MM.RR HH24:MI:SS,FF'), '2,84')
9 INSERT INTO "INFODB"."TEST2" (TRADE_DATE, POWER_DELTA) VALUES (TO_TIMESTAMP('04.01.12 08:24:01,486000000', 'DD.MM.RR HH24:MI:SS,FF'), '2,84')
10 INSERT INTO "INFODB"."TEST2" (TRADE_DATE, POWER_DELTA) VALUES (TO_TIMESTAMP('04.01.12 12:24:21,189000000', 'DD.MM.RR HH24:MI:SS,FF'), '2,84')
11 INSERT INTO "INFODB"."TEST2" (TRADE_DATE, POWER_DELTA) VALUES (TO_TIMESTAMP('04.01.12 17:46:21,123000000', 'DD.MM.RR HH24:MI:SS,FF'), '11,41')
12
SQL> INSERT INTO "INFODB"."DELTA" (DATUM, DELTA_STROM) VALUES (TO_DATE('01.01.12', 'DD.MM.RR'), '1,92')
2 INSERT INTO "INFODB"."DELTA" (DATUM, DELTA_STROM) VALUES (TO_DATE('02.01.12', 'DD.MM.RR'), '5,78')
3 INSERT INTO "INFODB"."DELTA" (DATUM, DELTA_STROM) VALUES (TO_DATE('03.01.12', 'DD.MM.RR'), '0,73')
4 INSERT INTO "INFODB"."DELTA" (DATUM, DELTA_STROM) VALUES (TO_DATE('04.01.12', 'DD.MM.RR'), '2,84')
5 INSERT INTO "INFODB"."DELTA" (DATUM, DELTA_STROM) VALUES (TO_DATE('05.01.12', 'DD.MM.RR'), '11,41')
6 INSERT INTO "INFODB"."DELTA" (DATUM, DELTA_STROM) VALUES (TO_DATE('06.01.12', 'DD.MM.RR'), '-2,76')
7 INSERT INTO "INFODB"."DELTA" (DATUM, DELTA_STROM) VALUES (TO_DATE('07.01.12', 'DD.MM.RR'), '0,43')
8 INSERT INTO "INFODB"."DELTA" (DATUM, DELTA_STROM) VALUES (TO_DATE('08.01.12', 'DD.MM.RR'), '1,25')
9 INSERT INTO "INFODB"."DELTA" (DATUM, DELTA_STROM) VALUES (TO_DATE('09.01.12', 'DD.MM.RR'), '-0,21')
10 .
Try 2:
SQL> INSERT INTO "INFODB"."TEST2" (TRADE_DATE, POWER_DELTA) VALUES (TO_TIMESTAMP('02.01.12 11:21:41,720000000', 'DD.MM.RR HH24:MI:SS,FF'), '5,78');
INSERT INTO "INFODB"."TEST2" (TRADE_DATE, POWER_DELTA) VALUES (TO_TIMESTAMP('02.01.12 11:21:41,720000000', 'DD.MM.RR HH24:MI:SS,FF'), '5,78')
*
ERROR at line 1:
ORA-00942: table or view does not exist
...
Try 3:
SQL> INSERT INTO "TEST2" (TRADE_DATE, POWER_DELTA) VALUES (TO_TIMESTAMP('02.01.12 11:21:41,720000000', 'DD.MM.RR HH24:MI:SS,FF'), '5,78');
INSERT INTO "TEST2" (TRADE_DATE, POWER_DELTA) VALUES (TO_TIMESTAMP('02.01.12 11:21:41,720000000', 'DD.MM.RR HH24:MI:SS,FF'), '5,78')
*
ERROR at line 1:
ORA-01722: invalid number
...
I give up.
Remember A NUMBER IS NOT A STRING.
Regards
Michel
|
|
|
|
| Re: Join two permanent tables with condition [message #574737 is a reply to message #574731] |
Tue, 15 January 2013 09:30 |
Solomon Yakobson
Messages: 1404
Registered: January 2010
|
Senior Member |
|
|
It is not clear what exactly you need, but it looks like:
select trade_date,
power_delta + delta_strom
from test2,
delta
where trade_date >= datum
and trade_date < datum + interval '12:40' hour to minute
/
TRADE_DATE POWER_DELTA+DELTA_STROM
------------------------------ -----------------------
02-JAN-12 11.21.41.720000 AM 11.56
03-JAN-12 10.31.01.376000 AM 1.46
03-JAN-12 11.55.11.798000 AM 1.46
04-JAN-12 08.24.01.486000 AM 5.68
04-JAN-12 12.24.21.189000 PM 5.68
SQL>
SY.
|
|
|
|
| Re: Join two permanent tables with condition [message #574738 is a reply to message #574731] |
Tue, 15 January 2013 09:35 |
|
marcopo
Messages: 24
Registered: December 2012
|
Junior Member |
|
|
SORRY
INSERT INTO "DELTA" (DATUM, DELTA_STROM) VALUES ('01.01.12','1,92');
INSERT INTO "DELTA" (DATUM, DELTA_STROM) VALUES ('02.01.12','5,78');
INSERT INTO "DELTA" (DATUM, DELTA_STROM) VALUES ('03.01.12','0,73');
INSERT INTO "DELTA" (DATUM, DELTA_STROM) VALUES ('04.01.12','2,84');
INSERT INTO "DELTA" (DATUM, DELTA_STROM) VALUES ('05.01.12','11,41');
INSERT INTO "DELTA" (DATUM, DELTA_STROM) VALUES ('06.01.12','-2,76');
INSERT INTO "DELTA" (DATUM, DELTA_STROM) VALUES ('07.01.12','0,43');
INSERT INTO "DELTA" (DATUM, DELTA_STROM) VALUES ('08.01.12','1,25');
INSERT INTO "DELTA" (DATUM, DELTA_STROM) VALUES ('09.01.12','-0,21');
INSERT INTO "TEST2" (TRADE_DATE, POWER_DELTA) VALUES ('02.01.12 11:21:41,720000000','5,78');
INSERT INTO "TEST2" (TRADE_DATE, POWER_DELTA) VALUES ('02.01.12 12:44:01,236000000','0,73');
INSERT INTO "TEST2" (TRADE_DATE, POWER_DELTA) VALUES ('02.01.12 14:05:01,845000000','0,73');
INSERT INTO "TEST2" (TRADE_DATE, POWER_DELTA) VALUES ('02.01.12 16:21:01,345000000','0,73');
INSERT INTO "TEST2" (TRADE_DATE, POWER_DELTA) VALUES ('02.01.12 17:25:41,470000000','0,73');
INSERT INTO "TEST2" (TRADE_DATE, POWER_DELTA) VALUES ('03.01.12 10:31:01,376000000','0,73');
INSERT INTO "TEST2" (TRADE_DATE, POWER_DELTA) VALUES ('03.01.12 11:55:11,798000000','0,73');
INSERT INTO "TEST2" (TRADE_DATE, POWER_DELTA) VALUES ('03.01.12 16:32:47,612000000','2,84');
INSERT INTO "TEST2" (TRADE_DATE, POWER_DELTA) VALUES ('04.01.12 08:24:01,486000000','2,84');
INSERT INTO "TEST2" (TRADE_DATE, POWER_DELTA) VALUES ('04.01.12 12:24:21,189000000','2,84');
INSERT INTO "TEST2" (TRADE_DATE, POWER_DELTA) VALUES ('04.01.12 17:46:21,123000000','11,41');
|
|
|
|
| Re: Join two permanent tables with condition [message #574740 is a reply to message #574738] |
Tue, 15 January 2013 09:44 |
|
marcopo
Messages: 24
Registered: December 2012
|
Junior Member |
|
|
Hi,
it must look like this.
TABLE TEST2
TRADE_DATE POWER_DELTA (From Table DELTA)
---------------------------- -----------
02.01.12 11:21:41,720000000 5.78
02.01.12 12:44:01,236000000 0.73
02.01.12 14:05:01,845000000 0.73
02.01.12 16:21:01,345000000 0.73
02.01.12 17:25:41,470000000 0.73
03.01.12 10:31:01,376000000 0.73
03.01.12 11:55:11,798000000 0.73
03.01.12 16:32:47,612000000 2.84
04.01.12 08:24:01,486000000 2.84
04.01.12 12:24:21,189000000 2.84
04.01.12 17:46:21,123000000 11.41
If 02.01.12 11:21:41,720000000 in table Test2 than take the same date in Table DELTA and insert the value of the column "DELTA_STROM" into "POWER_DELTA"(TEST2) because the time is smaller or equal than 12:40.
If 02.01.12 12:44:01,236000000 in the table Test2 than take the date of the next day in table DELTA and insert the value of the column "DELTA_STROM" into "POWER_DELTA"(TEST2) because the time is huger 12:40.
Best Regards
|
|
|
|
|
|
| Re: Join two permanent tables with condition [message #574748 is a reply to message #574742] |
Tue, 15 January 2013 10:36 |
|
marcopo
Messages: 24
Registered: December 2012
|
Junior Member |
|
|
INSERT INTO "TEST2" (TRADE_DATE, POWER_DELTA) VALUES ('02.01.12 11:21:41,720000000',5.78);
INSERT INTO "TEST2" (TRADE_DATE, POWER_DELTA) VALUES ('02.01.12 12:44:01,236000000',0.73);
INSERT INTO "TEST2" (TRADE_DATE, POWER_DELTA) VALUES ('02.01.12 14:05:01,845000000',0.73);
INSERT INTO "TEST2" (TRADE_DATE, POWER_DELTA) VALUES ('02.01.12 16:21:01,345000000',0.73);
INSERT INTO "TEST2" (TRADE_DATE, POWER_DELTA) VALUES ('02.01.12 17:25:41,470000000',0.73);
INSERT INTO "TEST2" (TRADE_DATE, POWER_DELTA) VALUES ('03.01.12 10:31:01,376000000',0.73);
INSERT INTO "TEST2" (TRADE_DATE, POWER_DELTA) VALUES ('03.01.12 11:55:11,798000000',0.73);
INSERT INTO "TEST2" (TRADE_DATE, POWER_DELTA) VALUES ('03.01.12 16:32:47,612000000',2.84);
INSERT INTO "TEST2" (TRADE_DATE, POWER_DELTA) VALUES ('04.01.12 08:24:01,486000000',2.84);
INSERT INTO "TEST2" (TRADE_DATE, POWER_DELTA) VALUES ('04.01.12 12:24:21,189000000',2.84);
INSERT INTO "TEST2" (TRADE_DATE, POWER_DELTA) VALUES ('04.01.12 17:46:21,123000000',11.41);
INSERT INTO "DELTA" (DATUM, DELTA_STROM) VALUES ('01.01.12',1.92);
INSERT INTO "DELTA" (DATUM, DELTA_STROM) VALUES ('02.01.12',5.78);
INSERT INTO "DELTA" (DATUM, DELTA_STROM) VALUES ('03.01.12',0.73);
INSERT INTO "DELTA" (DATUM, DELTA_STROM) VALUES ('04.01.12',2.84);
INSERT INTO "DELTA" (DATUM, DELTA_STROM) VALUES ('05.01.12',11.41);
INSERT INTO "DELTA" (DATUM, DELTA_STROM) VALUES ('06.01.12',-2.76);
INSERT INTO "DELTA" (DATUM, DELTA_STROM) VALUES ('07.01.12',0.43);
INSERT INTO "DELTA" (DATUM, DELTA_STROM) VALUES ('08.01.12',1.25);
INSERT INTO "DELTA" (DATUM, DELTA_STROM) VALUES ('09.01.12',-0.21);
|
|
|
|
| Re: Join two permanent tables with condition [message #574751 is a reply to message #574740] |
Tue, 15 January 2013 10:47 |
Solomon Yakobson
Messages: 1404
Registered: January 2010
|
Senior Member |
|
|
select trade_date,
nvl(delta_strom,power_delta) power_delta
from test2,
delta
where trade_date >= datum(+)
and trade_date < datum(+) + interval '12:40' hour to minute
order by trade_date
/
TRADE_DATE POWER_DELTA
------------------------------ -----------
02-JAN-12 11.21.41.720000 AM 5.78
02-JAN-12 12.44.01.236000 PM .73
02-JAN-12 02.05.01.845000 PM .73
02-JAN-12 04.21.01.345000 PM .73
02-JAN-12 05.25.41.470000 PM .73
03-JAN-12 10.31.01.376000 AM .73
03-JAN-12 11.55.11.798000 AM .73
03-JAN-12 04.32.47.612000 PM 2.84
04-JAN-12 08.24.01.486000 AM 2.84
04-JAN-12 12.24.21.189000 PM 2.84
04-JAN-12 05.46.21.123000 PM 11.41
11 rows selected.
SQL>
SY.
|
|
|
|
| Re: Join two permanent tables with condition [message #574753 is a reply to message #574748] |
Tue, 15 January 2013 10:51 |
cookiemonster
Messages: 9169
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
When inserting dates you should always wrap the string with a call to to_date with the appropriate format mask.
When inserting timestamp you should always wrap the string with a call to to_timestamp with the appropriate format mask.
|
|
|
|
|
|
| Re: Join two permanent tables with condition [message #574784 is a reply to message #574766] |
Tue, 15 January 2013 16:36 |
Solomon Yakobson
Messages: 1404
Registered: January 2010
|
Senior Member |
|
|
merge
into test2 a
using (
select test2.rowid rid,
nvl(delta_strom,power_delta) power_delta
from test2,
delta
where trade_date >= datum(+)
and trade_date < datum(+) + interval '12:40' hour to minute
) b
on (
a.rowid = b.rid
)
when matched
then update
set a.power_delta = b.power_delta
/
SY.
|
|
|
|
|
|
|
|
| Re: Join two permanent tables with condition [message #574890 is a reply to message #574869] |
Wed, 16 January 2013 09:38 |
|
marcopo
Messages: 24
Registered: December 2012
|
Junior Member |
|
|
Ok I got the error in my table.
But unfortunately the original values of my field trade_date and datum looks like this. But for these values my table is always empty after 12:40 
INSERT INTO "INFODB"."TEST2" (TRADE_DATE, POWER_DELTA) VALUES (TO_TIMESTAMP('04.01.12 17:46:21,123000000', 'DD.MM.RR HH24:MI:SS,FF'), '11,41')
INSERT INTO "INFODB"."DELTA" (DATUM, DELTA_STROM) VALUES (TO_DATE('01.01.12', 'DD.MM.RR'), '1,92')
TRADE_DATE POWER_DELTA
---------------------------- -----------
02.01.12 11:21:41,720000000 5.78
02.01.12 12:44:01,236000000
02.01.12 14:05:01,845000000
02.01.12 16:21:01,345000000
02.01.12 17:25:41,470000000
03.01.12 10:31:01,376000000 0.73
03.01.12 11:55:11,798000000 0.73
03.01.12 16:32:47,612000000
04.01.12 08:24:01,486000000 2.84
04.01.12 12:24:21,189000000 2.84
04.01.12 17:46:21,123000000
In the end we used this test values. Here, it works fine.
INSERT INTO "TEST2" (TRADE_DATE, POWER_DELTA) VALUES ('04.01.12 17:46:21,123000000',11.41);
INSERT INTO "DELTA" (DATUM, DELTA_STROM) VALUES ('01.01.12',1.92);
Sorry for too much complications!
|
|
|
|
| Re: Join two permanent tables with condition [message #574903 is a reply to message #574890] |
Wed, 16 January 2013 12:37 |
Solomon Yakobson
Messages: 1404
Registered: January 2010
|
Senior Member |
|
|
Not sure what error you are talking about. Duplicate entries in TEST2 table are not an issue:
SQL> select * from test2 order by trade_date;
TRADE_DATE POWER_DELTA
------------------------------ -----------
02-JAN-12 11.21.41.720000 AM 5.78
02-JAN-12 12.44.01.236000 PM .73
02-JAN-12 02.05.01.845000 PM .73
02-JAN-12 04.21.01.345000 PM .73
02-JAN-12 05.25.41.470000 PM .73
03-JAN-12 10.31.01.376000 AM .73
03-JAN-12 11.55.11.798000 AM .73
03-JAN-12 04.32.47.612000 PM 2.84
04-JAN-12 08.24.01.486000 AM 2.84
04-JAN-12 12.24.21.189000 PM 2.84
04-JAN-12 05.46.21.123000 PM 11.41
TRADE_DATE POWER_DELTA
------------------------------ -----------
04-JAN-12 05.46.21.123000 PM 11.41
12 rows selected.
SQL> select * from delta;
DATUM DELTA_STROM
--------- -----------
01-JAN-12 1.92
02-JAN-12 5.78
03-JAN-12 .73
04-JAN-12 2.84
05-JAN-12 11.41
06-JAN-12 -2.76
07-JAN-12 .43
08-JAN-12 1.25
09-JAN-12 -.21
01-JAN-12 1.92
10 rows selected.
SQL> merge
2 into test2 a
3 using (
4 select test2.rowid rid,
5 nvl(delta_strom,power_delta) power_delta
6 from test2,
7 delta
8 where trade_date >= datum(+)
9 and trade_date < datum(+) + interval '12:40' hour to minute
10 ) b
11 on (
12 a.rowid = b.rid
13 )
14 when matched
15 then update
16 set a.power_delta = b.power_delta
17 /
12 rows merged.
SQL> select * from test2 order by trade_date;
TRADE_DATE POWER_DELTA
------------------------------ -----------
02-JAN-12 11.21.41.720000 AM 5.78
02-JAN-12 12.44.01.236000 PM .73
02-JAN-12 02.05.01.845000 PM .73
02-JAN-12 04.21.01.345000 PM .73
02-JAN-12 05.25.41.470000 PM .73
03-JAN-12 10.31.01.376000 AM .73
03-JAN-12 11.55.11.798000 AM .73
03-JAN-12 04.32.47.612000 PM 2.84
04-JAN-12 08.24.01.486000 AM 2.84
04-JAN-12 12.24.21.189000 PM 2.84
04-JAN-12 05.46.21.123000 PM 11.41
TRADE_DATE POWER_DELTA
------------------------------ -----------
04-JAN-12 05.46.21.123000 PM 11.41
12 rows selected.
SQL>
It is duplicate entries in DELTA with different DELAT_STROM values that have a before 12:40 match in test 2:
SQL> select *
2 from delta
3 order by datum
4 /
DATUM DELTA_STROM
--------- -----------
01-JAN-12 1.92
01-JAN-12 1.92
02-JAN-12 9.99
02-JAN-12 5.78
03-JAN-12 .73
04-JAN-12 2.84
05-JAN-12 11.41
06-JAN-12 -2.76
07-JAN-12 .43
08-JAN-12 1.25
09-JAN-12 -.21
11 rows selected.
SQL>
So now we have an issue. TEST2 row:
TRADE_DATE POWER_DELTA
------------------------------ -----------
02-JAN-12 11.21.41.720000 AM 5.78
Maps into two rows in DELTA:
DATUM DELTA_STROM
--------- -----------
02-JAN-12 9.99
02-JAN-12 5.78
As a result Oracle doesn't know which value to use:
SQL> merge
2 into test2 a
3 using (
4 select test2.rowid rid,
5 nvl(delta_strom,power_delta) power_delta
6 from test2,
7 delta
8 where trade_date >= datum(+)
9 and trade_date < datum(+) + interval '12:40' hour to minute
10 ) b
11 on (
12 a.rowid = b.rid
13 )
14 when matched
15 then update
16 set a.power_delta = b.power_delta
17 /
merge
*
ERROR at line 1:
ORA-30926: unable to get a stable set of rows in the source tables
SQL>
SY.
|
|
|
|
| Re: Join two permanent tables with condition [message #574965 is a reply to message #574903] |
Thu, 17 January 2013 03:58 |
|
marcopo
Messages: 24
Registered: December 2012
|
Junior Member |
|
|
Hi,
please delete your table TEST2 and insert these values (values like my original table).
INSERT INTO "TEST2" (TRADE_DATE) VALUES (TO_TIMESTAMP('02.01.12 11:21:41,720000000', 'DD.MM.RR HH24:MI:SS,FF'));
INSERT INTO "TEST2" (TRADE_DATE) VALUES (TO_TIMESTAMP('02.01.12 12:44:01,236000000', 'DD.MM.RR HH24:MI:SS,FF'));
INSERT INTO "TEST2" (TRADE_DATE) VALUES (TO_TIMESTAMP('02.01.12 14:05:01,845000000', 'DD.MM.RR HH24:MI:SS,FF'));
INSERT INTO "TEST2" (TRADE_DATE) VALUES (TO_TIMESTAMP('02.01.12 16:21:01,345000000', 'DD.MM.RR HH24:MI:SS,FF'));
INSERT INTO "TEST2" (TRADE_DATE) VALUES (TO_TIMESTAMP('02.01.12 17:25:41,470000000', 'DD.MM.RR HH24:MI:SS,FF'));
INSERT INTO "TEST2" (TRADE_DATE) VALUES (TO_TIMESTAMP('03.01.12 10:31:01,376000000', 'DD.MM.RR HH24:MI:SS,FF'));
INSERT INTO "TEST2" (TRADE_DATE) VALUES (TO_TIMESTAMP('03.01.12 11:55:11,798000000', 'DD.MM.RR HH24:MI:SS,FF'));
INSERT INTO "TEST2" (TRADE_DATE) VALUES (TO_TIMESTAMP('03.01.12 16:32:47,612000000', 'DD.MM.RR HH24:MI:SS,FF'));
INSERT INTO "TEST2" (TRADE_DATE) VALUES (TO_TIMESTAMP('04.01.12 08:24:01,486000000', 'DD.MM.RR HH24:MI:SS,FF'));
INSERT INTO "TEST2" (TRADE_DATE) VALUES (TO_TIMESTAMP('04.01.12 12:24:21,189000000', 'DD.MM.RR HH24:MI:SS,FF'));
INSERT INTO "TEST2" (TRADE_DATE) VALUES (TO_TIMESTAMP('04.01.12 17:46:21,123000000', 'DD.MM.RR HH24:MI:SS,FF'));
TRADE_DATE POWER_DELTA
---------------------------- -----------
02.01.12 11:21:41,720000000 5.78
02.01.12 12:44:01,236000000
02.01.12 14:05:01,845000000
02.01.12 16:21:01,345000000
02.01.12 17:25:41,470000000
03.01.12 10:31:01,376000000 0.73
03.01.12 11:55:11,798000000 0.73
03.01.12 16:32:47,612000000
04.01.12 08:24:01,486000000 2.84
04.01.12 12:24:21,189000000 2.84
04.01.12 17:46:21,123000000
select trade_date,
nvl(delta_strom,power_delta) power_delta
from test2,
delta
where trade_date >= datum(+)
and trade_date < datum(+) + interval '12:40' hour to minute
order by trade_date;
|
|
|
|
|
|
| Re: Join two permanent tables with condition [message #574990 is a reply to message #574986] |
Thu, 17 January 2013 08:19 |
|
marcopo
Messages: 24
Registered: December 2012
|
Junior Member |
|
|
Hi,
the values of my goal table are looks like the ones I posted before.
INSERT INTO "INFODB"."TEST2" (TRADE_DATE, POWER_DELTA) VALUES (TO_TIMESTAMP('02.01.12 11:21:41,720000000', 'DD.MM.RR HH24:MI:SS,FF'), '5,78');
INSERT INTO "INFODB"."TEST2" (TRADE_DATE, POWER_DELTA) VALUES (TO_TIMESTAMP('02.01.12 12:44:01,236000000', 'DD.MM.RR HH24:MI:SS,FF'), '0,73');
INSERT INTO "INFODB"."TEST2" (TRADE_DATE, POWER_DELTA) VALUES (TO_TIMESTAMP('02.01.12 14:05:01,845000000', 'DD.MM.RR HH24:MI:SS,FF'), '0,73');
INSERT INTO "INFODB"."TEST2" (TRADE_DATE, POWER_DELTA) VALUES (TO_TIMESTAMP('02.01.12 16:21:01,345000000', 'DD.MM.RR HH24:MI:SS,FF'), '0,73');
INSERT INTO "INFODB"."TEST2" (TRADE_DATE, POWER_DELTA) VALUES (TO_TIMESTAMP('02.01.12 17:25:41,470000000', 'DD.MM.RR HH24:MI:SS,FF'), '0,73');
INSERT INTO "INFODB"."TEST2" (TRADE_DATE, POWER_DELTA) VALUES (TO_TIMESTAMP('03.01.12 10:31:01,376000000', 'DD.MM.RR HH24:MI:SS,FF'), '0,73');
INSERT INTO "INFODB"."TEST2" (TRADE_DATE, POWER_DELTA) VALUES (TO_TIMESTAMP('03.01.12 11:55:11,798000000', 'DD.MM.RR HH24:MI:SS,FF'), '0,73');
INSERT INTO "INFODB"."TEST2" (TRADE_DATE, POWER_DELTA) VALUES (TO_TIMESTAMP('03.01.12 16:32:47,612000000', 'DD.MM.RR HH24:MI:SS,FF'), '2,84');
INSERT INTO "INFODB"."TEST2" (TRADE_DATE, POWER_DELTA) VALUES (TO_TIMESTAMP('04.01.12 08:24:01,486000000', 'DD.MM.RR HH24:MI:SS,FF'), '2,84');
INSERT INTO "INFODB"."TEST2" (TRADE_DATE, POWER_DELTA) VALUES (TO_TIMESTAMP('04.01.12 12:24:21,189000000', 'DD.MM.RR HH24:MI:SS,FF'), '2,84');
INSERT INTO "INFODB"."TEST2" (TRADE_DATE, POWER_DELTA) VALUES (TO_TIMESTAMP('04.01.12 17:46:21,123000000', 'DD.MM.RR HH24:MI:SS,FF'), '11,41');
INSERT INTO "INFODB"."DELTA" (DATUM, DELTA_STROM) VALUES (TO_DATE('01.01.12', 'DD.MM.RR'), '1,92');
INSERT INTO "INFODB"."DELTA" (DATUM, DELTA_STROM) VALUES (TO_DATE('02.01.12', 'DD.MM.RR'), '5,78');
INSERT INTO "INFODB"."DELTA" (DATUM, DELTA_STROM) VALUES (TO_DATE('03.01.12', 'DD.MM.RR'), '0,73');
INSERT INTO "INFODB"."DELTA" (DATUM, DELTA_STROM) VALUES (TO_DATE('04.01.12', 'DD.MM.RR'), '2,84');
INSERT INTO "INFODB"."DELTA" (DATUM, DELTA_STROM) VALUES (TO_DATE('05.01.12', 'DD.MM.RR'), '11,41');
INSERT INTO "INFODB"."DELTA" (DATUM, DELTA_STROM) VALUES (TO_DATE('06.01.12', 'DD.MM.RR'), '-2,76');
INSERT INTO "INFODB"."DELTA" (DATUM, DELTA_STROM) VALUES (TO_DATE('07.01.12', 'DD.MM.RR'), '0,43');
INSERT INTO "INFODB"."DELTA" (DATUM, DELTA_STROM) VALUES (TO_DATE('08.01.12', 'DD.MM.RR'), '1,25');
INSERT INTO "INFODB"."DELTA" (DATUM, DELTA_STROM) VALUES (TO_DATE('09.01.12', 'DD.MM.RR'), '-0,21');
I had to change the values in the above discussion. Here, I deleted the function "(TO_TIMESTAMP(" and "TO_DATE(" as well as the string value into a number in the original data.
Now the values look like that. You worked with these values.
INSERT INTO "DELTA" (DATUM, DELTA_STROM) VALUES ('01.01.12',1.92);
INSERT INTO "DELTA" (DATUM, DELTA_STROM) VALUES ('02.01.12',5.78);
INSERT INTO "DELTA" (DATUM, DELTA_STROM) VALUES ('03.01.12',0.73);
INSERT INTO "DELTA" (DATUM, DELTA_STROM) VALUES ('04.01.12',2.84);
INSERT INTO "DELTA" (DATUM, DELTA_STROM) VALUES ('05.01.12',11.41);
INSERT INTO "DELTA" (DATUM, DELTA_STROM) VALUES ('06.01.12',-2.76);
INSERT INTO "DELTA" (DATUM, DELTA_STROM) VALUES ('07.01.12',0.43);
INSERT INTO "DELTA" (DATUM, DELTA_STROM) VALUES ('08.01.12',1.25);
INSERT INTO "DELTA" (DATUM, DELTA_STROM) VALUES ('09.01.12',-0.21);
INSERT INTO "TEST2" (TRADE_DATE, POWER_DELTA) VALUES ('02.01.12 11:21:41,720000000',5.78);
INSERT INTO "TEST2" (TRADE_DATE, POWER_DELTA) VALUES ('02.01.12 12:44:01,236000000',0.73);
INSERT INTO "TEST2" (TRADE_DATE, POWER_DELTA) VALUES ('02.01.12 14:05:01,845000000',0.73);
INSERT INTO "TEST2" (TRADE_DATE, POWER_DELTA) VALUES ('02.01.12 16:21:01,345000000',0.73);
INSERT INTO "TEST2" (TRADE_DATE, POWER_DELTA) VALUES ('02.01.12 17:25:41,470000000',0.73);
INSERT INTO "TEST2" (TRADE_DATE, POWER_DELTA) VALUES ('03.01.12 10:31:01,376000000',0.73);
INSERT INTO "TEST2" (TRADE_DATE, POWER_DELTA) VALUES ('03.01.12 11:55:11,798000000',0.73);
INSERT INTO "TEST2" (TRADE_DATE, POWER_DELTA) VALUES ('03.01.12 16:32:47,612000000',2.84);
INSERT INTO "TEST2" (TRADE_DATE, POWER_DELTA) VALUES ('04.01.12 08:24:01,486000000',2.84);
INSERT INTO "TEST2" (TRADE_DATE, POWER_DELTA) VALUES ('04.01.12 12:24:21,189000000',2.84);
INSERT INTO "TEST2" (TRADE_DATE, POWER_DELTA) VALUES ('04.01.12 17:46:21,123000000',11.41);
For these values the query works fine. All values are updated correctly. But with the values before all rows after 12:40 are not updated. So how can I fix this that my fields in the goal table get updated?
Best Regards an thank you
|
|
|
|
| Re: Join two permanent tables with condition [message #574994 is a reply to message #574990] |
Thu, 17 January 2013 09:47 |
Solomon Yakobson
Messages: 1404
Registered: January 2010
|
Senior Member |
|
|
I missed: "If the time is huger than 12:40, the value of the next day must be added into the field POWER_DELTA". Use:
merge
into test2 a
using (
select test2.rowid rid,
nvl(delta_strom,power_delta) power_delta
from test2,
delta
where datum = trunc(trade_date + interval '11:20' hour to minute) -- 11:20 = 24:00 - 12:40
) b
on (
a.rowid = b.rid
)
when matched
then update
set a.power_delta = b.power_delta
/
For example:
SQL> select *
2 from test2
3 /
TRADE_DATE POWER_DELTA
------------------------------ -----------
02-JAN-12 02.05.01.845000 PM
02-JAN-12 04.21.01.345000 PM
02-JAN-12 05.25.41.470000 PM
03-JAN-12 10.31.01.376000 AM
03-JAN-12 11.55.11.798000 AM
03-JAN-12 04.32.47.612000 PM
04-JAN-12 08.24.01.486000 AM
04-JAN-12 12.24.21.189000 PM
04-JAN-12 05.46.21.123000 PM
9 rows selected.
SQL> select *
2 from delta
3 /
DATUM DELTA_STROM
--------- -----------
01-JAN-12 1.92
02-JAN-12 5.78
03-JAN-12 .73
04-JAN-12 2.84
05-JAN-12 11.41
06-JAN-12 -2.76
07-JAN-12 .43
08-JAN-12 1.25
09-JAN-12 -.21
9 rows selected.
SQL> merge
2 into test2 a
3 using (
4 select test2.rowid rid,
5 nvl(delta_strom,power_delta) power_delta
6 from test2,
7 delta
8 where datum = trunc(trade_date + interval '11:20' hour to minute)
9 ) b
10 on (
11 a.rowid = b.rid
12 )
13 when matched
14 then update
15 set a.power_delta = b.power_delta
16 /
9 rows merged.
SQL> select *
2 from test2
3 /
TRADE_DATE POWER_DELTA
------------------------------ -----------
02-JAN-12 02.05.01.845000 PM .73
02-JAN-12 04.21.01.345000 PM .73
02-JAN-12 05.25.41.470000 PM .73
03-JAN-12 10.31.01.376000 AM .73
03-JAN-12 11.55.11.798000 AM .73
03-JAN-12 04.32.47.612000 PM 2.84
04-JAN-12 08.24.01.486000 AM 2.84
04-JAN-12 12.24.21.189000 PM 2.84
04-JAN-12 05.46.21.123000 PM 11.41
9 rows selected.
SQL>
SY.
|
|
|
|
|
|
Goto Forum:
Current Time: Fri May 24 13:57:38 CDT 2013
Total time taken to generate the page: 0.25460 seconds
|
] 
Members
Search
Help
Register
Login
Home
