Question

Error in pl/sql function

Asked by: axtur

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;

                                  
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:

Select allOpen in new window

This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.

Subscribe now for full access to Experts Exchange and get

Instant Access to this Solution

  • Plus...
  • 30 Day FREE access, no risk, no obligation
  • Collaborate with the world's top tech experts
  • Unlimited access to our exclusive solution database
  • Never be left without tech help again

Subscribe Now

Asked On
2009-06-30 at 07:22:48ID24533325
Tags

pl

,

sql

,

oracle

Topics

PL / SQL

,

Oracle Database

,

Oracle 10.x

Participating Experts
5
Points
500
Comments
17

Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.

  • "The time we save is the biggest benefit of Experts Exchange to Warner Bros. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange." Mike Kapnisakis, Warner Bros.
  • "Our team likes having a resource that is more secure than just using Google and most experts using this service really know their stuff. It's nice to look here first versus using Google." Dayna Sellner, Lockheed Martin
  • "Anytime that I've been stumped with a problem, 9 out of 10 times Experts Exchange has either the accepted solution or an open discussion of the potential solution to the problem." Kenny Red, eBay Inc.

See what Experts Exchange can do for you.

Got a question?

We've got the answer.

Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.

Screenshot of Experts Exchange Knowledgebase

Need individual assistance?

Our experts are ready to help.

If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.

Screenshot of Experts Exchange Knowledgebase

Want to learn from the best?

Read articles from industry experts.

Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.

Screenshot of an Article

Working on a long term project?

Store your work and research.

Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.

Screenshot of Experts Exchange Knowledgebase

Access the answers to your technology questions today.

Subscribe Now

30-day free trial. Register in 60 seconds.

What Makes Experts Exchange Unique?

Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.

Trusted by the world's most respected brands.

image of each brand's logo

Faithfully serving IT professionals since 1996.

Experts Exchange Logo

Try it out and discover for yourself.

Subscribe Now

30-day free trial. Register in 60 seconds.

Related Solutions

  1. PL/SQL
    I cannot get either of these functions to work. I am a beginner with PL/SQL so I'm at a loss. Also I don't have means to any sort of debugger. I can't seem to get the following two functions to compile. Both of them return: Warning: Procedure created with compilation err...
  2. Datepart in PL/SQL
    Good day! I work with PL/SQL at Oracle 8i. I have field in format DATE (let's say, it's name is 'Updated'). I need to make group by month. Something that in Transact SQL I can do by 'group by month(updated)' or group by 'datepart(month, updated)'. How can I do the same...
  3. PL/SQL and SQL
    What is the order of performance within procedure, function and triggers. Also what difference does it make if we keep within and outside packages. For a select, update, insert and delete I know the performance issues. But what happens when the same statements are placed in a...
  4. Finding of PL/SQL objects
    How can we find various PL/SQL objects like procedures, functions, packages, triggers etc. and under which owner. Do we have default tables/views to do the same. ---sesh2002
  5. sql pl/sql
    hi experts which is the first introduced: SQL or PL/SQL ( Please give appropriate references ) also what is sql2 and sql3 and how i find that what version i have?
  6. PL/SQL
    I am a beginner to PL/SQL. i want to know how can we get values into pl/sql variables at runtime as we use ampersend (&) in SQL to get input from users.

Free Tech Articles

  1. WARNING: 5 Reasons why you should NEVER fix a computer for free.
    It is in our nature to love the puzzle. We are obsessed. The lot of us. We love puzzles. We love the challenge. We thrive on finding the answer. We hate disarray. It bothers us deep in our soul. W...
  2. SCCM OSD Basic troubleshooting
    SCCM 2007 OSD is a fantastic way to deploy operating systems, however, like most things SCCM issues can sometimes be difficult to resolve due to the sheer volume of logs to sift through and the dispe...
  3. Migrate Small Business Server 2003 to Exchange 2010 and Windows 2008 R2
    This guide is intended to provide step by step instructions on how to migrate from Small Business Server 2003 to Windows 2008 R2 with Exchange 2010. For this migration to work you will need the fo...
  4. Create a Win7 Gadget
    This article shows you how to create a simple "Gadget" -- a sort of mini-application supported by Windows 7 and Vista. Gadgets can be dropped anywhere on the desktop to provide instant information, ...
  5. Outlook continually prompting for username and password
    There have been a lot of questions recently regarding Outlook prompting for a username and password whilst using Exchange 2007. There are a few reasons why this would happen and I will try to cover t...
  6. Backup Exchange 2010 Information Store using Windows Backup
    There seems to be quite a lot of confusion around the ability to backup Exchange 2010 using the built in Windows Backup feature. This stems from the omission of this feature prior to Exchange 2007 s...

