sql question

[Phlegmak]

I want to do something like this:

select id as myid from invoice where myid='1'

In other words, I want to use an alias for a column in the where clause. My example above doesn't work. Is there any sql trickery I can do to make something like this work? This is for Oracle.

 

[warpus]

I want to do something like this:

select id as myid from invoice where myid='1'

In other words, I want to use an alias for a column in the where clause. My example above doesn't work. Is there any sql trickery I can do to make something like this work? This is for Oracle.

what prevents you from going:

select id as myid
from invoice
where id = 1

? there must be a reason, but in the example you provide it isn't evident

 

[Phlegmak]

what prevents you from going:

select id as myid
from invoice
where id = 1

? there must be a reason, but in the example you provide it isn't evident

It would take too long to explain. I just want to know some kind of way to use an alias in a query.

 

[Arcadian83]

Please someone correct me if I am wrong, but I don't think you can do that. By definition, an alias is renaming one of the columns of the result of the query. That column is derived from the query's execution, so to use it in formulating the query is circular.

The Id column in the result set is a different column (although when unmodified it may happen to be the same) from those you are selecting from.

It's like taking x+y=z and trying to make it (x+z)+y=z

Hope that helps

 

[warpus]

Yeah, you're going to have to refer back to the actual columns (or function calls on the columns, such as LEFT) in your WHERE clause, while remaining free to declare aliases in your SELECT statement.

I can't think of any possible reason you would want to use an alias in your WHERE statement... but if you described the exact situation, I would be happy to figure out the flaw in your logic

 

[Phlegmak]

(At this point, I no longer need the help.)

Here's an example.

select totalamount + totalcharge total from invoice where totalamount + totalcharge > 1000

That's from one of our tables. Notice that I had to put the calculation for a total twice. That's the sort of thing I want to avoid. I hate that.

Another guy over here suggested I can do something like this:

select * from (select totalamount + totalcharge total from invoice ) where total > 1000

But I hate doing that. It would just make perfect sense for Oracle to allow using an alias in the where clause.

 

[warpus]

Another guy over here suggested I can do something like this:

select * from (select totalamount + totalcharge total from invoice ) where total > 1000

But I hate doing that. It would just make perfect sense for Oracle to allow using an alias in the where clause.

You could do what your friend suggests, but it creates unnecessary overhead.

If your only reason for wanting to use an alias in the where clause is hating it.. well.. that's how it works, you're gonna have to get used to it