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...

Answer 1

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;

Answer 2

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;

Answer 3

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.)