Re: Accelerating sp, again!!!
From: Jorge Reyes <jorg_reyes_at_hotmail.com>
Date: Wed, 25 Feb 2009 08:54:07 -0800 (PST)
Message-ID: <36bf4d9f-6fb8-4877-a367-8517b79790b7_at_q27g2000vbn.googlegroups.com>
On 24 feb, 05:17, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> 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/statem...
>
> 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.
Date: Wed, 25 Feb 2009 08:54:07 -0800 (PST)
Message-ID: <36bf4d9f-6fb8-4877-a367-8517b79790b7_at_q27g2000vbn.googlegroups.com>
On 24 feb, 05:17, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> 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/statem...
>
> 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.
Thank you so much for your help, im going to study more about Oracle, Charles thank you for your time, you are a wonderful person but most important you are patient with the newbies, thank you!!! Received on Wed Feb 25 2009 - 10:54:07 CST