ORA-00904 - Invalid Identifier

Question

I'm new to PL/SQL and I'm trying to learn it as fast as I can.
I was trying to do a simple SELECT but I came across this error.
Although I know what it means, I really don't know how to solve the problem...

This is my portion of code:

SELECT
    NVL(UPPER(T.COL1),'N.D.') COL1,
    V.SECO,
    'N' CL_MED,
    V.DEST_USO,
  (CASE  
    WHEN V.COL2 IS NULL
        AND V.SECO IN ('B090','B100') THEN ''
    WHEN V.COL2 LIKE 'L-DEF%' 
        OR V.COL2 LIKE 'L-FUI%' 
        AND V.SECO IN ('B090','B100') THEN 'FUI/DEF'
    WHEN V.COL2 IS NULL 
        AND V.SECO = 'B080' 
        AND V.COL3 LIKE 'DEF%'
        OR V.COL3 LIKE 'FUI%' THEN 'FUI/DEF'  
    ELSE ''
    END 
    ) FLAG_DEF_FUI
  FROM TAB1 V
  JOIN TAB2 C ON (V.COL4    = C.COL4
                 AND V.COL5       = C.COL5
                 AND V.COL6 = C.COL6)
  JOIN TAB3 T ON (V.COL4  = T.COL4
                  AND V.COL5 = T.COL5
                  AND V.COL5A = T.COL5A
                  AND T.COL6 =V.COL6)
  WHERE V.COL4 = :COL4
  AND V.COL6 = :COL6
  AND V.COL5 NOT IN
    (SELECT gcm.PDR
    FROM TAB4 gcm
    WHERE gcm.COL6 = :COL6
    )
  GROUP BY (UPPER(T.COL1),V.SECO, V.DEST_USO, FLAG_DEF_FUI)

and FLAG_DEF_FUI is the column that causes this error..... Any help?!

EDIT: I'm not asking WHY I can't use an alias in a GROUP BY. I'm asking a workaround for this problem...

Possible duplicate of [Why doesn't Oracle SQL allow us to use column aliases in GROUP BY clauses?](http://stackoverflow.com/questions/2681494/why-doesnt-oracle-sql-allow-us-to-use-column-aliases-in-group-by-clauses) — piet.t, Mar 23 '16 at 14:56
is it right `'N' CL_MED` or it is something like this `'N'+''+ CL_MED` in select statemnt — Jaydip Jadhav, Mar 23 '16 at 14:58
@piet.t I know that aliases can't be used in a group by. I was just asking advices about how to edit the query to make it work... — Leon, Mar 23 '16 at 15:03
Use a subquery (inline view) as suggested in the answers to the linked question. Or maybe [this question](http://stackoverflow.com/q/24980245/266304) is a better duplicate if you already know why? — Alex Poole, Mar 23 '16 at 15:05
Wait, why do you have a group-by clause at all? You aren't using any aggregate functions. Are you just getting duplicates? If so use `distinct` instead. — Alex Poole, Mar 23 '16 at 15:12
..or http://stackoverflow.com/questions/268429/group-by-alias-oracle — piet.t, Mar 23 '16 at 15:12

Raul Luna · Accepted Answer · 2016-03-24T17:21:32.270

2

To make a grouping of a complex function that the one you have, I always make a subselect. Thus, your query will become:

  select child_query.stuff, child_query.flag_def_fui
    from 
   (   
    select
        'some-stuff' some_stuff,
      (case  
        when v.col2 is null
            and v.seco in ('b090','b100') then ''
        when v.col2 like 'l-def%' 
            or v.col2 like 'l-fui%' 
            and v.seco in ('b090','b100') then 'fui/def'
        when v.col2 is null 
            and v.seco = 'b080' 
            and v.col3 like 'def%'
            or v.col3 like 'fui%' then 'fui/def'  
        else ''
        end 
        ) flag_def_fui
      from tab1 v
      join tab2 c
      on (v.col4    = c.col4
      and v.col5       = c.col5
      and v.col6 = c.col6)
      join tab3 t
      on (v.col4            = t.col4
      and v.col5               = t.col5
      and v.col5a              = t.col5a
      and t.col6         =v.col6)
      where v.col4          = :col4
      and v.col6         = :col6
      and v.col5 not          in
        (select gcm.pdr
        from tab4 gcm
        where gcm.col6 = :col6
        )
     )  child_query 
      group by child_query.stuff, child_query.flag_def_fui;

edited Mar 24 '16 at 17:21

answered Mar 23 '16 at 15:30

Raul Luna

1,945
1
17
26

1

But it's grouping by every column in the select list - so why not just use `distinct` instead? – Alex Poole Mar 23 '16 at 15:47
No, the thing is that I've removed all the columns that aren't interesting for the example, to make it easier to read. You can add every column you want and the select will work also as expected – Raul Luna Mar 24 '16 at 17:20
Not sure what you mean. This has all the columns from the question. Any columns you add to the select list will have to be added to the group-by too, unless they are aggregates. Without any aggregates why group when you can use distinct? – Alex Poole Mar 24 '16 at 17:31
In the solution I give, I've deleted the columns NVL(UPPER(T.COL1),'N.D.') COL1, V.SECO, 'N' CL_MED, V.DEST_USO.... for the sake of better understanding, but it seems I've failed. – Raul Luna Mar 28 '16 at 17:07

score 2 · Answer 2 · edited May 23 '17 at 12:23

The other answers give you two options and are both correct. Just to be clear, and to specifically answer your edited question, you have three options to work around the issue of not being able to reference aliased columns in the GROUP BY:

1) Answer 1: Wrap your query so that column aliases can be referenced easily, i.e.

  SELECT column_alias 
    FROM (<your query>) 
GROUP BY column_alias;

2) Answer 2: Don't use GROUP BY if you aren't using aggregate functions, use DISTINCT instead.

