Re: ACCELERATING STORE PROCEDURE
From: Ed Prochak <edprochak_at_gmail.com>
Date: Fri, 20 Feb 2009 11:21:03 -0800 (PST)
Message-ID: <a5474939-542f-408a-ab67-5f89f70aa9da_at_e6g2000vbe.googlegroups.com>
On Feb 19, 9:11 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> On Feb 19, 5:57 pm, Jorge Reyes <jorg_re..._at_hotmail.com> wrote:
>
>
>
> > Oh hello thank you so much for your help, i do this but i have a
> > problem with the DECODE function, i'm receiving this error:
>
> > 16:47:37.329 DBMS nmsnoc-db -- Error: PL/SQL: SQL Statement ignored,
> > Batch 1 Line 80 Col 17
> > 16:47:37.407 DBMS nmsnoc-db -- Error: PL/SQL: ORA-00934: group
> > function is not allowed here, Batch 1 Line 82 Col 19
>
> > i probe with this (into the sp):
>
> > SELECT
> > SUM(1) INTO vMIN_VALID_COFETEL,
> > SUM(decode(B.MODALIDAD,'CPP',decode(SUBSTR(A.CALLED_PARTY_ON_DEST,
> > 0,6),'140521',1,0),0)) INTO vMOVILES_VALIDOS
> > FROM om_db.CDRS A, om_db.cofetel B;
>
> > Decode function, could be used inside of an Store Procedure?
>
> > Regards
>
> It looks like I made a mistake, let's see if we are able to determine
> what is wrong by using a test setup:
[]
> Try #2:
> DECLARE
> vC0 NUMBER;
> vC1 NUMBER;
>
> BEGIN
> SELECT
> SUM(DECODE(C1,0,1,0)) INTO vC0,
> SUM(DECODE(C1,1,0,0)) INTO vC1
> FROM
> T1;
> END;
> /
>
> ERROR at line 8:
> ORA-06550: line 8, column 5:
> PL/SQL: ORA-00934: group function is not allowed here
> ORA-06550: line 6, column 3:
> PL/SQL: SQL Statement ignored
>
> OK, it looks like we were able to reproduce the problem, (after
> thinking about it for a couple minutes) I wonder if it is a problem
> with INTO appearing more than once?
Date: Fri, 20 Feb 2009 11:21:03 -0800 (PST)
Message-ID: <a5474939-542f-408a-ab67-5f89f70aa9da_at_e6g2000vbe.googlegroups.com>
On Feb 19, 9:11 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> On Feb 19, 5:57 pm, Jorge Reyes <jorg_re..._at_hotmail.com> wrote:
>
>
>
> > Oh hello thank you so much for your help, i do this but i have a
> > problem with the DECODE function, i'm receiving this error:
>
> > 16:47:37.329 DBMS nmsnoc-db -- Error: PL/SQL: SQL Statement ignored,
> > Batch 1 Line 80 Col 17
> > 16:47:37.407 DBMS nmsnoc-db -- Error: PL/SQL: ORA-00934: group
> > function is not allowed here, Batch 1 Line 82 Col 19
>
> > i probe with this (into the sp):
>
> > SELECT
> > SUM(1) INTO vMIN_VALID_COFETEL,
> > SUM(decode(B.MODALIDAD,'CPP',decode(SUBSTR(A.CALLED_PARTY_ON_DEST,
> > 0,6),'140521',1,0),0)) INTO vMOVILES_VALIDOS
> > FROM om_db.CDRS A, om_db.cofetel B;
>
> > Decode function, could be used inside of an Store Procedure?
>
> > Regards
>
> It looks like I made a mistake, let's see if we are able to determine
> what is wrong by using a test setup:
[]
> Try #2:
> DECLARE
> vC0 NUMBER;
> vC1 NUMBER;
>
> BEGIN
> SELECT
> SUM(DECODE(C1,0,1,0)) INTO vC0,
> SUM(DECODE(C1,1,0,0)) INTO vC1
> FROM
> T1;
> END;
> /
>
> ERROR at line 8:
> ORA-06550: line 8, column 5:
> PL/SQL: ORA-00934: group function is not allowed here
> ORA-06550: line 6, column 3:
> PL/SQL: SQL Statement ignored
>
> OK, it looks like we were able to reproduce the problem, (after
> thinking about it for a couple minutes) I wonder if it is a problem
> with INTO appearing more than once?
Yes that's exqactly it. the syntax for this is basically SELECT <column, column,...> INTO <variable, variable,...>
IOW, the INTO appears once between the list of columns and the list of target variables. So there is not need for record types to solve this. [Note: record types are useful in their own right.)
This is less to correct Charles than it is to inform newbies and lurkers.
Have a great day.
Ed Received on Fri Feb 20 2009 - 13:21:03 CST