Database

Re: Create an sql script that compares two dbase and updates dbase and insert date of update in a column in oracle 11g

20-May-13 8:41

Ok i have the result from the both db's in this query now i want to insert the result in DB_2

this the query that gives me the result:
select /*+ FULL(Tbl1) */
PSJ_GESTION, PSJ_COD, PSJ_PLACA, PSJ_PESO, PSJ_FECHA
, PSJ_ESTADO, BLZ_COD, MNF_COD, DMN_COD, USR_COD
, PSJ_OPERACION, TIC_COD, PSJ_TARA, PSJ_NETO
from COMPUSOFT.PESAJE Tbl1
minus
select /*+ FULL(Tbl2) */
PSJ_GESTION, PSJ_COD, PSJ_PLACA, PSJ_PESO, PSJ_FECHA
, PSJ_ESTADO, BLZ_COD, MNF_COD, DMN_COD, USR_COD
, PSJ_OPERACION, TIC_COD, PSJ_TARA, PSJ_NETO
from COMPUSOFT.PESAJE@"DB_2" Tbl2
Order by PSJ_GESTION ASC NULLS LAST, PSJ_COD ASC NULLS LAST, PSJ_PLACA ASC NULLS LAST, PSJ_PESO ASC NULLS LAST, PSJ_FECHA ASC NULLS LAST, PSJ_ESTADO ASC NULLS LAST, BLZ_COD ASC NULLS LAST, MNF_COD ASC NULLS LAST, DMN_COD ASC NULLS LAST, USR_COD ASC NULLS LAST, TIC_COD ASC NULLS LAST, PSJ_TARA ASC NULLS LAST, PSJ_NETO ASC NULLS LAST

Eddy Vluggen20-May-13 8:49

Re: Create an sql script that compares two dbase and updates dbase and insert date of update in a column in oracle 11g

20-May-13 8:49

SQL

INSERT INTO TableName 
  SELECT [...]

Source[^]

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Miguel Quintanilla20-May-13 9:09

Re: Create an sql script that compares two dbase and updates dbase and insert date of update in a column in oracle 11g

20-May-13 9:09

I have try this:
Insert into COMPUSOFT.PESAJE@DB_2

select /*+ FULL(Tbl1) */
PSJ_GESTION, PSJ_COD, PSJ_PLACA, PSJ_PESO, PSJ_FECHA
, PSJ_ESTADO, BLZ_COD, MNF_COD, DMN_COD, USR_COD
, PSJ_OPERACION, TIC_COD, PSJ_TARA, PSJ_NETO
from COMPUSOFT.PESAJE Tbl1
minus
select /*+ FULL(Tbl2) */
PSJ_GESTION, PSJ_COD, PSJ_PLACA, PSJ_PESO, PSJ_FECHA
, PSJ_ESTADO, BLZ_COD, MNF_COD, DMN_COD, USR_COD
, PSJ_OPERACION, TIC_COD, PSJ_TARA, PSJ_NETO
from COMPUSOFT.PESAJE@"DB_2" Tbl2;

But i get "missing values"

Eddy Vluggen20-May-13 9:51

Re: Create an sql script that compares two dbase and updates dbase and insert date of update in a column in oracle 11g

20-May-13 9:51

Miguel Quintanilla wrote:
But i get "missing values"

Can you post the complete message?

This would only work if the table you're inserting to has columns to match the query's result.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Miguel Quintanilla20-May-13 16:23

Re: Create an sql script that compares two dbase and updates dbase and insert date of update in a column in oracle 11g

20-May-13 16:23

I have solved it ! It was a missmatch in the first select ok how do i store the query so i can call it in a store_procedure

Eddy Vluggen20-May-13 19:45

Re: Create an sql script that compares two dbase and updates dbase and insert date of update in a column in oracle 11g

20-May-13 19:45

Doesn't Oracle have a "create procedure" or something similar? Smile | :)

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Mycroft Holmes16-May-13 21:24

Re: Create an sql script that compares two dbase and updates dbase and insert date of update in a column in oracle 11g

16-May-13 21:24

We won't actually do the writing for you but will help when you have a problem (not the entire process). Try breaking it down into smaller bits.

Write a query that compares the 2 tables and identifies the records that need to be updated.

Test it

Now change the query to do the actual update.

Create a stored procedure using the update query.

Look into database job scheduling to call the stored procedure you have created (I know SQL Server but not Oracle).

