Solved

GROUP BY the year part of a date

Posted on 2004-09-02
11
3,126 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
  • 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
 

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
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.

 

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

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

747 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

13 Experts available now in Live!

Get 1:1 Help Now