CODEWITHSUNDEEP
sqloracle-database
bertday
bertday

Reputation: 10971

Using functions efficiently in Oracle queries

If I have a SQL query in Oracle with a bunch of nested but repetitive functions like:

select trim('0' from trim(' ' from address))
from customers
where
    trim('0' from trim(' ' from address)) <> '1234 MAIN ST' and
    trim('0' from trim(' ' from address)) <> '1234 WOOD ST'

is there some way of caching/naming the result of trim('0' from trim(' ' from address)) so that I'm not running it over and over again?

Upvotes: 0

Views: 43

Answers (3)

Jon Heller
Jon Heller

Reputation: 36922

Use an inline view to create the column with an alias and then reference that alias in the outer query:

select trimmed_address
from
(
    select trim('0' from trim(' ' from address)) trimmed_address
    from customers
)
where trimmed_address not in ('1234 MAIN ST', '1234 WOOD ST');

Upvotes: 1

Aleksej
Aleksej

Reputation: 22969

You can rewrite your query this way:

select trim('0' from trim(' ' from address))
from customers
where
    trim('0' from trim(' ' from address)) NOT IN ( '1234 MAIN ST',
                                                   '1234 WOOD ST',
                                                   ...
                                                 )

Upvotes: 0

msheikh25
msheikh25

Reputation: 578

Something like this:

With temp as (
select trim('0' from trim(' ' from address)) col1 from customers
)
Select * from temp
Where col1 <> '1234 MAIN ST' and
         col1 <> '1234 WOOD ST'

Upvotes: 2

Related Questions