Solved

Error in pl/sql function

Posted on 2009-06-30
17
498 Views
Last Modified: 2013-12-19
I keep getting an error in the next pl sql function:

No group function allowe here says
CREATE OR REPLACE

FUNCTION CHOFER (HORARIOINI IN NUMBER, HORARIOFIN IN INTEGER) RETURN VARCHAR2 AS

v_nombre VARCHAR2(32);

v_cuenta NUMBER;

BEGIN

select tchofer.nombre_chofer into v_nombre, count(tcliente.cod_cliente)as pasajero

FROM

tchofer, tconduce, testa_constituida, tviaja, tcliente, tlineahorario

where tchofer.cod_chofer = tconduce.cod_chofer

AND tconduce.cod_bus = testa_constituida.cod_bus

AND tviaja.cod_linea = testa_constituida.cod_linea

AND tviaja.cod_cliente = tcliente.cod_cliente

AND tlineahorario.cod_linea = testa_constituida.cod_linea

AND tcliente.movilidad_reducida='S'

AND tlineahorario.horario between horarioini and horariofin

AND rownum < 2

group by tchofer.nombre_chofer

order by pasajeros desc;

  RETURN v_nombre;

END CHOFER;

Open in new window

0
Comment
Question by:axtur
  • 5
  • 4
  • 3
  • +3
17 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24745622
>select tchofer.nombre_chofer into v_nombre, count(tcliente.cod_cliente)as pasajero

a function can only return 1 single value, not a complete rowset
please clarify what the function should actually do?
0
 
LVL 11

Expert Comment

by:Andytw
ID: 24745641
the format of your select statement is slightly off.  It should be:
SELECT col1, col2 ...
INTO var1, var2
FROM table
WHERE ...

Try this:
CREATE OR REPLACE

FUNCTION CHOFER (HORARIOINI IN NUMBER, HORARIOFIN IN INTEGER) RETURN VARCHAR2 AS

v_nombre VARCHAR2(32);

v_cuenta NUMBER;

BEGIN

select tchofer.nombre_chofer, count(tcliente.cod_cliente)as pasajero

INTO v_nombre, v_cuenta

FROM tchofer, tconduce, testa_constituida, tviaja, tcliente, tlineahorario

where tchofer.cod_chofer = tconduce.cod_chofer

AND tconduce.cod_bus = testa_constituida.cod_bus

AND tviaja.cod_linea = testa_constituida.cod_linea

AND tviaja.cod_cliente = tcliente.cod_cliente

AND tlineahorario.cod_linea = testa_constituida.cod_linea

AND tcliente.movilidad_reducida='S'

AND tlineahorario.horario between horarioini and horariofin

AND rownum < 2

group by tchofer.nombre_chofer

order by pasajeros desc;

  RETURN v_nombre;

END CHOFER;

Open in new window

0
 

Author Comment

by:axtur
ID: 24745732
it isn't working that either andy, forget about the v_cuenta NUMBER

this should return the name of the "chofer" with most "pasajeros" witht the given conditions...
CREATE OR REPLACE

FUNCTION CHOFER (HORARIOINI IN NUMBER, HORARIOFIN IN INTEGER) RETURN VARCHAR2 AS

v_nombre VARCHAR2(32);

BEGIN

select tchofer.nombre_chofer into v_nombre, count(tcliente.cod_cliente)as pasajero

FROM

tchofer, tconduce, testa_constituida, tviaja, tcliente, tlineahorario

where tchofer.cod_chofer = tconduce.cod_chofer

AND tconduce.cod_bus = testa_constituida.cod_bus

AND tviaja.cod_linea = testa_constituida.cod_linea

AND tviaja.cod_cliente = tcliente.cod_cliente

AND tlineahorario.cod_linea = testa_constituida.cod_linea

AND tcliente.movilidad_reducida='S'

AND tlineahorario.horario between horarioini and horariofin

AND rownum < 2

group by tchofer.nombre_chofer

order by pasajeros desc;

  RETURN v_nombre;

END CHOFER;

Open in new window

0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 24745780
this should do:
CREATE OR REPLACE

