• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 405
  • Last Modified:

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?




0
toooki
Asked:
toooki
2 Solutions
 
kambleamarCommented:
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
0
 
toookiAuthor Commented:
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),
0
 
yuchingCommented:
You can do like this
WITH t AS  (SELECT F1, F3, MYFUNCT(F2) as F2_updated
FROM MYTAB)
SELECT * FROM t
WHERE F3 = F2_updated;

Open in new window

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
sdstuberCommented:
the WITH clause above above should work
or you can put your query inline, which the optimizer might do for you internally anyway.

select f1, f2_updated from
(SELECT F1, F3, MYFUNCT(F2) as F2_updated
FROM MYTAB
) m
WHERE m.F3 = f2_updated;
0
 
toookiAuthor Commented:
Thank you all.
I am going to test the queries you sent to me.
0
 
ste5anSenior DeveloperCommented:
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 <--
0
 
toookiAuthor Commented:
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..
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now