asked on

Reusing a function in a SQL command

I need to write an SQL command (Oraclee 11gR2) like this:

SELECT F1, MYFUNCT(F2) as F2_updated
FROM MYTAB m
WHERE m.F3 = MYFUNCT(F2) ;
-- WHERE m.F3 = F2_updated does not seem to work

How can I reuse the value "F2_updated" I already obtained in the SELECT clause when I am writing the WHERE clause?
I made the example simple but I need to use "F2_updated" multiple times inside the WHERE clause. How do I simplify this?

kambleamar

Helllo,

Do you want to write a function that will return you the multiple occurence of a substring in a whole string

like "aaaBsdfghm,weqweqBrtgdhsadsa,Bdiuydia"
so we need to B here 3 times
if it is the soution you want
i would change the the start of position every time i get return the substring matched

substr( string, start_position, [ length ] )
so change the start position every time

toooki

ASKER

Thank you
But no I am using the MYFUNCT it is not string manipulation It has some business logic. I compute the function Output in select statement. How do I reuse the value in the where clause? (w/o calling the function again),

SOLUTION

yuching

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

toooki

ASKER

Thank you all.
I am going to test the queries you sent to me.

ste5an

hi,

the a look at the execution plan, the function is only executed once, cause you're calling it with the same parameter, thus it is already reused.

mfG
--> stefan <--

toooki

ASKER

Than you.

select f1, f2_updated from
(SELECT F1, F3, MYFUNCT(F2) as F2_updated
FROM MYTAB
) m
WHERE m.F3 = f2_updated;

and

WITH t AS (SELECT F1, F3, MYFUNCT(F2) as F2_updated
FROM MYTAB)
SELECT * FROM t
WHERE F3 = F2_updated;

both worked for me..