First of all a kind salute to the master yoda of SQL query's Shawn Green :)
He saved me a couple of hours the other day with a linking a table to itself
query !
I've been in the Telecommunications industry for more than 2 years now. I
hope that I can provide feedback any questions regarding DB
knowledge/query's in this area for this list.
Now to the point. I'm researching how to do another query that I 've in the
Oracle DB on pl/SQL.
The task is to provide a report based on the call origin. We get last 4
numbers of each call origin and from that we can determine where the call
was originated (in spain: 6% is mobile, 913% is madrid, and so on). My
mission is to create something that will use the less possible resources for
each query.
This is IMDTAB:
+------------+-----------+--------------+--------+--------------------+-----
---+--------+
| IMDFLL | IMD906 | IMDTER | IMDTAR | IMDDUR |
IMDORI | IMDCLI |
+------------+-----------+--------------+--------+--------------------+-----
---+--------+
| 2004-08-01 | 80xxxxxxx | 913xxxxxx | R | 2.283333333333330 |
6277 | 01 |
| 2004-08-01 | 80xxxxxxx | 913xxxxxx | R | 2.650000000000000 |
6290 | 01 |
| 2004-08-01 | 80xxxxxxx | 913xxxxxx | R | 8.316666666666670 |
9710 | 01 |
In Oracle using PL /Sql I 've a select that looks like this:
SELECT GRUPO_ORI(IMDTAB.IMDORI) AS ORI, COUNT(IMDTAB.IMDORI) AS CLL FROM
IMDTAB
WHERE
(IMDTAB.IMRCLI =01 AND (IMD906=80xxxxxxx )) AND (IMDTAB.IMDFLL =
'@date1' )
GROUP BY IMDTAB.IMDCLI,GRUPO_ORI(IMDTAB.IMDORI)
And this is the GRUPO_ORI function:
FUNCTION "GRUPO_ORI" (ORI IN IMDTAB.IMDORI%TYPE)
RETURN VARCHAR2
IS
STRORI CLITAB.CLIPOB%TYPE;
GRPORI CLITAB.CLIPOB%TYPE;
BEGIN
STRORI := To_Char(ORI);
GRPORI := SUBSTR(STRORI,1,1);
IF GRPORI = '6' THEN
return GRPORI;
END IF;
GRPORI := SUBSTR(STRORI,1,2);
IF GRPORI = '91' OR GRPORI = '93' THEN
return GRPORI;
END IF;
GRPORI := SUBSTR(STRORI,1,3);
IF GRPORI = '920' OR GRPORI = '921' OR GRPORI = '922' OR GRPORI = '923' OR
GRPORI = '924'
OR GRPORI = '925' OR GRPORI = '926' OR GRPORI = '927' OR GRPORI = '928' OR
GRPORI = '941'
OR GRPORI = '942' OR GRPORI = '943' OR GRPORI = '945' OR GRPORI = '947' OR
GRPORI = '948'
OR GRPORI = '949' OR GRPORI = '950' OR GRPORI = '953' OR GRPORI = '956' OR
GRPORI = '957'
OR GRPORI = '958' OR GRPORI = '959' OR GRPORI = '964' OR GRPORI = '967' OR
GRPORI = '968'
OR GRPORI = '969' OR GRPORI = '971' OR GRPORI = '972' OR GRPORI = '973' OR
GRPORI = '974'
OR GRPORI = '975' OR GRPORI = '976' OR GRPORI = '977' OR GRPORI = '978' OR
GRPORI = '979'
OR GRPORI = '980' OR GRPORI = '981' OR GRPORI = '982' OR GRPORI = '983' OR
GRPORI = '986'
OR GRPORI = '986' OR GRPORI = '987' OR GRPORI = '988'
THEN
return GRPORI;
END IF;
return 'Another';
END GRUPO_ORI;
I was thinking in doing this by query, striping the IMDORI to 3 numbers, but
I 've also as seen in the code copied above also 1 number, and 2 numbers
zone identifiers.
Also I can build a table with this instead of doing all the IF's, and join
depending on the starting numbers, but looks rather complicated and I think
the query might use a lot of DB power (and as described, I want to use the
less possible resources taking in account this is a CALL BY CALL table with
***lots*** of rows)
So what do you think guys is the best way to go ?
Thanks is advance,
MARTIN
Statistics Department
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
