Differences between Valentina 1 and 2

[Sims, John (CDC/CCHP/NCBDDD) (CTR)]

> -----Original Message-----
> From: valentina-bounces at lists.macserve.net 
> [mailto:valentina-bounces at lists.macserve.net] On Behalf Of 
> Carsten Friehe
> Sent: Tuesday, November 07, 2006 10:09 AM
> To: valentina at lists.macserve.net
> Subject: Differences between Valentina 1 and 2
> 
> Hi all!
> 
> I am sitting here since a few days to make the move from 
> Valentina 1 to 2.
> First of all I must say that the first steps were very easy. 
> After looking at the Wiki page for Valentina SQL (is it 
> anywhere in downloadable form
> available?) I must also say that the features are great!
> 
> But now on to my problem with one select that worked with 
> Valentina 1 but not with 2.
> I have three tables:
> A:
> Name (Varchar)
> Number(Long)
> 
> B:
> PtrA (ObjectPtr)
> PtrC (ObjectPtr)
> 
> C:
> Stat1 (Boolean)
> Stat2 (Boolean)
> 
> Between A and B there is a 1:m and between B and C there is a 
> m:1 relation.
> 
> Sure there are more fields, but for this select I don't need more.
> 
> This is the old select:
> Select Name,
>           sum(Number),
>           sum(Stat1),
>           sum(Stat2),
>           count(Stat2),
>           Number
> from A,
>          B,
>          C
> where A.RecID *= B.PtrA and
>            B.PtrC *= C.RecID
> group by Number
> order by Number
> 
> I tried many things but nothing worked. It seems that Boolean 
> Values can no longer be added (I wanted to know how many 
> records have Stat1 and Stat2 set to TRUE). I tried to use 
> "sum(IF(Stat1, 1, 0))" but that always returns 1.
> I tried to use "sum(case Stat1 when True then 1 when False 
> then 0 end)", but this also doesn't work.
> Also the outer join seems not to work. I tried an simpler outer join:
> Select Name,
>            count(Stat2)
> from A,
>          B,
>          C
> where A.RecID *= B.PtrA and
>            B.PtrC *= C.RecID
>            where Stat2 = True
> group by Number
> order by Number
> 
> but I only get one record where I expected two records.
> 
> Can someone help me?
> 
> Best Regards,
> Carsten
> _______________________________________________

Hi Carsten,

If you include Name in the Select clause without an aggregate function,
you need to include Name in the Group By clause.


Select Name,
          sum(Number),
          sum(Stat1),
          sum(Stat2),
          count(Stat2),
          Number
from A,
         B,
         C
where A.RecID *= B.PtrA and
           B.PtrC *= C.RecID
group by Name, Number
order by Number



-John