?
Solved

GROUP BY the year part of a date

Posted on 2004-09-02
11
Medium Priority
?
3,154 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

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

Independent Software Vendors: 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!

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
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…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

800 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