[Vserve+V4RB] Weird concatenated fields behaviour in searches

[Ruslan Zasukhin]

On 12/17/04 1:24 PM, "Eric Ferrer" <wonderfef at noos.fr> wrote:

Hi Eric,

> Maybe this is normal, but it looks weird to me:
> 
> I have these tables :
> 
> FAXES
>     ID
>     Subject
>     ParentRecipientID
> PEOPLE
>     ID
>     FirstName
>     LastName
>     AllNames METHOD( CONCAT(FirstName, ' ', UPPER(LastName)) )
> 
> Note that Faxes may not have a 'ParentRecipientID', where the recipient is
> unknown (field is empty).

More correct to say NULL, right ?


> When I want to display a list of faxes :
> 
> SELECT Subject, ParentRecipientID, People.AllNames
> FROM Faxes, People
> WHERE Faxes.ParentRecipientID *= People.ID

May be I ma wrong, but it seems you need right outer  =*
Check just in case.

> Valentina always tries to fill 'AllNames' with a value, even if
> ParentRecipientID is empty :
> 
> Untitled fax            PPL123      John SMITH     // this one is good
> Untiltled fax 2                         John SMITH     // John again !
> Untitled fax 3                          John SMITH     // argh ! Him again !
> Untitled fax 4         PPL234      Mary GLUCK    // ok for this one
> Untitled fax 5                          Mary GLUCK    // Mary again !
> 
> If the first found record has no ParentRecipientID, Valentina seems to "pick
> up randomly" someone in the People table :
> 
> Untitled fax                             Alfred SCHOPF     // why him ?
> Untiltled fax 2                         Alfred SCHOPF     // again !
> Untitled fax 3                          Alfred SCHOPF     // again !
> Untitled fax 4         PPL234      Mary GLUCK        // ok for this one
> Untitled fax 5                          Mary GLUCK        // mary again !
> 
> 
> If I test with this query :
> SELECT Subject, ParentRecipientID,
>             People.FirstName, People.LastName, People.AllNames
> FROM Faxes, People
> WHERE Faxes.ParentRecipientID *= People.ID
> 
> I get good results, except for "AllNames" field :
> 
> fax1                                     Alfred SCHOPF
> fax2    PPL123   John Smith  John SMITH
> fax3                                     John SMITH
> fax4    PPL234  Mary Gluck  Mary GLUCK
> fax5                                    Mary GLUCK
> fax6                                    Mary GLUCK
> 
> Thanks in advance for your help

If still problems please send me your db + query text


-- 
Best regards,
Ruslan Zasukhin      [ I feel the need...the need for speed ]
-------------------------------------------------------------
e-mail: ruslan at paradigmasoft.com
web: http://www.paradigmasoft.com

To subscribe to the Valentina mail list go to:
http://lists.macserve.net/mailman/listinfo/valentina
-------------------------------------------------------------