axtur
asked on
Error in pl/sql function
I keep getting an error in the next pl sql function:
No group function allowe here says
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;
the format of your select statement is slightly off. It should be:
SELECT col1, col2 ...
INTO var1, var2
FROM table
WHERE ...
Try this:
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;
ASKER
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...
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;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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;
Andytw beat me to it, but you should remove the comma after
INTO v_nombre
INTO v_nombre
Didn't see that angelIII also beat me to it. I must learn to type faster :-)
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.
pls catch the exception .....
EXCEPTION
WHEN NO_DATA_FOUND
THEN
RETURN NULL;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
RETURN NULL;
ASKER
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
Error(5,1): PL/SQL: SQL Statement ignored
Error(5,8): PL/SQL: ORA-00904: "TCHOFER"."NOMBRE_CHOFER":
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;
please try my suggestion above.
note: the ORDER BY + WHERE ROWNUM <= 2 will NOT give you the expected results!
note: the ORDER BY + WHERE ROWNUM <= 2 will NOT give you the expected results!
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"
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"
>>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).
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).
ASKER
Good angel, whats the meaning of sq where sq.r=1
> , 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
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
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_line a
AND tviaja.cod_cliente = tcliente.cod_cliente
AND tlineahorario.cod_linea = testa_constituida.cod_line a
AND tcliente.movilidad_reducid a = '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;
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
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_line
AND tviaja.cod_cliente = tcliente.cod_cliente
AND tlineahorario.cod_linea = testa_constituida.cod_line
AND tcliente.movilidad_reducid
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;
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_line a
AND tviaja.cod_cliente = tcliente.cod_cliente
AND tlineahorario.cod_linea = testa_constituida.cod_line a
AND tcliente.movilidad_reducid a='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.
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
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_line
AND tviaja.cod_cliente = tcliente.cod_cliente
AND tlineahorario.cod_linea = testa_constituida.cod_line
AND tcliente.movilidad_reducid
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.
a function can only return 1 single value, not a complete rowset
please clarify what the function should actually do?