Forum Stats

  • 3,874,091 Users
  • 2,266,678 Discussions
  • 7,911,723 Comments

Discussions

Why isn't my group by on to_char working?

2»

Answers

  • stom
    stom Member Posts: 128 Bronze Badge
    edited Jan 15, 2020 8:21AM

    Thanks! I tried extract. Didn't work.

    SELECT    EXTRACT(YEAR FROM business_date),    COUNT(*)FROM    iceref.ref_clearing_calendarGROUP BY    EXTRACT(YEAR FROM business_date);
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,937 Red Diamond
    edited Jan 15, 2020 8:28AM Answer ✓

    Looks like it is fixed in 18C:

    SQL> select  banner

      2    from  v$version

      3  /

    BANNER

    --------------------------------------------------------------------------------

    Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production

    SQL> CREATE TABLE TBL(

      2                   CALENDAR_NAME CHAR(17),

      3                   BUSINESS_DATE DATE,

      4                   UPDATE_TS TIMESTAMP(6) DEFAULT systimestamp

      5                  )

      6  /

    Table created.

    SQL> INSERT

      2    INTO TBL

      3    SELECT  'CALENDAR1',

      4            SYSDATE + LEVEL,

      5            SYSTIMESTAMP

      6      FROM  DUAL

      7      CONNECT BY LEVEL <= 3

      8  /

    3 rows created.

    SQL> SELECT  TO_CHAR(BUSINESS_DATE,'YYYY'),

      2          COUNT(*)

      3    FROM  TBL

      4    GROUP BY TO_CHAR(BUSINESS_DATE,'YYYY')

      5  /

    TO_C   COUNT(*)

    ---- ----------

    2020          3

    SQL> ALTER TABLE TBL

      2    ADD CONSTRAINT TBL_PK

      3      PRIMARY KEY(BUSINESS_DATE)

      4  /

    Table altered.

    SQL> SELECT  TO_CHAR(BUSINESS_DATE,'YYYY'),

      2          COUNT(*)

      3    FROM  TBL

      4    GROUP BY TO_CHAR(BUSINESS_DATE,'YYYY')

      5  /

    TO_C   COUNT(*)

    ---- ----------

    2020          3

    SQL>

    SY.

  • GregV
    GregV Member Posts: 3,106 Gold Crown
    edited Jan 15, 2020 8:37AM

    Looks like this bug:

    12.2 Wrong Results from Query with GROUP BY Clause Based on To_char Function of Unique Index Columns (Doc ID 2294763.1)

    Fixed in 18.1 (or by patch 26588069)

    Jonathan Lewis
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,116 Blue Diamond
    edited Jan 15, 2020 8:44AM

    Just searching MOS to see if it's a known bug, and found that

    So far I found that apparently "

    GROUP BY may be removed when unique key of all tables appear in the GROUP-BY clause

    " is an enhancement introduced in 12.2.0.1

    This does then point me onwards to bug 26588069 which is the fix for the resulting wrong results bug. First appears in 18.1, with various patches available for 12.2.0.1

    Regards

    Jonathan Lewis

    stom