3) Copy the complicated expression that makes up the column into the GROUP BY, i.e.

  SELECT CASE  
           WHEN col1 = 1 THEN 'one'
           WHEN col1 = 2 THEN 'two'
           ELSE ''
         END as col1_alias,
         SUM(col2) as col2_alias,
         col3
    FROM table_name
GROUP BY CASE  
           WHEN col1 = 1 THEN 'one'
           WHEN col1 = 2 THEN 'two'
           ELSE ''
         END,
         col3;

Alex Poole · Answer 3 · 2016-03-23T16:00:05.120

From what you've shown you don't need a group-by clause at all, as you have no aggregate functions (min, max, etc.). Every column in the select list is in the group-by clause.

If you are using that clause to suppress duplicates then it would be simpler to use the distinct keyword instead:

SELECT DISTINCT
    NVL(UPPER(T.COL1),'N.D.') COL1,
    V.SECO,
    'N' CL_MED,
    V.DEST_USO,
  (CASE  
    WHEN V.COL2 IS NULL
        AND V.SECO IN ('B090','B100') THEN ''
    WHEN V.COL2 LIKE 'L-DEF%' 
        OR V.COL2 LIKE 'L-FUI%' 
        AND V.SECO IN ('B090','B100') THEN 'FUI/DEF'
    WHEN V.COL2 IS NULL 
        AND V.SECO = 'B080' 
        AND V.COL3 LIKE 'DEF%'
        OR V.COL3 LIKE 'FUI%' THEN 'FUI/DEF'  
    ELSE ''
    END 
    ) FLAG_DEF_FUI
  FROM TAB1 V
  JOIN TAB2 C
  ON (V.COL4    = C.COL4
  AND V.COL5       = C.COL5
  AND V.COL6 = C.COL6)
  JOIN TAB3 T
  ON (V.COL4            = T.COL4
  AND V.COL5               = T.COL5
  AND V.COL5A              = T.COL5A
  AND T.COL6         =V.COL6)
  WHERE V.COL4          = :COL4
  AND V.COL6         = :COL6
  AND V.COL5 NOT          IN
    (SELECT gcm.PDR
    FROM TAB4 gcm
    WHERE gcm.COL6 = :COL6
    )

(You might also want to see if a not exists check would be more efficient that the not in you have now.)

ORA-00904 - Invalid Identifier

3 Answers3