FUNCTION CHOFER (HORARIOINI IN NUMBER, HORARIOFIN IN INTEGER) RETURN VARCHAR2 AS

v_nombre VARCHAR2(32);

BEGIN

SELECT nombre_chofer into v_nombre

FROM ( 

  select tchofer.nombre_chofer 

      , count(tcliente.cod_cliente) as pasajero

      , row_number() over (order by count(tcliente.cod_cliente) desc) r

    FROM tchofer, tconduce, testa_constituida, tviaja, tcliente, tlineahorario

    where tchofer.cod_chofer = tconduce.cod_chofer

      AND tconduce.cod_bus = testa_constituida.cod_bus

      AND tviaja.cod_linea = testa_constituida.cod_linea

      AND tviaja.cod_cliente = tcliente.cod_cliente

      AND tlineahorario.cod_linea = testa_constituida.cod_linea

      AND tcliente.movilidad_reducida='S'

      AND tlineahorario.horario between horarioini and horariofin

      group by tchofer.nombre_chofer

   ) sq

  WHERE sq.r = 1

  ;

  RETURN v_nombre;

END CHOFER;

Open in new window

0
 
LVL 11

Expert Comment

by:Andytw
ID: 24745784
You're query is not quite right for that.  Your query should be as follows:
SELECT  tchofer.nombre_chofer 

INTO v_nombre, 

FROM ( SELECT tchofer.nombre_chofer, count(tcliente.cod_cliente)as pasajero

       FROM tchofer, tconduce, testa_constituida, tviaja, tcliente, tlineahorario

       WHERE tchofer.cod_chofer = tconduce.cod_chofer

         AND tconduce.cod_bus = testa_constituida.cod_bus

          AND tviaja.cod_linea = testa_constituida.cod_linea

          AND tviaja.cod_cliente = tcliente.cod_cliente

          AND tlineahorario.cod_linea = testa_constituida.cod_linea

          AND tcliente.movilidad_reducida='S'

          AND tlineahorario.horario between horarioini and horariofin

          AND rownum < 2

     GROUP BY tchofer.nombre_chofer

     ORDER BY pasajeros desc )

WHERE rownum = 1;

Open in new window

0
 
LVL 31

Expert Comment

by:awking00
ID: 24745829
Andytw beat me to it, but you should remove the comma after
INTO v_nombre
0
 
LVL 31

Expert Comment

by:awking00
ID: 24745855
Didn't see that angelIII also beat me to it. I must learn to type faster :-)
0
 
LVL 11

Expert Comment

by:Andytw
ID: 24745857
awking00: yes, just, but I was beaten to it by angelIII  ; - ).  Yes, a small typo there, with the trailing ',' after  v_nombre, thanks for spotting it.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 14

Expert Comment

by:shru_0409
ID: 24745952
pls  catch the exception .....

EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      RETURN NULL;

0
 

Author Comment

by:axtur
ID: 24746302
I keep getting this:

Error(5,1): PL/SQL: SQL Statement ignored
Error(5,8): PL/SQL: ORA-00904: "TCHOFER"."NOMBRE_CHOFER": identificador no válido
create or replace FUNCTION CHOFER (HORARIOINI IN NUMBER, HORARIOFIN IN INTEGER) RETURN VARCHAR2 AS

v_nombre VARCHAR2(32);

BEGIN

SELECT tchofer.nombre_chofer

INTO v_nombre 

FROM (SELECT tchofer.nombre_chofer, count(tcliente.cod_cliente) as pasajeros

       FROM tchofer, tconduce, testa_constituida, tviaja, tcliente, tlineahorario

       WHERE tchofer.cod_chofer = tconduce.cod_chofer

         AND tconduce.cod_bus = testa_constituida.cod_bus

          AND tviaja.cod_linea = testa_constituida.cod_linea

          AND tviaja.cod_cliente = tcliente.cod_cliente

          AND tlineahorario.cod_linea = testa_constituida.cod_linea

          AND tcliente.movilidad_reducida='S'

          AND tlineahorario.horario between horarioini and horariofin

          AND rownum < 2

     GROUP BY tchofer.nombre_chofer

     ORDER BY pasajeros desc );

