Re: Oracle query..
Renish wrote: Hello, Sorry. Again u r wrong.. Thers no connection with MYSQL here. Well you're *very* much in the wrong list. Why are you posting to a MySQL list if your question has nothing to do with MySQL? You do realise there are Oracle lists, don't you? Surely people there will be able to give you more accurate advice than people here? -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Oracle query..
Thanks dear. i thought u could be of some use in Oracle aswelll - Original Message - From: Daniel Kasak [EMAIL PROTECTED] To: Renish [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Friday, October 20, 2006 1:59 PM Subject: Re: Oracle query.. Renish wrote: Hello, Sorry. Again u r wrong.. Thers no connection with MYSQL here. Well you're *very* much in the wrong list. Why are you posting to a MySQL list if your question has nothing to do with MySQL? You do realise there are Oracle lists, don't you? Surely people there will be able to give you more accurate advice than people here? -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Oracle query..
Renish wrote: Hi All, I have oracle 7.3 data available with me..I see many *.ora files and one Import _Rawdata.bat file. . I dont know which one to import. Now I have installed, Oracle 10 g version server and client. Could you pls tell me. how can I import those data into the Oracle 10g so that I could see all the tables. For eample..when I have an SQl file(*.sdl) I used to import it in dos mode like Mysql4.1 binmysql .. .sql -p -u root; Thank you very much. Regards, Renish There are probably migration tools available to move your data. Check on the MySQL website. If there are tools, I would recommend using them. If you want to do it yourself, you need to export your data from Oracle before importing it into MySQL. Dump each table to a comma-delimited file, and use 'load data infile' to import it into MySQL. Read up on 'load data infile' to find the exact format and commands to use. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Oracle query..
Hi Mr Kasak think you misunderstood my question I dont want to Import to MYSQL. I want to import the data into Oracle 10 G. AS I said I have orcle 7.3 data already available with me.. Please tell me in step by step what i should do? as I am new to this field. I appreciate ur time in this matter. Cheers, Renish Koshy - Original Message - From: Daniel Kasak [EMAIL PROTECTED] To: Renish [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Friday, October 20, 2006 11:50 AM Subject: Re: Oracle query.. Renish wrote: Hi All, I have oracle 7.3 data available with me..I see many *.ora files and one Import _Rawdata.bat file. . I dont know which one to import. Now I have installed, Oracle 10 g version server and client. Could you pls tell me. how can I import those data into the Oracle 10g so that I could see all the tables. For eample..when I have an SQl file(*.sdl) I used to import it in dos mode like Mysql4.1 binmysql .. .sql -p -u root; Thank you very much. Regards, Renish There are probably migration tools available to move your data. Check on the MySQL website. If there are tools, I would recommend using them. If you want to do it yourself, you need to export your data from Oracle before importing it into MySQL. Dump each table to a comma-delimited file, and use 'load data infile' to import it into MySQL. Read up on 'load data infile' to find the exact format and commands to use. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Oracle query..
Renish wrote: Hi Mr Kasak think you misunderstood my question I dont want to Import to MYSQL. I want to import the data into Oracle 10 G. AS I said I have orcle 7.3 data already available with me.. Please tell me in step by step what i should do? as I am new to this field. I appreciate ur time in this matter. Ah. Well you're asking the wrong list then. But the general idea is the same. First I would look for a migration tool. Failing that, I would export each table to csv files, create the destination tables in Oracle, and then import from the csv files. But again, migration tools are going to be the best bet, as they will set up your table definitions. There is a *remote* chance that Oracle will be able to import from a 'mysqldump' backup. You can also check that out, but I doubt it will work somehow. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Oracle query..
Hello, Sorry. Again u r wrong.. Thers no connection with MYSQL here. Lets imagine I only have Oracle 7.3 data and then import the same data into Oracle 10g I hope this helps Renisn - Original Message - From: Daniel Kasak [EMAIL PROTECTED] To: Renish [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Friday, October 20, 2006 1:39 PM Subject: Re: Oracle query.. Renish wrote: Hi Mr Kasak think you misunderstood my question I dont want to Import to MYSQL. I want to import the data into Oracle 10 G. AS I said I have orcle 7.3 data already available with me.. Please tell me in step by step what i should do? as I am new to this field. I appreciate ur time in this matter. Ah. Well you're asking the wrong list then. But the general idea is the same. First I would look for a migration tool. Failing that, I would export each table to csv files, create the destination tables in Oracle, and then import from the csv files. But again, migration tools are going to be the best bet, as they will set up your table definitions. There is a *remote* chance that Oracle will be able to import from a 'mysqldump' backup. You can also check that out, but I doubt it will work somehow. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Oracle query..
Renish wrote: Hello, Sorry. Again u r wrong.. Thers no connection with MYSQL here. Lets imagine I only have Oracle 7.3 data and then import the same data into Oracle 10g He's saying you need to talk to an oracle mailing list or forum. We don't use oracle so we can't help you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Oracle query..
Thank u for ur speedy reply. - Original Message - From: Chris [EMAIL PROTECTED] To: Renish [EMAIL PROTECTED] Cc: Daniel Kasak [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Friday, October 20, 2006 1:51 PM Subject: Re: Oracle query.. Renish wrote: Hello, Sorry. Again u r wrong.. Thers no connection with MYSQL here. Lets imagine I only have Oracle 7.3 data and then import the same data into Oracle 10g He's saying you need to talk to an oracle mailing list or forum. We don't use oracle so we can't help you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Oracle query to mysql
Your original Oracle(R) query (slightly reformatted): SELECT IMRTAB.IMR906 AS NUM906 , IMRTAB.IMRFLL AS FLL , SUM(IMRTAB.IMRCLL) AS CLL , ROUND(SUM(IMRTAB.IMRDSC),2) AS DUR , ROUND(SUM(IMRTAB.IMRDSC)/SUM(IMRTAB.IMRCLL),2) AS PRO , SUM(IMRTAB1.IMRCLL) AS CLL_N , ROUND(SUM(IMRTAB1.IMRDSC),2) AS DUR_N , ROUND(SUM(IMRTAB1.IMRDSC)/SUM(IMRTAB1.IMRCLL),2) AS PRO_N , SUM(IMRTAB2.IMRCLL) AS CLL_R , ROUND(SUM(IMRTAB2.IMRDSC),2) AS DUR_R , ROUND(SUM(IMRTAB2.IMRDSC)/SUM(IMRTAB2.IMRCLL),2) AS PRO_R FROM IMRTAB , IMRTAB IMRTAB1 , IMRTAB IMRTAB2 /* here does the tables alias*/ WHERE IMRTAB.IMRIDE = IMRTAB1.IMRIDE (+) AND IMRTAB.IMRIDE = IMRTAB2.IMRIDE (+) /* links by the uniqID both alias */ AND (IMRTAB1.IMRTAR (+) = 'N') /* takes N calls and discard the rest for IMRTAB1 */ AND (IMRTAB2.IMRTAR (+) = 'R') /* takes R calls and discard the rest for IMRTAB2 */ AND (IMRTAB.IMRFLL BETWEEN '01/09/2004' and '10/09/2004') AND (IMRTAB.IMRCLI=2584 AND (IMRTAB.IMR906=803xx )) GROUP BY IMRTAB.IMR906,IMRTAB.IMRFLL ORDER BY IMRTAB.IMR906,IMRTAB.IMRFLL My MySQL translation: SELECT IMRTAB.IMR906 AS NUM906 , IMRTAB.IMRFLL AS FLL , SUM(IMRTAB.IMRCLL) AS CLL , ROUND(SUM(IMRTAB.IMRDSC),2) AS DUR , ROUND(SUM(IMRTAB.IMRDSC)/SUM(IMRTAB.IMRCLL),2) AS PRO , SUM(IMRTAB1.IMRCLL) AS CLL_N , ROUND(SUM(IMRTAB1.IMRDSC),2) AS DUR_N , ROUND(SUM(IMRTAB1.IMRDSC)/SUM(IMRTAB1.IMRCLL),2) AS PRO_N , SUM(IMRTAB2.IMRCLL) AS CLL_R , ROUND(SUM(IMRTAB2.IMRDSC),2) AS DUR_R , ROUND(SUM(IMRTAB2.IMRDSC)/SUM(IMRTAB2.IMRCLL),2) AS PRO_R FROM IMRTAB LEFT JOIN IMRTAB IMRTAB1 ON IMRTAB.IMRIDE = IMRTAB1.IMRIDE AND IMRTAB1.IMRTAR = 'N' /* N calls only for IMRTAB1 */ LEFT JOIN IMRTAB IMRTAB2 ON IMRTAB.IMRIDE = IMRTAB2.IMRIDE AND IMRTAB2.IMRTAR = 'R' /* R calls only for IMRTAB2 */ WHERE IMRTAB.IMRFLL BETWEEN '2004-09-01' and '2004-09-10' AND IMRTAB.IMRCLI=2584 AND IMRTAB.IMR906=803xx GROUP BY IMRTAB.IMR906,IMRTAB.IMRFLL ORDER BY IMRTAB.IMR906,IMRTAB.IMRFLL You were using the Oracle syntax , ...(+) to declare your outer joins. The equivalent MySQL form is LEFT JOIN... ON http://dev.mysql.com/doc/mysql/en/JOIN.html I also had to reformat the dates in your WHERE clause to be MySQL formatted: '01/09/2004' (dd/mm/) = '2004-09-01' (-mm-dd) http://dev.mysql.com/doc/mysql/en/Date_and_time_types.html http://dev.mysql.com/doc/mysql/en/DATETIME.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine martin fasani [EMAIL PROTECTED] wrote on 09/28/2004 07:23:38 AM: Hi guys, I'm working in a telecom company that has Oracle for the call statistics. Now we export the daily stats to a remote mySql. The daily resume table looks like this: ++---+-++--+ --+++ | IMRFLL | IMR906| IMRTER | IMRTAR | IMRDUR | IMRFAC | IMRCLI | IMRCLL | ++---+-++--+ --+++ | 2004-06-01 | 803xx | x | N | 446.9166572 | 40355904 | 21 | 26 | | 2004-06-01 | 803xx | 0 | R | 9.414 | 40355904 | 21 | 10 | ++---+-++--+ --+++ What I need it's to get a report that joins the table to itself two times to get the Normal tarif ( IMRTAR=N) and the Reduced tarif (IMRTAR=R). In Oracle is done using Outer joins like this: SELECT IMRTAB.IMR906 AS NUM906,IMRTAB.IMRFLL AS FLL, SUM(IMRTAB.IMRCLL) AS CLL ,ROUND(SUM(IMRTAB.IMRDSC),2) AS DUR, ROUND(SUM(IMRTAB.IMRDSC)/SUM(IMRTAB.IMRCLL),2) AS PRO, SUM(IMRTAB1.IMRCLL) AS CLL_N,ROUND(SUM(IMRTAB1.IMRDSC),2) AS DUR_N,ROUND(SUM(IMRTAB1.IMRDSC)/SUM(IMRTAB1.IMRCLL),2) AS PRO_N, SUM(IMRTAB2.IMRCLL) AS CLL_R,ROUND(SUM(IMRTAB2.IMRDSC),2) AS DUR_R,ROUND(SUM(IMRTAB2.IMRDSC)/SUM(IMRTAB2.IMRCLL),2) AS PRO_R FROM IMRTAB, IMRTAB IMRTAB1, IMRTAB IMRTAB2 /* here does the tables alias */ WHERE IMRTAB.IMRIDE = IMRTAB1.IMRIDE (+) AND IMRTAB.IMRIDE = IMRTAB2.IMRIDE (+) /* links by the uniqID both alias */ AND (IMRTAB1.IMRTAR (+) = 'N') /* takes N calls and discard the rest for IMRTAB1 */ AND (IMRTAB2.IMRTAR (+) = 'R') /* takes R calls and discard the rest for IMRTAB2 */ AND (IMRTAB.IMRFLL BETWEEN '01/09/2004' and '10/09/2004') AND (IMRTAB.IMRCLI =2584 AND (IMRTAB.IMR906=803xx )) GROUP BY IMRTAB.IMR906,IMRTAB.IMRFLL ORDER BY IMRTAB.IMR906,IMRTAB.IMRFLL And this query returns something like this: +---++++++ | NUM906| FLL| CLL| DUR| DUR_N | CLL_N | DUR_R | CLL_R
