Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3157
  • Last Modified:

GROUP BY the year part of a date

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
vcarballo
Asked:
vcarballo
  • 4
  • 4
  • 2
  • +1
1 Solution
 
jaramillCommented:
What you're doing seems fine.  What is the ORA error you're getting?  Not the VBO error.

Gio
0
 
seazodiacCommented:
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
 
geotigerCommented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
vcarballoAuthor Commented:
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
 
seazodiacCommented:
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
 
vcarballoAuthor Commented:
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
 
seazodiacCommented:
then, post the SQL you use to create view. the whole sql command
0
 
vcarballoAuthor Commented:
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
 
seazodiacCommented:
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
 
geotigerCommented:

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
 
vcarballoAuthor Commented:
Thanks a lot everybody for your help.
The view name was too long

0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 4
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now