If this is beyond you then you need to get some education in working with a database, specifically Oracle which is not a trivial exercise.

Never underestimate the power of human stupidity
RAH

Miguel Quintanilla20-May-13 7:09

Re: Create an sql script that compares two dbase and updates dbase and insert date of update in a column in oracle 11g

20-May-13 7:09

Hi many thanks for all the help you give me. here is my problem i want to use me of this:
this i have on toad:
-- run every 5 MINUTES
BEGIN
dbms_scheduler.create_schedule(
schedule_name => 'INTERVAL_EVERY_5_MINUTES',
start_date => trunc(sysdate)+18/24,
repeat_interval => 'freq=MINUTELY;interval=5',
comments => 'Runtime: Every day all 5 minutes');
END;
/
begin
-- Call a procedure of a database package
dbms_scheduler.create_program
(program_name=> 'PROG_COLLECT_SESS_DATA',
program_type=> 'STORED_PROCEDURE',
program_action=> '?????? i dont have this yet',
enabled=>true,
comments=>'Procedure to collect session information'
);
end;

begin
-- Connect both dbms_scheduler parts by creating the final job
dbms_scheduler.create_job
(job_name => 'JOB_COLLECT_SESS_DATA',
program_name=> 'PROG_COLLECT_SESS_DATA',
schedule_name=>'INTERVAL_EVERY_5_MINUTES',
enabled=>true,
auto_drop=>false,
comments=>'Job to collect data about session values every 5 minutes');
end;

I AM GOING TO USE THIS SCHEDULED JOB AND NOW I WANT TO CREATE MY SQL AND I HAVE A LITTLE BIT OF TROUBLE.
I HAVE DONE THIS DBLINK:
CREATE DATABASE LINK DB_2 CONNECT TO COMPUSOFT IDENTIFIED BY albosapam02 USING 'inv2'
AND THEN I HAVE DONE THIS:

SELECT * FROM PESAJE
MINUS
SELECT * FROM PESAJE@DB_2
IT GIVES ME A RESULT BUT NOW I WANT TO INSERT THAT RESULT IN THE DB_2 AND STORE THE QUERY SO I CAN ALL IT ON THE JOB..
aNY HELP???

Mycroft Holmes20-May-13 12:25

Re: Create an sql script that compares two dbase and updates dbase and insert date of update in a column in oracle 11g

20-May-13 12:25

Sorry my knowledge of Oracle and DB2 is very limited, I'm not going to be able to help with the details on those platforms. Oracle has a dedicated support forum, Ask Ted I think, you should get more relevant support there!

Never underestimate the power of human stupidity
RAH

Miguel Quintanilla20-May-13 16:11

Create a procedure to change date value of a column in a table ? (SQL)

20-May-13 16:11

Thanks anyway

taibc15-May-13 21:09

taibc

15-May-13 21:09

Hi everyone,

I have a table with the name is: Detail, that includes 'DateOfBirth' column (datetime type). For example: 1986-06-16 00:00:00.000

Now I want to create a store procedure to swap "day" value and "month" value for date values that have "day" and "month" value smaller 13, such as :

Origin value: 1984-05-10 (10th May)
Expected value: 1984-10-05 (5th Oct)

Do you know how can do that ?

Thanks and regards,

Tai

Re: Create a procedure to change date value of a column in a table ? (SQL)

Corporal Agarn16-May-13 1:18

Corporal Agarn

16-May-13 1:18

This is not tested and it is not the only way to do this:

SQL

UPDATE MYTABLE
SET DOB = CAST(CAST(YEAR(DOB) AS VARCHAR(4)) + 
               CAST(DAY(DOB) AS VARCHAR(2)) + 
               CAST(MONTH(DOB)AS VARCHAR(2)) AS DATETIME) + 
               CAST(DOB AS TIME)

Re: Create a procedure to change date value of a column in a table ? (SQL)

taibc16-May-13 3:41

taibc

16-May-13 3:41

Thank you.

I got an error when trying your code:

"Type TIME is not a defined system type"

I am using Microsoft SQL Server

Re: Create a procedure to change date value of a column in a table ? (SQL)

Corporal Agarn16-May-13 3:49

Corporal Agarn

16-May-13 3:49

I use TIME with SQL2008. You can use the CONVERT command to give you the time portion of the DATETIME column as a VARCHAR. (Look at 108)

Re: Create a procedure to change date value of a column in a table ? (SQL)

Eddy Vluggen16-May-13 3:16

