Solved

GROUP BY the year part of a date

Posted on 2004-09-02
11
3,139 Views
Last Modified: 2012-08-13
Hi again:

This time I have a problem with GROUP BY clause

I have this:

table: training
trn_date: date
trn_hours: number

I need to build a view getting the sum of trn_hours
grouping by year by month. Something like this:

SELECT to_char(trn_date, 'YYYY') AS trn_year,
            to_char(trn_date, 'MM') AS trn_month,
            SUM(trn_hours) AS trn_total
  FROM training
  GROUP BY to_char(trn_date, 'YYYY'),
                  to_char(trn_date, 'MM')

But I receive a "VBO-1506: Please specify a valid Oracle identifier in the name field" error message.

How must I do to get this type of query??

Thnx in advance.
0
Comment
Question by:vcarballo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
  • +1
11 Comments
 
LVL 3

Expert Comment

by:jaramill
ID: 11967275
What you're doing seems fine.  What is the ORA error you're getting?  Not the VBO error.

Gio
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 11967300
vcarballo:

your query is just fine.

SELECT to_char(trn_date, 'YYYY') AS trn_year,
           to_char(trn_date, 'MM') AS trn_month,
           SUM(trn_hours) AS trn_total
 FROM training
 GROUP BY to_char(trn_date, 'YYYY'),
                  to_char(trn_date, 'MM')


lookat your error message, the error code is VBO-1506, meaning you have a VB error not ORACLE error.

I guess in your vb code, you have tried to use a different field name than the one in the select query (pay attention to the fact that you are using ALIAS now in your select query.

0
 
LVL 12

Expert Comment

by:geotiger
ID: 11967316
I do not know why you get the error message. I have tried the same DML on my table and did not get any error.

SQL>  select to_char(DFCREATE, 'YYYY') as trn_year,
  2   to_char(DFCREATE,'MM') as trn_month, sum(DFPLATE) as trn_total
  3  from s120p001
  4  group by to_char(DFCREATE, 'YYYY'), to_char(DFCREATE,'MM');

TRN_ TR  TRN_TOTAL
---- -- ----------
2004 04          4
2004 05        159
2004 06        123
2004 07         14
2004 08         10

Are you using XML DB? You may have name clash in your DB.

GT
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

Author Comment

by:vcarballo
ID: 11967579
Ok I know the query is fine, I ran that query from Oracle SQL Plus
and it worked fine.

But I get that error when I try to create a view using the previous
query in Oracle 8i DBA Studio.

I'll appreciate any help.


0
 
LVL 23

Expert Comment

by:seazodiac
ID: 11967611
Did you read my post???
in my first post:


lookat your error message, the error code is VBO-1506, meaning you have a VB error not ORACLE error.

I guess in your vb code, you have tried to use a different field name than the one in the select query (pay attention to the fact that you are using ALIAS now in your select query.


forget about oracle8i DBA studio, it's very primitive and buggy...you cannot even execute procedures and function there....

in any case, post your error message will be helpfull...
0
 

Author Comment

by:vcarballo
ID: 11967866
Visual Basic ??
I'm not using any programming language.

I get that error from "Oracle 8i DBA Studio" when I try to create
a view based on the previous query.
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 11967920
then, post the SQL you use to create view. the whole sql command
0
 

Author Comment

by:vcarballo
ID: 11968163
Ok, first a note: I'm creating these views from Oracle 8i DBA Studio

I have created a first view (called vw_horasxusuario_capacitacion)
succesfully:

CREATE VIEW vw_horasxusuario_capacitacion AS
  SELECT vw_horas_capacitacion.idcapacitacion,
              vw_horas_capacitacion.tipo,
              vw_horas_capacitacion.tema,
              vw_horas_capacitacion.fecha,
              vw_horas_capacitacion.horas,
              vw_users_capacitacion.grupo,
              vw_users_capacitacion.cantidad AS empleados,
              vw_horas_capacitacion.horas * vw_users_capacitacion.cantidad AS totalhoras
    FROM vw_horas_capacitacion, vw_users_capacitacion
    WHERE vw_horas_capacitacion.idcapacitacion = vw_users_capacitacion.idcapacitacion

Based on previous view I need to create a new view:

CREATE VIEW vw_horasxusuario_mes_capacitacion AS
  SELECT to_char(fecha, 'YYYY') AS anno,
              to_char(fecha, 'MM') AS mes,
              SUM(totalhoras) AS horastotalmes
    FROM vw_horasxusuario_capacitacion
    GROUP BY to_char(fecha, 'YYYY'),
                    to_char(fecha, 'MM')

When I try to create this second view, Oracle DBA Studio show a dialog box titled
"Oracle Enterprise Manager" with the following error message:
"VBO-1506: Please specify a valid Oracle identifier in the name field"

I appreciate your helping time
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 11968210
can you try this:

CREATE VIEW vw_horasxusuario_mes_capacitacion AS
 SELECT to_char(fecha, 'YYYY') AS "anno",
              to_char(fecha, 'MM') AS "mes",
              sum(totalhoras)  AS  "horastotalmes"
   FROM vw_horasxusuario_capacitacion
   GROUP BY to_char(fecha, 'YYYY'),
                    to_char(fecha, 'MM')
0
 
LVL 12

Accepted Solution

by:
geotiger earned 180 total points
ID: 11968359

The problem is that your view name exceeds the name limitation in Oracle. I did a test:

SQL> create view vw_horasxusuario_mes_capacitacion as
  2  select * from dual;
create view vw_horasxusuario_mes_capacitacion as
            *
ERROR at line 1:
ORA-00972: identifier is too long

0
 

Author Comment

by:vcarballo
ID: 11968412
Thanks a lot everybody for your help.
The view name was too long

0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

697 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