Cloud Class Webinars

  1. Avoiding Bugs in Microsoft Access
    Alison Balter takes and in-depth look at avoiding bugs in Access. In this webinar you will learn about using the immediate window to debug your applications, invoking the debugger, using breakpoints to troubleshoot, stepping through code, setting the next statement to execute, ...
  2. Top 10 Best New Features in Visio 2010
    Scott Helmers gives live demonstrations of the top 10 new features in Visio 2010. This webinar will teach you how to create compelling diagrams by adding shapes to the page with a single click, linking the shapes in a diagram to data in Excel (or SQL Server, or SharePoint), ...
  3. IT Consultant Business Secrets Revealed
    Michael Munger, Experts Exchange tech pro and IT consultant, pulls back the curtain on his very successful businesses and answers question on every IT consultant and business owner should know about. He shares secrets on what he did to solve the 5 most common problems in IT, ...
  4. Disaster Recovery and Business Continuity
    Quest CTO, Mike Billon, gives an overview of the steps involved in building a dunamic disaster recovery plan. Through case studies and an examination of software/hardware tooles for monitoring and testing, you'll gain a better understandin of where you are, where you want ...
  5. Organize Your Visio Diagrams with Containers and Lists
    Scott Helmers uses cross functional flowcharts, wireframe diagrams, data graphic legends and seating charts to teach you: how to ustilize all three new structured diagram components in Visio 2010, the best practices for organizeing shapes in previous version of Visio, how to organize ...
  6. How to Us Objects, Properties, Events and Methods in Microsoft Access
    Alison Dalter gives an in-depbth look at objects, properties, events and methods in Microsoft Access. In this webinar you will learn about using the object browser, referring to objects, working with properties and methods, working with object variables, understanding the ...

Join the Community

Give a Little. Get a Lot.

Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.

Join the Community

Answers

 

by: angelIIIPosted on 2009-06-30 at 07:25:23ID: 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?

 

by: AndytwPosted on 2009-06-30 at 07:27:20ID: 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;
                                              
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:

Select allOpen in new window

 

by: axturPosted on 2009-06-30 at 07:37:26ID: 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;

                                              
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:

Select allOpen in new window

 

by: angelIIIPosted on 2009-06-30 at 07:42:11ID: 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;

                                              
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:

Select allOpen in new window

 

by: AndytwPosted on 2009-06-30 at 07:42:24ID: 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;

                                              
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:

Select allOpen in new window

 

by: awking00Posted on 2009-06-30 at 07:47:12ID: 24745829

Andytw beat me to it, but you should remove the comma after
INTO v_nombre

 

by: awking00Posted on 2009-06-30 at 07:49:12ID: 24745855

Didn't see that angelIII also beat me to it. I must learn to type faster :-)

 

by: AndytwPosted on 2009-06-30 at 07:49:23ID: 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.

 

by: shru_0409Posted on 2009-06-30 at 07:58:16ID: 24745952

pls  catch the exception .....

EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      RETURN NULL;

 

by: axturPosted on 2009-06-30 at 08:30:48ID: 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;
                                              
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:

Select allOpen in new window

 

by: angelIIIPosted on 2009-06-30 at 08:38:33ID: 24746403

please try my suggestion above.


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

 

by: AndytwPosted on 2009-06-30 at 08:40:58ID: 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"

 

by: AndytwPosted on 2009-06-30 at 08:43:28ID: 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).

 

by: axturPosted on 2009-06-30 at 08:50:20ID: 24746527

Good angel, whats the meaning of sq where sq.r=1

 

by: angelIIIPosted on 2009-06-30 at 08:53:19ID: 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

 

by: shru_0409Posted on 2009-06-30 at 08:55:21ID: 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;

 

by: latorreconsultoresPosted on 2009-07-01 at 10:13:12ID: 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.

20120131-EE-VQP-002

3 Ways to Join

30-Day Free Trial

The Experts

98% positive feedback on 31,087 answers since March 2000. angeliii is a Microsoft Most Valuable Professional for his work with MS SQL Server & Develoment.

He has also proven his knowledge of Visual Basic Programming, PHP Scripting and Oracle Databases.

The Experts

97% positive feedback on 10,752 answers since July 2000. lrmoore has more than 18 years experience in the networking industry.

The six-time Mircosoft MVPs specialties include firewalls, virtual private networking, and network management.

Testimonials

"...and excellent source for support... Kind of like having your very own IT dept." Electriciansnet

Testimonials

"I was apprehensive at signing up at first. However... it has already made my life as an IT administrator much easier." JaCrews

Testimonials

"WOW! You guys have great, active, and knowledgeable people on here." moore50

Business Clients

Business Clients

In the Press

"If you’ve got a question... Experts Exchange can supply an answer.”

In the Press

"...an invaluable aid for both IT professionals and those who require tech support."

In the Press

"where IT professionals provide quick answers on just about any topic"

Business Account Plans

Loading Advertisement...