Solved

GROUP BY the year part of a date

Posted on 2004-09-02
11
3,147 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
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 setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

695 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