Convertion ORACLE query to MYSQL
Hi Experts,
We are in the process of converting Oracle administration commands into MySQL
for some purposes. We dont know how to convert the following Oracle commands to
MySQL. How to do that..?
1. ALTER USER spec TEMPORARY TABLESPACE temp_ts;
2. DROP USER jbossjms1 CASCADE;
3. DROP TABLESPACE jbossjms1 INCLUDING CONTENTS;
4. CREATE TABLESPACE jbossjms1DATAFILE '${JBOSSJMS1}' SIZE 100M REUSE
AUTOEXTEND ON MAXSIZE UNLIMITED;
5. CREATE USER jbossjms1 IDENTIFIED BY jbossjms1DEFAULT TABLESPACE jbossjms1;
Thanks in Advance,
ViSolve PlanCAT Team
Re: Convertion ORACLE query to MYSQL
These commands are Oracle specific or contains Oracle specific
extensions. Nothing wrong with that, but in some cases there just is no
corresponding command in MySQL, as the concepts are different.
ViSolve DB Team wrote:
Hi Experts,
We are in the process of converting Oracle administration commands into MySQL
for some purposes. We dont know how to convert the following Oracle commands to
MySQL. How to do that..?
1. ALTER USER spec TEMPORARY TABLESPACE temp_ts;
In MySQL there are no tablespaces in general, although certain storage
engines use them. But they have completely different properties from
what is the case with Oracle. Also, there is no such thing as a specific
temporary tablespace.
2. DROP USER jbossjms1 CASCADE;
DROP USER works fine in MySQL. In Oracle, there is a specific connection
between a User and the Schema, or rather, they are the same. In MySQL,
these are different, there is a schema (or in MySQL, a database) and
then there is granted access to that schema, that is it, there is no
specific ownership of a schema. Assuming you set up MySQL the same way
as Oracle, that each user (jbossjms1) gets his own schema (jbossjms1)
and you want to drop both of those, in MySQL you would:
DROP DATABASE jbossjms1;
DROP USER jbossjms1;
3. DROP TABLESPACE jbossjms1 INCLUDING CONTENTS;
As there is a DROP TABLESPACE command in MySQL 5.1, but that does not
with all certainty do what you want it to. The closest command is
probably DROP DATABASE, but that assumes that you have all the jbossjms1
objects in that database. I'd be careful here though, and read up on
these commands in both Oracle and MySQL before you do this, as this
might, and again might not, do what you want. These are admin commands,
which typically work differently in different RDBMS systems.
4. CREATE TABLESPACE jbossjms1DATAFILE '${JBOSSJMS1}' SIZE 100M REUSE
AUTOEXTEND ON MAXSIZE UNLIMITED;
This is again an administration commend. Assuming you are using the
InnoDB storage engine, this command corresponds to
innodb_data_file_path setting in the MySQL configuration file (my.cnf
/ my.ini etc).
5. CREATE USER jbossjms1 IDENTIFIED BY jbossjms1DEFAULT TABLESPACE jbossjms1;
This command works similarly same in MySQL, with the exception that
there is no concept of a DEFAULT TABLESPACE in MySQL.
Thanks in Advance,
ViSolve PlanCAT Team
--
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ / Anders Karlsson ([EMAIL PROTECTED])
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Sales Engineer
/_/ /_/\_, /___/\___\_\___/ Stockholm
___/ www.mysql.com Cellphone: +46 708 608121
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Convertion ORACLE query to MYSQL
On 11/9/06, ViSolve DB Team [EMAIL PROTECTED] wrote:
Hi Experts,
We are in the process of converting Oracle administration commands into MySQL
for some purposes. We dont know how to convert the following Oracle commands to
MySQL. How to do that..?
1. ALTER USER spec TEMPORARY TABLESPACE temp_ts;
There's no equivalent concept in MySQL to a temp tablespace. The key
with them in Oracle is actions performed in a temp tablespace aren't
logged (sorts and hashes that overflow the memory), and the files that
constitute them aren't required for recovery.
2. DROP USER jbossjms1 CASCADE;
No equivalent to the cascade option ... so create a script-generating
script, or drop the database if you've equated schema with database.
3. DROP TABLESPACE jbossjms1 INCLUDING CONTENTS;
You normally don't need to muck around with a storage engine's
low-level tablespace management. You might want to work out what
objects this would drop, and do that instead.
4. CREATE TABLESPACE jbossjms1DATAFILE '${JBOSSJMS1}' SIZE 100M REUSE
AUTOEXTEND ON MAXSIZE UNLIMITED;
Ensure the files in your innodb_data_file_path have the autoextend
property set, and total atleast 100MB in size. The Oracle syntax
above uses ASM-based storage, which has no equivalent in MySQL, so
forget that bit
5. CREATE USER jbossjms1 IDENTIFIED BY jbossjms1DEFAULT TABLESPACE jbossjms1;
As per normal user creation ... just ignore the tablespace bit.
Ciao
Fuzzy
:-)
--
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. 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]
Oracle query..
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 -- 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]
Oracle query to mysql
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 +---++++++ | 803xx | 2004-09-02 | 1 | 4.30 | 4.30 | 1 | | 803xx | 2004-09-01 | 2 | 0.00 | 0.00 | 2 | +---++++++ I took out some fields in this graphic just to simplify. I've been trying to get this query running for mysql but I can get the same results and I kill the DB. Does someone knows if I can get the same result ? Thanks is advance, MARTIN [EMAIL PROTECTED] -- 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
How can this Oracle Query converted to MySQL
SELECT U.UserID FROM Transaction_Data T, Rfq_Data R ,Company C WHERE T.TransactionID = R.TransactionID AND (R.Industryid=1 or R.IndustryID IN (SELECT IndustryID FROM Company_Industries CI WHERE CI.CompanyID = C.CompanyID)) This is to Pass the Mail Server : sql,query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: How can this Oracle Query converted to MySQL
Not directly, MySql does not support sub selects yet. HTH Peter --- Excellence in internet and open source software --- Sunmaia www.sunmaia.net [EMAIL PROTECTED] tel. 0121-242-1473 --- -Original Message- From: Arul [mailto:[EMAIL PROTECTED]] Sent: 26 June 2002 10:33 To: MySQL Cc: [EMAIL PROTECTED] Subject: How can this Oracle Query converted to MySQL SELECT U.UserID FROM Transaction_Data T, Rfq_Data R ,Company C WHERE T.TransactionID = R.TransactionID AND (R.Industryid=1 or R.IndustryID IN (SELECT IndustryID FROM Company_Industries CI WHERE CI.CompanyID = C.CompanyID)) This is to Pass the Mail Server : sql,query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How can this Oracle Query converted to MySQL
Hi. First, I removed the CC to the java list, because this problem is not java-related in any way. Second, I suggest you do some reading in the manual, the questions you asked recently are mostly answered there. On Wed 2002-06-26 at 15:03:19 +0530, [EMAIL PROTECTED] wrote: SELECT U.UserID FROM Transaction_Data T, Rfq_Data R ,Company C WHERE T.TransactionID = R.TransactionID AND (R.Industryid=1 or R.IndustryID IN (SELECT IndustryID FROM Company_Industries CI WHERE CI.CompanyID = C.CompanyID)) I started to try to rewrite the query to sub-selects, when I noticed that it is broken. You have no table aliased to U, so U.UserID makes no sense. And it looks as if an restriction on Company is missing (else, the sub-query simply select all IndustrIDs in Company_Industries). Bye, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How can this Oracle Query converted to MySQL
What us U.UserID? You don't have table U in the table list. Arul wrote: SELECT U.UserID FROM Transaction_Data T, Rfq_Data R ,Company C WHERE T.TransactionID = R.TransactionID AND (R.Industryid=1 or R.IndustryID IN (SELECT IndustryID FROM Company_Industries CI WHERE CI.CompanyID = C.CompanyID)) -- For technical support contracts, visit https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Alexander Barkov [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer /_/ /_/\_, /___/\___\_\___/ Izhevsk, Russia ___/ www.mysql.com +7-902-856-80-21 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How can this Oracle Query converted to MySQL
At 03:03 PM 6/26/02 +0530, Arul wrote: SELECT U.UserID FROM Transaction_Data T, Rfq_Data R ,Company C WHERE T.TransactionID = R.TransactionID AND (R.Industryid=1 or R.IndustryID IN (SELECT IndustryID FROM Company_Industries CI WHERE CI.CompanyID = C.CompanyID)) You ran this query through Oracle? I was going to reformulate this query using UNION (because of the OR), but looking closer I couldn't make sense of it ... + Your SELECT list references U.UserId, but I couldn't find table 'U' in your FROM list. What does that mean? + The table -- Company, in your FROM list is not joined with anything except in the subquery. I fail to see how the query could produce proper results. -- Lee Fesperman, FirstSQL, Inc. (http://www.firstsql.com) == * The Ultimate DBMS is here! * FirstSQL/J Object/Relational DBMS (http://www.firstsql.com) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: How can this Oracle Query converted to MySQL
In a nutshell, this can be rewritten as a join - no sub-select needed, so no problem (this is true of the vast majority - albeit not all - sub-selects). Because the U table is missing (indeed, it looks like a lot of your join informaiton is missing too), there's no way for me to give you an accurate rewrite as a JOIN, but make Company_Industries a table that you join to, join it to Company on CompanyID, and add the IndustryID conditions and you're set. Rob -Original Message- From: Benjamin Pflugmann [mailto:[EMAIL PROTECTED]] Sent: Wednesday, June 26, 2002 1:04 PM To: Arul Cc: MySQL Subject: Re: How can this Oracle Query converted to MySQL Hi. First, I removed the CC to the java list, because this problem is not java-related in any way. Second, I suggest you do some reading in the manual, the questions you asked recently are mostly answered there. On Wed 2002-06-26 at 15:03:19 +0530, [EMAIL PROTECTED] wrote: SELECT U.UserID FROM Transaction_Data T, Rfq_Data R ,Company C WHERE T.TransactionID = R.TransactionID AND (R.Industryid=1 or R.IndustryID IN (SELECT IndustryID FROM Company_Industries CI WHERE CI.CompanyID = C.CompanyID)) I started to try to rewrite the query to sub-selects, when I noticed that it is broken. You have no table aliased to U, so U.UserID makes no sense. And it looks as if an restriction on Company is missing (else, the sub-query simply select all IndustrIDs in Company_Industries). Bye, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How can this Oracle Query converted to MySQL
create temporary table tmp SELECT IndustryID FROM Company_Industries CI WHERE CI.CompanyID = C.CompanyID; SELECT U.UserID FROM Transaction_Data T, Rfq_Data R ,Company C WHERE T.TransactionID = R.TransactionID AND (R.Industryid=1 or R.IndustryID=tmp.IndustryID) Cheers Rich Arul wrote: SELECT U.UserID FROM Transaction_Data T, Rfq_Data R ,Company C WHERE T.TransactionID = R.TransactionID AND (R.Industryid=1 or R.IndustryID IN (SELECT IndustryID FROM Company_Industries CI WHERE CI.CompanyID = C.CompanyID)) This is to Pass the Mail Server : sql,query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How can this Oracle Query converted to MySQL
Hi All Sorry Guys...I just cut a small part of a bi Query where i had problems but i didnt see the User Table missing in the From List :(( Thanx for your Help.. -Arul - Original Message - From: Rob Vieira [EMAIL PROTECTED] To: 'Benjamin Pflugmann' [EMAIL PROTECTED]; Arul [EMAIL PROTECTED] Cc: MySQL [EMAIL PROTECTED] Sent: Thursday, June 27, 2002 5:42 AM Subject: RE: How can this Oracle Query converted to MySQL In a nutshell, this can be rewritten as a join - no sub-select needed, so no problem (this is true of the vast majority - albeit not all - sub-selects). Because the U table is missing (indeed, it looks like a lot of your join informaiton is missing too), there's no way for me to give you an accurate rewrite as a JOIN, but make Company_Industries a table that you join to, join it to Company on CompanyID, and add the IndustryID conditions and you're set. Rob -Original Message- From: Benjamin Pflugmann [mailto:[EMAIL PROTECTED]] Sent: Wednesday, June 26, 2002 1:04 PM To: Arul Cc: MySQL Subject: Re: How can this Oracle Query converted to MySQL Hi. First, I removed the CC to the java list, because this problem is not java-related in any way. Second, I suggest you do some reading in the manual, the questions you asked recently are mostly answered there. On Wed 2002-06-26 at 15:03:19 +0530, [EMAIL PROTECTED] wrote: SELECT U.UserID FROM Transaction_Data T, Rfq_Data R ,Company C WHERE T.TransactionID = R.TransactionID AND (R.Industryid=1 or R.IndustryID IN (SELECT IndustryID FROM Company_Industries CI WHERE CI.CompanyID = C.CompanyID)) I started to try to rewrite the query to sub-selects, when I noticed that it is broken. You have no table aliased to U, so U.UserID makes no sense. And it looks as if an restriction on Company is missing (else, the sub-query simply select all IndustrIDs in Company_Industries). Bye, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
