Re: Accelerating sp, again!!!
From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Tue, 24 Feb 2009 03:17:56 -0800 (PST)
Message-ID: <bc047eec-84f1-4bd7-932f-b8392f7b76f1_at_e18g2000vbe.googlegroups.com>
On Feb 23, 6:20 pm, Jorge Reyes <jorg_re..._at_hotmail.com> wrote:
> Hi people, this is a continuation of the post titled "ACCELERATING
> STORED PROCEDURE" from the last week, am sorry for the inconvenience
> that might cause them to have to read the previous post but its a long
> history as you can see, so the last post was very useful and thanks
> you so much for your answers, but it was a final question about the
> last response from Charles who give me this query:
>
> BEGIN
>
> SELECT A.CALL_SOURCE_REGID AS CLIENTE,
> D.vMIN_INVALID_COFETEL + SUM(1) AS vTOTAL_CDRS_CLIENTE,
> D.vMIN_INVALID_COFETEL AS vMIN_INVALID_COFETEL,
> SUM(1) AS vMIN_VALID_COFETEL,
> SUM(DECODE(B.MODALIDAD,'CPP',DECODE(SUBSTR(A.CALLED_PARTY_ON_DEST,
> 0,6),'140521',1,0),0)) AS vMOVILES_VALIDOS,
> SUM(DECODE(B.MODALIDAD,'CPP',DECODE(SUBSTR(A.CALLED_PARTY_ON_DEST,
> 0,6),'140521',0,1),0)) AS vMOVILES_INVALIDOS,
> SUM(DECODE(B.MODALIDAD,'FIJO',DECODE(SUBSTR(A.CALLED_PARTY_ON_DEST,
> 0,5),'14052',1,0),0)) +
> SUM(DECODE(B.MODALIDAD,'MPP',DECODE(SUBSTR(A.CALLED_PARTY_ON_DEST,
> 0,5),'14052',1,0),0)) AS vFIJOS_VALIDOS,
> SUM(DECODE(B.MODALIDAD,'FIJO',DECODE(SUBSTR(A.CALLED_PARTY_ON_DEST,
> 0,5),'14052',0,1),0)) +
> SUM(DECODE(B.MODALIDAD,'MPP',DECODE(SUBSTR(A.CALLED_PARTY_ON_DEST,
> 0,5),'14052',0,1),0)) AS vFIJOS_INVALIDOS
> FROM
> om_db.CDRS A,
> om_db.cofetel B,
> (SELECT CALL_SOURCE_REGID,
> SUM(SIGN(INSTR(CALLED_PARTY_ON_DEST,'*'))) AS
> vMIN_INVALID_COFETEL
> FROM om_db.CDRS
> WHERE CALL_SOURCE_REGID IS NOT NULL) D
> WHERE
> TO_NUMBER(SUBSTR(A.CALLED_PARTY_ON_DEST,-10,10)) between
> B.NIR_INICIAL AND B.NIR_FINAL
> AND INSTR(CALLED_PARTY_ON_DEST,'*') = 0
> AND A.CALL_SOURCE_REGID=D.CALL_SOURCE_REGID
> AND A.CALL_SOURCE_REGID IS NOT NULL
> GROUP BY
> A.CALL_SOURCE_REGID,
> D.vMIN_INVALID_COFETEL;
>
> END;
>
> and it returns the next error:
>
> ---- an INTO clause is expected in this SELECT statement
>
> ?? what can i do :( ??
>
> Thanks
Date: Tue, 24 Feb 2009 03:17:56 -0800 (PST)
Message-ID: <bc047eec-84f1-4bd7-932f-b8392f7b76f1_at_e18g2000vbe.googlegroups.com>
On Feb 23, 6:20 pm, Jorge Reyes <jorg_re..._at_hotmail.com> wrote:
> Hi people, this is a continuation of the post titled "ACCELERATING
> STORED PROCEDURE" from the last week, am sorry for the inconvenience
> that might cause them to have to read the previous post but its a long
> history as you can see, so the last post was very useful and thanks
> you so much for your answers, but it was a final question about the
> last response from Charles who give me this query:
>
> BEGIN
>
> SELECT A.CALL_SOURCE_REGID AS CLIENTE,
> D.vMIN_INVALID_COFETEL + SUM(1) AS vTOTAL_CDRS_CLIENTE,
> D.vMIN_INVALID_COFETEL AS vMIN_INVALID_COFETEL,
> SUM(1) AS vMIN_VALID_COFETEL,
> SUM(DECODE(B.MODALIDAD,'CPP',DECODE(SUBSTR(A.CALLED_PARTY_ON_DEST,
> 0,6),'140521',1,0),0)) AS vMOVILES_VALIDOS,
> SUM(DECODE(B.MODALIDAD,'CPP',DECODE(SUBSTR(A.CALLED_PARTY_ON_DEST,
> 0,6),'140521',0,1),0)) AS vMOVILES_INVALIDOS,
> SUM(DECODE(B.MODALIDAD,'FIJO',DECODE(SUBSTR(A.CALLED_PARTY_ON_DEST,
> 0,5),'14052',1,0),0)) +
> SUM(DECODE(B.MODALIDAD,'MPP',DECODE(SUBSTR(A.CALLED_PARTY_ON_DEST,
> 0,5),'14052',1,0),0)) AS vFIJOS_VALIDOS,
> SUM(DECODE(B.MODALIDAD,'FIJO',DECODE(SUBSTR(A.CALLED_PARTY_ON_DEST,
> 0,5),'14052',0,1),0)) +
> SUM(DECODE(B.MODALIDAD,'MPP',DECODE(SUBSTR(A.CALLED_PARTY_ON_DEST,
> 0,5),'14052',0,1),0)) AS vFIJOS_INVALIDOS
> FROM
> om_db.CDRS A,
> om_db.cofetel B,
> (SELECT CALL_SOURCE_REGID,
> SUM(SIGN(INSTR(CALLED_PARTY_ON_DEST,'*'))) AS
> vMIN_INVALID_COFETEL
> FROM om_db.CDRS
> WHERE CALL_SOURCE_REGID IS NOT NULL) D
> WHERE
> TO_NUMBER(SUBSTR(A.CALLED_PARTY_ON_DEST,-10,10)) between
> B.NIR_INICIAL AND B.NIR_FINAL
> AND INSTR(CALLED_PARTY_ON_DEST,'*') = 0
> AND A.CALL_SOURCE_REGID=D.CALL_SOURCE_REGID
> AND A.CALL_SOURCE_REGID IS NOT NULL
> GROUP BY
> A.CALL_SOURCE_REGID,
> D.vMIN_INVALID_COFETEL;
>
> END;
>
> and it returns the next error:
>
> ---- an INTO clause is expected in this SELECT statement
>
> ?? what can i do :( ??
>
> Thanks
John's response is correct.
The above SQL statement was meant to be used in SQL*Plus or some other query tool. Once you verify that the SQL statement provides correct information, you should be able to use the SQL statement with the MERGE SQL command to update the other table: http://download.oracle.com/docs/cd/B12037_01/server.101/b10759/statements_9016.htm
If the solution works, you will have eliminated most of the need for PL/SQL for this particular task.
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Received on Tue Feb 24 2009 - 05:17:56 CST