Home » SQL & PL/SQL » SQL & PL/SQL » data manupulation (oracle10g,10.20,IBM)
data manupulation [message #295795] Wed, 23 January 2008 08:25 Go to next message
kekanap
Messages: 18
Registered: January 2008
Location: Centurion
Junior Member

Hallo friends,

This is my scripts below:

SET LINES 135
SET PAGES 1000

SET UNDERLINE '='

SET FEEDBACK OFF
VARIABLE TheDate VARCHAR2(30);

ALTER SESSION SET NLS_DATE_FORMAT ='MM/DD/YY';


BREAK ON FDE_TYPE SKIP 1 ON TOT_CREATED ON CANCELLED_TYPE ON OPEN_TICKETS ON TOT_NUM_CLOSED ON NUM_OPENED ON NUM_DEFERRED
SET ECHO OFF

COLUMN FDE_TYPE HEADING 'FDE FRAUD TYPE' FORMAT A30 WOR NULL '-'
COLUMN TOT_CREATED HEADING 'RAISED|TICKETS' NULL '-'
COLUMN CANCELLED_TYPE HEADING 'CANCELLED|TICKETS' NULL '-'
COLUMN NUM_OPENED HEADING 'OPENED|TICKETS' NULL '-'
COLUMN NUM_DEFERRED HEADING 'DEFERRED|TICKETS' NULL '-'
COLUMN TOT_NUM_CLOSED HEADING 'TOTAL|TICKETS|CLOSED' NULL '-'
COLUMN CM_TYPE HEADING 'CLOSED FRAUD TYPE' FORMAT A30 WOR NULL '-'
COLUMN NUM_CLOSED_EACH_FRAUD_TYPE HEADING 'NUM|CLOSED' NULL '-'
COLUMN PERCENT_OF_TOTAL HEADING '% OF|TOTAL|CLOSED' FORMAT 999.99 NULL '-'

SPOOL TICKET_STATS_BY_FRAUD_TYPE.OUT

ACCEPT AskFromDate DATE FORMAT 'MM/DD/YY' PROMPT 'Enter date from which stats are taken (MM/DD/YY) >';
EXECUTE :TheDate := '&AskFromDate';


TTITLE CENTER 'TICKET STATS BY FRAUD TYPE' SKIP 1 -
CENTER '-----------------------------' SKIP 1


SELECT A.FDE_TYPE, B.TOT_CREATED, C.CANCELLED_TYPE, F.NUM_OPENED, G.NUM_DEFERRED, D.TOT_NUM_CLOSED, E.CM_TYPE, E.NUM_CLOSED_EACH_FRAUD_TYPE,
(E.NUM_CLOSED_EACH_FRAUD_TYPE / D.TOT_NUM_CLOSED) * 100 AS PERCENT_OF_TOTAL FROM
--
-- All Fraud Types
--
(SELECT FRAUD_TYPE_ID AS FDE_TYPE FROM IAA_LK_FRAUD_TYPE) A,
--
-- Raised Tickets
--
(SELECT FDE_TYPE, SUM(RAISED_TICKETS) AS TOT_CREATED FROM
((SELECT FDE_FRAUD_TYPE AS FDE_TYPE, COUNT(1) AS RAISED_TICKETS
FROM (SELECT * FROM CM_TICKET WHERE CREATION_DATE > :TheDate
UNION SELECT * FROM CM_TICKET_ARCHIVE WHERE CREATION_DATE > :TheDate)
GROUP BY FDE_FRAUD_TYPE) UNION ALL
(SELECT FDE_FRAUD_TYPE AS FDE_FRAUD_TYPE, COUNT(1) AS RAISED_TICKETS FROM CM_CANCELLED_TICKETS
WHERE CREATED_DATE > :TheDate GROUP BY FDE_FRAUD_TYPE)) GROUP BY FDE_TYPE) B,
--
-- Cancelled Tickets
--
(SELECT FDE_FRAUD_TYPE AS FDE_TYPE, COUNT(1) AS CANCELLED_TYPE
FROM CM_CANCELLED_TICKETS WHERE CANCELLED_DATE > :TheDate GROUP BY FDE_FRAUD_TYPE) C,
--
-- Total Number Closed Tickets
--
(SELECT A.FDE_FRAUD_TYPE AS FDE_TYPE, COUNT (1) AS TOT_NUM_CLOSED
FROM (SELECT * FROM CM_TICKET UNION SELECT * FROM CM_TICKET_ARCHIVE) A,
(SELECT CASE_HISTORY_ID,MAX(DATE_TIME) FROM
(SELECT * FROM CM_ACTIVITIES UNION SELECT * FROM CM_ACTIVITIES_ARCHIVE)
WHERE ACTION_ID = 15 GROUP BY CASE_HISTORY_ID HAVING MAX(DATE_TIME) > :TheDate) B
WHERE A.CASE_HISTORY_ID = B.CASE_HISTORY_ID
GROUP BY A.FDE_FRAUD_TYPE) D,
--
-- Closed for Each Fraud Type
--
(SELECT A.FDE_FRAUD_TYPE AS FDE_TYPE, A.CM_FRAUD_TYPE AS CM_TYPE, COUNT (1) AS NUM_CLOSED_EACH_FRAUD_TYPE
FROM (SELECT * FROM CM_TICKET UNION SELECT * FROM CM_TICKET_ARCHIVE) A,
(SELECT CASE_HISTORY_ID,MAX(DATE_TIME) FROM
(SELECT * FROM CM_ACTIVITIES UNION SELECT * FROM CM_ACTIVITIES_ARCHIVE)
WHERE ACTION_ID = 15 GROUP BY CASE_HISTORY_ID HAVING MAX(DATE_TIME) > :TheDate ) B
WHERE A.CASE_HISTORY_ID = B.CASE_HISTORY_ID
GROUP BY A.FDE_FRAUD_TYPE, A.CM_FRAUD_TYPE ) E,
--
-- Opened Tickets
--
(SELECT A.FDE_FRAUD_TYPE AS FDE_TYPE, COUNT (1) AS NUM_OPENED
FROM (SELECT * FROM CM_TICKET UNION SELECT * FROM CM_TICKET_ARCHIVE) A,
(SELECT CASE_HISTORY_ID,MAX(DATE_TIME) FROM
(SELECT * FROM CM_ACTIVITIES UNION SELECT * FROM CM_ACTIVITIES_ARCHIVE)
WHERE ACTION_ID = 1 GROUP BY CASE_HISTORY_ID HAVING MAX(DATE_TIME) > :TheDate) B
WHERE A.CASE_HISTORY_ID = B.CASE_HISTORY_ID
GROUP BY A.FDE_FRAUD_TYPE) F,
--
--DEFERRED TICKETS
--
(SELECT A.FDE_FRAUD_TYPE AS FDE_TYPE, COUNT (1) AS NUM_DEFERRED
FROM (SELECT * FROM CM_TICKET UNION SELECT * FROM CM_TICKET_ARCHIVE) A,
(SELECT CASE_HISTORY_ID,MAX(DATE_TIME) FROM
(SELECT * FROM CM_ACTIVITIES UNION SELECT * FROM CM_ACTIVITIES_ARCHIVE)
WHERE ACTION_ID = 6 GROUP BY CASE_HISTORY_ID HAVING MAX(DATE_TIME) > :TheDate) B
WHERE A.CASE_HISTORY_ID = B.CASE_HISTORY_ID
GROUP BY A.FDE_FRAUD_TYPE) G
WHERE A.FDE_TYPE = B.FDE_TYPE(+)
AND A.FDE_TYPE = C.FDE_TYPE(+)
AND A.FDE_TYPE = D.FDE_TYPE(+)
AND A.FDE_TYPE = E.FDE_TYPE(+)
AND A.FDE_TYPE = F.FDE_TYPE(+)
AND A.FDE_TYPE = G.FDE_TYPE(+);

CLEAR BREAKS
CLEAR COMPUTES
CLEAR COLUMNS

TTITLE OFF

SPOOL OFF

This is the output:

SQL> @TICKET_STATS_BY_FRAUD_TYPE.sql
Enter date from which stats are taken (MM/DD/YY) >01/22/08

TICKET STATS BY FRAUD TYPE
-----------------------------
TOTAL % OF
RAISED CANCELLED OPENED DEFERRED TICKETS NUM TOTAL
FDE FRAUD TYPE TICKETS TICKETS TICKETS TICKETS CLOSED CLOSED FRAUD TYPE CLOSED CLOSED
============================== ========== ========== ========== ========== ========== ============================== ========== =======
Scorecard 17 - - - - - - -

Overlapping Calls 6 - - - - - - -

Velocity Alarm - - - - - - - -

Hot List Alarm - - - - - - - -

Duration Patterns 259 30 - - - - - -

PD Summary alarm 1477 2 - - - - - -

Short Calls 1641 399 - - - - - -

Multiple IMEI 139 50 - - - - - -

High Aggregate Cost of Calls 180 85 - - - - - -

Absolute Feature Use - - - - - - - -

Advanced Services 517 125 - - - - - -

Multiple IMSI 2116 482 - - - - - -

Prepaid 6783 1771 - - - - - -

Very Long Duration Call 2447 201 - - - - - -

Manual Ticket - - - - - - - -

High Frequency 642 61 - - - - - -

High Value Calls 524 - - - - - - -

SQL>


I would like to include the table below called cm_user_groups into the script so that coloumn group_name from this table can be part of the output. Table cm_user_groups looks like this below :

SQL> desc cm_user_groups
Name Null? Type
----------------------------------------- -------- ----------------------------
GROUP_ID NOT NULL NUMBER(6)
GROUP_NAME NOT NULL VARCHAR2(30)
DESCRIPTION VARCHAR2(200)
IS_DISABLED CHAR(1)
DEFAULT_COMPONENT_ID NUMBER(6)

SQL> select group_name from cm_user_groups;

GROUP_NAME
------------------------------
<Auto Cancel>
<Auto Closure>
<Auto Counter-Measure>
<No User Group>
Administrators
Autopage
I-talk
MTN_SP
Master Group
Nashua

10 rows selected.


I would like the output that you are seeing above to be diveded between different group_names.

Please assist me.

Regards,

Paxley
Re: data manupulation [message #295796 is a reply to message #295795] Wed, 23 January 2008 08:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First, please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code.
Use the "Preview Message" button to verify.

Then what do you mean "diveded between different group_names"?

Regards
Michel
Re: data manupulation [message #295805 is a reply to message #295796] Wed, 23 January 2008 08:59 Go to previous messageGo to next message
kekanap
Messages: 18
Registered: January 2008
Location: Centurion
Junior Member

Hi Mr Cadot,

Lets take the output that I have attached. Note that score card has 17 tickets. This tickets can belong to any of the group names that i specified and thats is what i would like to show in the output file.

Do you understand? Can i call you and explain?
Re: data manupulation [message #295807 is a reply to message #295805] Wed, 23 January 2008 09:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just add group_names in your "group by" clause.

Regards
Michel
Re: data manupulation [message #295815 is a reply to message #295807] Wed, 23 January 2008 09:51 Go to previous messageGo to next message
kekanap
Messages: 18
Registered: January 2008
Location: Centurion
Junior Member

please show me what you mean.
Re: data manupulation [message #295819 is a reply to message #295815] Wed, 23 January 2008 10:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please reformat your post.

Regards
Michel
Re: data manupulation [message #295844 is a reply to message #295795] Wed, 23 January 2008 13:38 Go to previous message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
you are not using DATEs properly. You must use a TO_DATE function to convert a character string (you input) into a DATE. It is not a DATE the way you are using it.
Previous Topic: Not Catching Exception for ORA-06502
Next Topic: Remove '|' chraracter
Goto Forum:
  


Current Time: Sun Dec 04 06:08:56 CST 2016

Total time taken to generate the page: 0.17389 seconds