Hi,
Could any one of you suggest me a better way to bump the data in oracle 7.3 to
mysql 4.0 classic.
Thanks,
Narasimha
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Tue 9/28/2004 8:58 PM
To: martin fasani
Cc: [EMAIL PROTECTED]
Subject: 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=803xxxxxx ))
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=803xxxxxx
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/yyyy) =>> '2004-09-01' (yyyy-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 | 803xxxxxx | xxxxxxxxx | N |
446.916666666666572 |
> 40355904 | 21 | 26 |
> | 2004-06-01 | 803xxxxxx | 0 | R | 9.416666666666664
|
> 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=803xxxxxx )) 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
> +-----------+------------+--------+--------+--------+--------+
> | 803xxxxxx | 2004-09-02 | 1 | 4.30 | 4.30 | 1 |
> | 803xxxxxx | 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]
>
Confidentiality Notice
The information contained in this electronic message and any attachments to this
message are intended
for the exclusive use of the addressee(s) and may contain confidential or privileged
information. If
you are not the intended recipient, please notify the sender at Wipro or [EMAIL
PROTECTED] immediately
and destroy all copies of this message and any attachments.
