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?
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'
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');
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',
...
)
