vcarballo
asked on
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.
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.
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.
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.
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
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
ASKER
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.
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.
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...
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...
ASKER
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.
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.
then, post the SQL you use to create view. the whole sql command
ASKER
Ok, first a note: I'm creating these views from Oracle 8i DBA Studio
I have created a first view (called vw_horasxusuario_capacitac ion)
succesfully:
CREATE VIEW vw_horasxusuario_capacitac ion AS
SELECT vw_horas_capacitacion.idca pacitacion ,
vw_horas_capacitacion.tipo ,
vw_horas_capacitacion.tema ,
vw_horas_capacitacion.fech a,
vw_horas_capacitacion.hora s,
vw_users_capacitacion.grup o,
vw_users_capacitacion.cant idad AS empleados,
vw_horas_capacitacion.hora s * vw_users_capacitacion.cant idad AS totalhoras
FROM vw_horas_capacitacion, vw_users_capacitacion
WHERE vw_horas_capacitacion.idca pacitacion = vw_users_capacitacion.idca pacitacion
Based on previous view I need to create a new view:
CREATE VIEW vw_horasxusuario_mes_capac itacion AS
SELECT to_char(fecha, 'YYYY') AS anno,
to_char(fecha, 'MM') AS mes,
SUM(totalhoras) AS horastotalmes
FROM vw_horasxusuario_capacitac ion
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
I have created a first view (called vw_horasxusuario_capacitac
succesfully:
CREATE VIEW vw_horasxusuario_capacitac
SELECT vw_horas_capacitacion.idca
vw_horas_capacitacion.tipo
vw_horas_capacitacion.tema
vw_horas_capacitacion.fech
vw_horas_capacitacion.hora
vw_users_capacitacion.grup
vw_users_capacitacion.cant
vw_horas_capacitacion.hora
FROM vw_horas_capacitacion, vw_users_capacitacion
WHERE vw_horas_capacitacion.idca
Based on previous view I need to create a new view:
CREATE VIEW vw_horasxusuario_mes_capac
SELECT to_char(fecha, 'YYYY') AS anno,
to_char(fecha, 'MM') AS mes,
SUM(totalhoras) AS horastotalmes
FROM vw_horasxusuario_capacitac
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
can you try this:
CREATE VIEW vw_horasxusuario_mes_capac itacion AS
SELECT to_char(fecha, 'YYYY') AS "anno",
to_char(fecha, 'MM') AS "mes",
sum(totalhoras) AS "horastotalmes"
FROM vw_horasxusuario_capacitac ion
GROUP BY to_char(fecha, 'YYYY'),
to_char(fecha, 'MM')
CREATE VIEW vw_horasxusuario_mes_capac
SELECT to_char(fecha, 'YYYY') AS "anno",
to_char(fecha, 'MM') AS "mes",
sum(totalhoras) AS "horastotalmes"
FROM vw_horasxusuario_capacitac
GROUP BY to_char(fecha, 'YYYY'),
to_char(fecha, 'MM')
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks a lot everybody for your help.
The view name was too long
The view name was too long
Gio