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 Go to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 59748
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2060
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #574742 is a reply to message #574738] Tue, 15 January 2013 10:04 Go to previous messageGo to next message
Michel Cadot
Messages: 59748
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
SORRY


Still not correct.

Regards
Michel
Re: Join two permanent tables with condition [message #574748 is a reply to message #574742] Tue, 15 January 2013 10:36 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2060
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 Go to previous messageGo to next message
cookiemonster
Messages: 11177
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 #574766 is a reply to message #574753] Tue, 15 January 2013 12:49 Go to previous messageGo to next message
marcopo
Messages: 24
Registered: December 2012
Junior Member
Thanks a lot,

but how looks the UPDATE statement?
Re: Join two permanent tables with condition [message #574784 is a reply to message #574766] Tue, 15 January 2013 16:36 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2060
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 #574852 is a reply to message #574784] Wed, 16 January 2013 02:42 Go to previous messageGo to next message
marcopo
Messages: 24
Registered: December 2012
Junior Member
Ok thanks, but now I get this error.

30926. 00000 - "unable to get a stable set of rows in the source tables"
*Cause: A stable set of rows could not be got because of large dml
activity or a non-deterministic where clause.
*Action: Remove any non-deterministic where clauses and reissue the dml.

If I am working with DISTINCT only the half table appears.
Re: Join two permanent tables with condition [message #574869 is a reply to message #574852] Wed, 16 January 2013 07:27 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2060
Registered: January 2010
Senior Member
That means you have multiple rows in delta for same date. As a result Oracle can't determine which value to use.

SY.
Re: Join two permanent tables with condition [message #574890 is a reply to message #574869] Wed, 16 January 2013 09:38 Go to previous messageGo to next message
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 Sad

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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2060
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 Go to previous messageGo to next message
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 #574986 is a reply to message #574965] Thu, 17 January 2013 06:45 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2060
Registered: January 2010
Senior Member
OK. So what's your question?

SY.
Re: Join two permanent tables with condition [message #574990 is a reply to message #574986] Thu, 17 January 2013 08:19 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2060
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.
Re: Join two permanent tables with condition [message #575218 is a reply to message #574994] Mon, 21 January 2013 06:30 Go to previous message
marcopo
Messages: 24
Registered: December 2012
Junior Member
Thanks so much!
Previous Topic: Gloabal Temporary Table
Next Topic: Help in building batch logic
Goto Forum:
  


Current Time: Sat Nov 22 20:43:15 CST 2014

Total time taken to generate the page: 0.10635 seconds