I am trying to use columns that I created in this query to create another column.

Let me first my messy query. The query looks like this:

```
SELECT tb.team, tb.player, tb.type, tb.date, ToChar(Current Date-1, 'DD-MON-YY') as yesterday, 
       CASE WHEN to_date(tb.date) = yesterday then 1 else 0 end dateindicator, 

       FROM (
              COUNT DISTINCT(*) 
                  FROM TABLE_A, dual
                  where dateindicator = 1 
                  Group by tb.team
             )

```

What I am trying to do here is:

1. creating a column with "Yesterday's date"
2. Using the "Yesterday" column to create another column called dateindicator indicating each row is yesterday's data or not.
3. then using that dateindicator, I want to count the distinct number of player for each team that has 1 of the dateindicator column.

But I am getting the `"invalid identifier"` error. I am new to this oracle SQL, and trying to learn here.