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]
>
