help convert access query to oracle 1

[amortillaro]

Hi:
This is my access query

Select (EmFirst || EmLast) theName,
RTrim(theName) theNameTrimmed
From EmTable;

The point is that in msAccess I do can reference a field (theName) defined inside the same query. Can I do it in oracle?

Thanks a lot, in advance...

 

[SantaMufasa]

Amortillaro,

The only clause in which Oracle SQL allows you to refer to a column alias (example: theName) is in the "ORDERY BY" clause. To achieve the result you want, you say:

Select EmFirst || EmLast theName,
RTrim(EmFirst || EmLast) theNameTrimmed
From EmTable
order by theName -- I added this for illustration
;

Mufasa
(aka Dave of Sandy, Utah, USA @ 18:39 (14Jan04) GMT, 11:39 (14Jan04) Mountain Time)

 

[SantaMufasa]

"ORDERY BY" ... right... I love making up my own syntax. Obviously should have been "ORDER BY". (Don't you wish Tek-Tips had some sort of "repentance" feature that let you fix errors after you hit the [Submit Post] button?)

Mufasa
(aka Dave of Sandy, Utah, USA @ 18:42 (14Jan04) GMT, 11:42 (14Jan04) Mountain Time)

 

[amortillaro]

Hi Mufasa: here is a question:
I use the concatenation just as an example, but the function that take place is quite complicated.
Here is the question: is in any way the following solution, a beter way, lets say, in terms of optimization or server "load":

Select theName, RTrim(theName) theNameTrimmed
FROM (
Select (EmFirst || EmLast) theName From EmTable) subquery

Thanks.. Aishel

 

[SantaMufasa]

Aishel,

There should be no extra load on the server. As I recall, for a given row, Oracle does not recalculate intermediate results from operations since the results are still accessible for that same row. Bottom line: don't worry about re-stating the expression.

Your alternative is clever, as well, to simplify the code. It, too, does not generate extra load, so I would choose your SELECT-in-a-SELECT choice for the sake of readability.

Mufasa
(aka Dave of Sandy, Utah, USA @ 20:02 (14Jan04) GMT, 13:02 (14Jan04) Mountain Time)

 

[amortillaro]

Finally, the expression is so complicated that this is definitely the best solution, if there is no extra load in the server, that is even better.
Thanks a lot.. Aishel