sql strange, VSTUDIO and V4MD 4.3

[Christian - ChrisKatDev.com]

Le 9 juin 2010 à 14:22, Ivan Smahin a écrit :

> Hello Christian,
Hello Ivan,
> 
> Wednesday, June 9, 2010, 2:58:57 PM, you wrote:
> 
> 
>> Le 9 juin 2010 à 09:56, Ivan Smahin a écrit :
> 
>>> Hello Christian,
>>> 
>>> Wednesday, June 9, 2010, 2:47:20 AM, you wrote:
>>> 
>>>> Hello,
>>> 
>>>> I have this request :
>>>> SELECT t1.idScrap, (t1.pertinence + t2.pertinence) as s FROM
>>>> kw_in_scrap as t1, kw_in_scrap as t2 WHERE (t1.kw = 'musique' and
>>>> t2.kw = 'cuivre' and t1.idScrap = t2.idScrap) ORDER BY s DESC
>>> 
>>>> but I have wrong values for s in the results and I don't know why.
>>> 
>>>> Example :
>>>> idscrap, kw, pertinence
>>>> 1, musique, 1
>>>> 1, cuivre, 0.25
>>>> 1, son, 0.5
>>>> 2, musique, 1
>>>> 2, bruit, 0.25
>>>> 2, cuivre, 0.3333
>>> 
>>>> I want to have :
>>>> idscrap, s
>>>> 1, 1.25
>>>> 2, 1.3333
>>> 
>>>> but I have
>>>> 2, 0.6666
>>>> 1, 0.5
>>> 
>>> Seems to be a bug. Please report it to the Mantis.
>>> 
>>> 
>>>> second problem : in Director, order by s is ignored and Valentina make an order by recid in fact...
>>> 
>>> You   mean   it  is expected sort order with vStudio and no sort order
>>> with V4MD?
> 
>> yes, but in two cases, "s" is not correct
> 
> Agree. It will be fixed ASAP. Please report it to Mantis.
> 
>>> If  so  then  you should just update your V4MD -  because it was fixed
>>> already.
> 
>> in which version?
>> have you an url ?
> 
> I guess it was in 4.5
> 
> BTW the workaround is -
> 
> Group all the records you want and sum pertinence.
> Something like this:
> 
> SELECT
>        idScrap, 
>        SUM(pertinence) as s 
> FROM 
>        kw_in_scrap
> WHERE 
>                kw = 'musique' 
>        OR  kw = 'cuivre' 
> GROUP BY
>        idScrap
> ORDER BY 
>        s DESC             


This request doesn't match what I'm expecting for.

In fact, I want to find idscrap which has kw1 AND kw2 and I want to have the addition of pertinence of kw1 and pertinence of kw2 for this idscrap.
I don't want idscrap which has only one of the kw and I don't want the global sum of pertinence.
>                                      

br
Christian
> 
> 
> -- 
> Best regards,
> Ivan Smahin 
> Senior Software Engineer
> Paradigma Software, Inc
> Valentina - The Ultra-Fast Database
> http://www.valentina-db.com
> 
> _______________________________________________
> Valentina mailing list
> Valentina at lists.macserve.net
> http://lists.macserve.net/mailman/listinfo/valentina

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.macserve.net/pipermail/valentina/attachments/20100609/0779415d/attachment.html