Leaving a column out of 'select *'
I've found plenty of articles and suggestions that someone should never want to do this. I do. Really, I do.
Here's the scenario. I've got a query will roll up attributes based on the Category with the most recent effective date. That looks something like this:
select i."Item Nbr",attr.*
from items i
select *, rank() over(partition by "Category Nbr" order by "Effective Date" desc, "Item Nbr" desc) as Rnk
on i."Category Nbr" = attr."Category Nbr"
and attr.Rnk = 1
The problem is, the outer query lists Item Nbr twice, and I don't want the second one. Items will have a variable amount of columns that is subject to change, so I don't want to specify all of my columns because this is going to go in a view that gets automatically recreated every night.
So it would help if I could do something like:
select i."Item Nbr",attr.* [except "Item Nbr"]
Either that, or being able to rename one column in the view and dropping the other column from the view as part of my nightly script. I'd be happy with either solution or something else that lets me do what I need without specifying all of the columns. Whatever it is, I should only end up with i."Item Nbr" and NOT attr."Item Nbr".
Thanks in advance for your help!