Complex methods

[Ruslan Zasukhin]

On 1/21/06 5:31 PM, "jda" <jda at his.com> wrote:

Hi Jon,
 
> I think I know the answer to this, but I thought I'd ask...
> 
> I want to string together two string operations in a method. The
> first strips out certain words from the string. The second returns
> the first line (ending in a return charcater) in that string.
> 
> I have done this, and it works, but the SQL code for each section of
> a CASE/WHEN/ELSE clause is enormous (here's an example from a single
> WHEN clause, for just one word to be removed from the string):
> 
> IF(Left((CASE WHEN
> LOCATE(UPPER('al-'),UPPER(coalesce(authors,editors)))=1 THEN
> RIGHT(coalesce(authors,editors), LENGTH(coalesce(authors,editors)) -
> LENGTH('al-')) WHEN LOCATE(UPPER('von
> '),UPPER(coalesce(authors,editors)))=1 THEN
> RIGHT(coalesce(authors,editors), LENGTH(coalesce(authors,editors)) -
> LENGTH('von ')) WHEN LOCATE(UPPER('the
> '),UPPER(coalesce(authors,editors)))=1 THEN
> RIGHT(coalesce(authors,editors), LENGTH(coalesce(authors,editors)) -
> LENGTH('the ')) ELSE coalesce(authors,editors) END)),
> (LOCATE(chr(13), (CASE WHEN
> LOCATE(UPPER('al-'),UPPER(coalesce(authors,editors)))=1 THEN
> RIGHT(coalesce(authors,editors), LENGTH(coalesce(authors,editors)) -
> LENGTH('al-'))
> 
> This makes debugging terribly difficult, and of course can easily
> generate SQL statements of thousands of characters.
> 
> The question is, is there a simple way to get the result of the first
> SQL query and then feed that to the second, all within one SQL
> statement? I suppose I can create temporary fields to handle
> sequential calls, but I'd rather not (that gets complex, too).

If you want in SINGLE QUERY and simpler,
then MAY BE you can try subselects.

Also you can think about methods. But NOT indexed.
And extract some part of formula into method(s)


-- 
Best regards,

Ruslan Zasukhin
VP Engineering and New Technology
Paradigma Software, Inc

Valentina - Joining Worlds of Information
http://www.paradigmasoft.com

[I feel the need: the need for speed]