Hi, has anyone got any idea on how I can use a DECODE within a select statement that includes a grouping function. I can only give the DECODE field an alias and you can't use alias's in the Group By clause. Yet, I have to have the Group By clause for the fields that don't use a grouping function or I get the error 'Not a group by expression'. If I try to use the alias in the Group By I get 'invalid column name'. Example code:

select order_header.client_id as clientid,
DECODE('Shipped', 'Hold', order_header.ORDER_DATE, 'Shipped', order_header.DELIVER_BY_DATE, NULL) as TheDate,
count(order_header.FROM_SITE_ID)
from order_header
group by client_id, client_id, TheDate

The above statement will give me the error 'invalid column name' on TheDate in the group by clause.