using Decode alias in where claus

[Mel1]

We have a decode statement in the select clause where we are storing the value into a alias field name.

Can you use this alias field name in the where clause of the PL/SQL query?

 

[Dagon]

I didn't understand that. You might need to give a clearer example of what you are trying to do.

 

[Mel1]

The decode statement is something like:

select decode(value1<value2, &quot;late&quot;, value1=<value2, &quot;on time&quot;, &quot;early&quot part_status


We want to use the alias part_status in the where statement of the same select -- for example

select decode(table1.field1<table1.field2, &quot;late&quot;, table1.field1=table1.field2, &quot;on time&quot;, &quot;early&quot part_status
where part_status = &quot;late&quot;


We keep getting an error that part_status is an invalid column name. We wondered if you could do this.

 

[Dagon]

No, I don't think you can. You would have to repeat the decode e.g.

select decode(table1.field1<table1.field2, &quot;late&quot;, table1.field1=table1.field2, &quot;on time&quot;, &quot;early&quot part_status
where decode(table1.field1<table1.field2, &quot;late&quot;, table1.field1=table1.field2, &quot;on time&quot;, &quot;early&quot = &quot;late&quot;

But I'm not sure this would be very sensible anyway. It might be more efficient just to re-write the query as:

select &quot;late&quot; part_status
from ...
where table1.field1<table1.field2

That way, if there is an good index on table1.field1 or table2.field2, there's a chance Oracle will use it. With the decode there is no chance.