END CHOFER;

Open in new window

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24746403
please try my suggestion above.


note: the ORDER BY + WHERE ROWNUM <= 2 will NOT give you the expected results!
0
 
LVL 11

Expert Comment

by:Andytw
ID: 24746434
ORA-00904: "%s": invalid identifier.
It's not recognising NOMBRE_CHOFER as a column, I suspect that column name is a quoted identifier.

In SQL*Plus can you issue the command "DESC tchofer"?  You should see output similar to the following:
SQL> DESC tchofer
Name                            ....
-----------
NOMBRE_CHOFER
....
You need to quote the column name in your query, and copy the case as returned by the command above  e.g.
"Nombre_chofer"

0
 
LVL 11

Expert Comment

by:Andytw
ID: 24746458
>>this should return the name of the "chofer" with most "pasajeros" witht the given conditions...
angelIII is correct, your query will NOT give the right answer.  In order to get the result you want, you should try either angelIII's solution (http:#24745780) or my solution (http:#24745784).
0
 

Author Comment

by:axtur
ID: 24746527
Good angel, whats the meaning of sq where sq.r=1
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24746565
> , row_number() over (order by count(tcliente.cod_cliente) desc) r
defined the column alias "r"

and the "sq" (subquery) comes from here:

SELECT nombre_chofer into v_nombre
FROM (   <subquery>
   ) sq   --- <<< here
  WHERE sq.r = 1
0
 
LVL 14

Expert Comment

by:shru_0409
ID: 24746590
CREATE OR REPLACE FUNCTION chofer(horarioini IN NUMBER, horariofin IN INTEGER)
   RETURN VARCHAR2
AS
   v_nombre   VARCHAR2(32);
   v_cuenta   NUMBER;
BEGIN
   SELECT d.nombre_chofer
     INTO v_nombre
     FROM (SELECT   tchofer.nombre_chofer,
                    COUNT(tcliente.cod_cliente) AS pasajero
               FROM tchofer,
                    tconduce,
                    testa_constituida,
                    tviaja,
                    tcliente,
                    tlineahorario
              WHERE tchofer.cod_chofer = tconduce.cod_chofer
                AND tconduce.cod_bus = testa_constituida.cod_bus
                AND tviaja.cod_linea = testa_constituida.cod_linea
                AND tviaja.cod_cliente = tcliente.cod_cliente
                AND tlineahorario.cod_linea = testa_constituida.cod_linea
                AND tcliente.movilidad_reducida = 'S'
                AND tlineahorario.horario BETWEEN horarioini AND horariofin
           GROUP BY tchofer.nombre_chofer
           ORDER BY pasajeros DESC) d
    WHERE ROWNUM = 1;

   RETURN v_nombre;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      RETURN NULL;
END chofer;
0
 
LVL 2

Expert Comment

by:latorreconsultores
ID: 24756319
hello axtur:

1 . tu necesitas una funcion que te traiga el nombre del chofer, dandole solo el horario de inicio y fin.

2. necesitas modificar tu consulta:
      - el error te lo marca porque tienes un count y no tienes un group by.

select tchofer.nombre_chofer, count(tcliente.cod_cliente)as pasajero
INTO v_nombre, v_cuenta
FROM tchofer, tconduce, testa_constituida, tviaja, tcliente, tlineahorario
where tchofer.cod_chofer = tconduce.cod_chofer
AND tconduce.cod_bus = testa_constituida.cod_bus
AND tviaja.cod_linea = testa_constituida.cod_linea
AND tviaja.cod_cliente = tcliente.cod_cliente
AND tlineahorario.cod_linea = testa_constituida.cod_linea
AND tcliente.movilidad_reducida='S'
AND tlineahorario.horario between horarioini and horariofin
group by  tchofer.nombre_chofer

 // tambien puedes consultarme en joaquin_ma hotmail
preguntas:
 
1. creo que no necesitas la tabla de clientes para esta consulta.
2. es interesante ver que este querie te puede traer mas de un registro porque puedes tener 2 choferes con los mismos horarios. creo que aqui tienes un problema de la definicion del problema.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now