Re: Calling user defined function in SQL where clause

One option is to rewrite your function
to return an object table type. Then
your query can cast the return value
to a relational table.

Check my website under:

    Miscellaneous -> in-lists

The example is for bind variables and Oracle 8.0 (which uses the 'the()' operator rather than the 'table()' operator of 8.1) but it should give you the idea.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html

____UK_______April 8th
____UK_______April 22nd
____Denmark__May 21-23rd
____USA_(FL)_May 2nd

Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____UK_(Manchester)_May
____Estonia___June (provisional)
____USA_(CA, TX)_August

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


"r2i" <hpresingu_at_transformpharma.com> wrote in message
news:6535d7a7.0304070729.585bb3e2_at_posting.google.com...


> I require to call a function (for encryption..etc) inside a where

> clause of my sql.

>

> Example:

> select * from table

> Where col = my_func();

>

> The col is defined as a Varchar and this works fine as long as

> my_func() returns a string value.

>

> However, i need to change the sql to handle multiple values in the

> where clause, therefore i changed the sql to

> select * from table

> Where col in (my_func());

>

> now my_func() returns a string which has multiple strings separated

by


> commas.

>

> This also works, as long as myfunc() returns 1 value. The moment i

> modify myfunc() to return multiple strings separated by commas, the

> sql fails to return any results. I tweaked myfunc() to return

multiple


> values, with quotes, without quotes, embedded in double quotes..etc

> but to no affect.

>

> Is there any way, i can get this to work, or find a solution to this

> problem in another way?