Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 412
  • Last Modified:

Changes in the range of dates for execution of store procedure

Hi experts,
I need to change the date range that handles the execution of the store procedure.

His execution takes values from a table and then inserts or updates made on another board.

When I run, passing him an IN parameter (p_in_time DATE):
update_1dia_proc exec (TO_DATE ('30 / 05/2013 3:00:00 ',' DD / MM / YYYY HH24: MI: SS '));

Open in new window

takes data from:
a_hora_test

Open in new window

and the inserted or updated in:
a_1dia_test

Open in new window

The result is:
Processing -> 30/05/2013 00:00:00
A_HORA_TEST -> A_1DIA_TEST
Updating utchora = 30/05/2013 00:00:00, pointnumber = 330000
Updating utchora = 30/05/2013 00:00:00, pointnumber = 330001

Open in new window

But I need that the insert or update be conducted on:
Processing -> 30/05/2013 03:00:00
A_HORA_TEST -> A_1DIA_TEST
Updating utchora = 30/05/2013 03:00:00, pointnumber = 330000
Updating utchora = 30/05/2013 03:00:00, pointnumber = 330001

Open in new window

I can not find this variant and so I come to your experience.

Attachment tables, inserts and procedure.

Thankyou in advanced!
update-1dia-proc.sql
script-table-inserts-hora.sql
script-table-inserts-1dia.sql
0
carlino70
Asked:
carlino70
  • 3
  • 2
1 Solution
 
slightwv (䄆 Netminder) Commented:
Check out this line:
v_in_time := TRUNC (p_in_time, 'DD');

This strips off the time portion of the date.
0
 
carlino70Author Commented:
slightwv, i changed that line, for example:
v_in_time := p_in_time;

Open in new window

And nothing made over the tables:
Processing -> 30/05/2013 03:00:00
A_HORA_TEST -> A_1DIA_TEST

Open in new window

never gets update lines or inserts.
0
 
slightwv (䄆 Netminder) Commented:
OK, look further down in the code.  You will need to adjust the following:
WHERE TRUNC ( utctime, ''DD'')+1 = :1)

Likely changing it from an '=' to a '>=' and '<'.

I cannot say for sure because I don't understand your exact requirements.
0
 
carlino70Author Commented:
Look here:

The table a_hora_test contains data inserted by hour.

The procedure take all data ( he takes max, min, average and date of each one ) of a_hora_test table, and then are inserted or updated the  a_1dia_test table.

That insert or update must be done over the mark of '03:00:00' at the next day.

Ej: In this case the table a_hora_test, have data of
'29/05/2013 10:00:00' and  '29/05/2013 11:00:00' 

Open in new window

Then that insert or update, must be done in a_1dia_test:
'30/05/2013 03:00:00'

Open in new window

But is done in:
'30/05/2013 00:00:00'

Open in new window

wrong way!

Please, tell me if you dont understand yet.

Thank you.
0
 
carlino70Author Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for carlino70's comment #a39281384

for the following reason:

Thanks!
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now