Problem when rating object-table when doing a SELECT on Oracle

Question

Problem when rating object-table when doing a SELECT on Oracle

Navigation

#1 by (0 votes)
#2 by (0 votes)

2

I have a problem with the following query :

SELECT USER
    ,TO_CHAR(LAST_LOGIN, 'DD/MM/YYYY') LAST_LOGIN
    ,SAILA
    ,MAX(INTENTS) INTENTS
    ,MAQ
FROM OWNER.MY_TABLE_NAME
WHERE LAST_LOGIN >= TO_DATE('22/02/2017' || ' 00:00:00', 'dd/mm/yyyy hh24:mi:ss')
    AND LAST_LOGIN <= TO_DATE('22/02/2017' || ' 23:59:59', 'dd/mm/yyyy hh24:mi:ss')
GROUP BY USER
    ,TO_CHAR(LAST_LOGIN, 'DD/MM/YYYY')
    ,SAILA
    ,INTENTS
    ,MAQ
ORDER BY SAILA;

When executed in this way, it is executed correctly without any problem.

But when grading the fields of the table and "assigning" a name to the table sends me an error, I execute this query :

SELECT NAME_TABLE.USER
    ,TO_CHAR(NAME_TABLE.LAST_LOGIN, 'DD/MM/YYYY') LAST_LOGIN
    ,NAME_TABLE.SAILA
    ,MAX(NAME_TABLE.INTENTS) INTENTS
    ,NAME_TABLE.MAQ
FROM OWNER.MY_TABLE_NAME NAME_TABLE
WHERE NAME_TABLE.LAST_LOGIN >= TO_DATE('22/02/2017' || ' 00:00:00', 'dd/mm/yyyy hh24:mi:ss')
    AND NAME_TABLE.LAST_LOGIN <= TO_DATE('22/02/2017' || ' 23:59:59', 'dd/mm/yyyy hh24:mi:ss')
GROUP BY NAME_TABLE.USER
    ,TO_CHAR(NAME_TABLE.LAST_LOGIN, 'DD/MM/YYYY')
    ,NAME_TABLE.SAILA
    ,NAME_TABLE.INTENTS
    ,NAME_TABLE.MAQ
ORDER BY NAME_TABLE.SAILA
    ,NAME_TABLE.LAST_LOGIN DESC;

The error it throws is:

[Error] Execution (1: 82): ORA-00979: not to GROUP BY expression

Does anyone know why that happens?

How could I solve it?

sql oracle

asked by 5frags 23.02.2017 в 00:31

source

2 answers

0

The problem is that you can not use a field in the SELECT or ORDER BY clauses that are not part of the GROUP BY , unless you use it in an aggregation function such as MAX .

In this case, the error is because you use the NAME_TABLE.LAST_LOGIN field in ORDER BY . And although, technically, NAME_TABLE.LAST_LOGIN is in GROUP BY , it's not really the same, because in the GROUP BY you're using the expression TO_CHAR(NAME_TABLE.LAST_LOGIN, 'DD/MM/YYYY') , hence the error you receive.

Although the solution you found to add MAX() in ORDER BY is very simple and works well, in my opinion it obscures the intention of the query.

If I were in your place, I would make the following changes:

In GROUP BY , change TO_CHAR(NAME_TABLE.LAST_LOGIN, 'DD/MM/YYYY') by TRUNC(NAME_TABLE.LAST_LOGIN) , which serves the same purpose of grouping by date excluding time, but without having to convert to a type other than date , which is partly causing problems in the other parts of your query.

In SELECT , set TO_CHAR(NAME_TABLE.LAST_LOGIN, 'DD/MM/YYYY') LAST_LOGIN to TO_CHAR(TRUNC(NAME_TABLE.LAST_LOGIN), 'DD/MM/YYYY') LAST_LOGIN .

Use the expression TRUNC(NAME_TABLE.LAST_LOGIN) in ORDER BY .

These changes result in the following query:

SELECT NAME_TABLE.USER
    ,TO_CHAR(TRUNC(NAME_TABLE.LAST_LOGIN), 'DD/MM/YYYY') LAST_LOGIN
    ,NAME_TABLE.SAILA
    ,MAX(NAME_TABLE.INTENTS) INTENTS
    ,NAME_TABLE.MAQ
FROM OWNER.MY_TABLE_NAME NAME_TABLE
WHERE NAME_TABLE.LAST_LOGIN >= TO_DATE('22/02/2017' || ' 00:00:00', 'dd/mm/yyyy hh24:mi:ss')
    AND NAME_TABLE.LAST_LOGIN <= TO_DATE('22/02/2017' || ' 23:59:59', 'dd/mm/yyyy hh24:mi:ss')
GROUP BY NAME_TABLE.USER
    ,TRUNC(NAME_TABLE.LAST_LOGIN)
    ,NAME_TABLE.SAILA
    ,NAME_TABLE.INTENTS
    ,NAME_TABLE.MAQ
ORDER BY NAME_TABLE.SAILA
    ,TRUNC(NAME_TABLE.LAST_LOGIN) DESC;

In my opinion, the intention is clearer, and it makes more sense that the conversion from date to chain ( TO_CHAR ) is limited to the clause SELECT for the presentation of the results. But that, in both the GROUP BY and the ORDER BY , LAST_LOGIN is still handled as a type DATE .

answered by 23.02.2017 в 17:50

Refresh layout in pymel automatically Error generating an APK in Android Studio

score 0 · Accepted Answer

With this, solve my problem, thank you.

SELECT NAME_TABLE.USER
    ,TO_CHAR(NAME_TABLE.LAST_LOGIN, 'DD/MM/YYYY') LAST_LOGIN
    ,NAME_TABLE.SAILA
    ,MAX(NAME_TABLE.INTENTS) INTENTS
    ,NAME_TABLE.MAQ
FROM OWNER.MY_TABLE_NAME NAME_TABLE
WHERE NAME_TABLE.LAST_LOGIN >= TO_DATE('22/02/2017' || ' 00:00:00', 'dd/mm/yyyy hh24:mi:ss')
    AND NAME_TABLE.LAST_LOGIN <= TO_DATE('22/02/2017' || ' 23:59:59', 'dd/mm/yyyy hh24:mi:ss')
GROUP BY NAME_TABLE.USER
    ,TO_CHAR(NAME_TABLE.LAST_LOGIN, 'DD/MM/YYYY')
    ,NAME_TABLE.SAILA
    ,NAME_TABLE.INTENTS
    ,NAME_TABLE.MAQ
ORDER BY NAME_TABLE.SAILA
    ,MAX(NAME_TABLE.LAST_LOGIN) DESC;

I just had to add the MAX() to the last field.

Thank you.