Re: Create a procedure to change date value of a column in a table ? (SQL)

16-May-13 3:16

taibc wrote:
Do you know how can do that ?

Using string-manipulation functions. The problem here is not the format of your date, but the fact that it's stored as a string, not as a date.

What are you going to do with the date "6 may"? Would that become "5 juli", or are you going to "hope" that it is the correct date (that's what you state; you'd like to skip those)?

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

taibc16-May-13 3:34

taibc

16-May-13 3:34

Thank you.

Yes, my expected result is 5 June.

I am not good with fuctions in sql, do you have any ideas ?

Kind regards,

Re: Create a procedure to change date value of a column in a table ? (SQL)

Eddy Vluggen16-May-13 3:48

Looking for the best solution

16-May-13 3:48

taibc wrote:
Yes, my expected result is 5 June.

That's not what you stated in your original question; take a look, you wanted only those where you could "invalidate" the date by having a "day" larger then the number of months. 5 may would be skipped if you do it like that.

taibc wrote:
I am not good with fuctions in sql, do you have any ideas ?

Convert it to a date-column, as it should have been. It's either that, or trying your luck at string-manipulation. You'll have to write the code yourself btw, I'm not going to.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

MacAriel15-May-13 8:40

MacAriel

15-May-13 8:40

Hello to all,

I'm new to this forum and a few days ago I started to learn web design.

I have a porjet to start in September and I am looking to make effective technical.

I have to send daily sms to customers and I diffenrents around 25,000 customers each with 25,000 different phone numbers and different addresses.

My socui is to do all this from a website.

I want to send emails to smss and internet party.

I will create a database and sales agents will charge to send the data by group. Chauqe day I have to meet about 20 000 people.

So my problem is what solution I need? I mean how to make it be effective?

- I want my system to be independent, I do not want to depend on a GSM operator here, it will not be possible given the daily flow and no operator still offers unlimited sms offers here (I am in West Africa ).
- I would like for example to specify the sender according to the groups of my clients.

I am ready to buy equipment, I made a few searches on the net and I saw devices like sms gateway but I do not know how these devices work.

Is it possible to do this with php? and be totally independent?

Is there a solution where I can use the services of an operator in Europe or Canada or state States?

I want my system to be truly independent as far as possible.

What equipment do I need?
How to proceed?

I hope I have your good detailed answers.

Deploying SSIS Package and calling it from Console application

indian14315-May-13 7:28

indian143

15-May-13 7:28

Hi,

I have an SSIS package which does some Database functionalities, I want to deploy that package as just .dtsx file on to some servers filesystem and call it from a schduled task.

Can I just copy the .dtsx file from the solution folder and put it on some server and execute it from .Net code.

If not how to deploy an SSIS package on to filesystem as just .dtsx file and call it from .net code to finish its functionalities.

I am also searching on the internet but any kind help like code snippet, suggestion or any link would be appreciated.

Thanks in advance.

Thanks & Regards,

Abdul Aleem Mohammad
St Louis MO - USA

Re: Deploying SSIS Package and calling it from Console application

unclepaul15-May-13 8:47

unclepaul

15-May-13 8:47

See this article http://blog.sqlauthority.com/2011/05/21/sql-server-running-ssis-package-from-command-line/[^]

Caveat: Each machine where the package is to be run must have a SSIS Installation which requires a SQL server license. This can get expensive.

If at first you don't succeed, look for directions in the trash.

Re: Deploying SSIS Package and calling it from Console application

Mycroft Holmes15-May-13 18:00

Create trigger that executes on date

15-May-13 18:00

What is it with you people today, the previous bloke wanted to use a trigger, you want to deploy a dtx and schedule it with some tool. USE A SQL JOB. That is precisely what it is designed for! there are a whole bunch of stuff available [^]

Never underestimate the power of human stupidity
RAH

damokk15-May-13 3:54

damokk

15-May-13 3:54

Hi. I am creating an application that uses sqlserver and asp.net c#. I need to create a trigger that inserts data into a table 30 days before an expiry date field in another table. I have created the trigger but dont know how to implement the dates? Any suggestions would be appreciated.

Re: Create trigger that executes on date

Corporal Agarn15-May-13 6:31

Corporal Agarn

15-May-13 6:31

Does the date go in when the "expiry date" column is populate or do you want it to go in 30 days before the date?

Re: Create trigger that executes on date

Mycroft Holmes15-May-13 17:55