Sql statement 2

[DannC]

Hi,

Q1: I'm trying to select 3 fields from a table as id, name and the third one is a sum() from a second table. The Sql looks like this
sele a.id, a.name, sum(b.val) from a into cursor aTmp but I get only one records. I manage to select all with a Union statement but it works slow. Anyone has a better idea ?

Q2: In a listbox are 3 items added with addlistitem("a",1) .... When I remove one of them, the listindex remains unchanged (normal). I'd like to know if it is a way to change it.

Thanks a lot !

 

[TomasDill]

If you want to get a sum for each name, do following:

sele a.id, a.name, sum(b.val) from ... group by a.id, a.name

Yes, you can change the listindex property of combobox to any value greater zero (index of item in list) or zero (no item selected)). Usually combobox.Refresh is enough to change the list index and show item that is appropriate to the value. If you just removed item from combobox list, ListIndex becomes 0 that means no item selected.

HTH.

Vlad Grynchyshyn
vgryn@softserve.lviv.ua

http://www.softservecom.com

The professional level of programmer could be determined by level of stupidity of his/her bugs

 

[DannC]

Hy,

Thanks, TomasDill that helped but now i've runned in other Sql Trap.

I have 2 tables:
t1: (Id c(10),name C(20))
t2: (id c(10), val n(16.2))

and i'm trying to select all the records from t1 in 3 columns like t1.id, t1.name, val where val has to have the value of t2.val wich depends upon the Id.
I've done
sele t1.id, t1.name, iif(t1.id=t2.id,t2.val,0) as val from t1 into cursor crsT
but it seems that only the first id if found
Ex:
t1:
1 a
2 b
3 c
4 d

t2:
1 10
2 20

=====

1 a 10
2 b 0 ?


Thanks a lot !!!

 

[ramani]

Hi
Try the following code

SELECT t1.id, t1.name, SUM(t2.val) FROM t1, t2 ;
WHERE t1.id = t2.id INTO CURSOR crsT ;
GROUP BY t1.id ORDER BY t1.id

This will get you the result. ramani :-9
(Subramanian.G)
FoxAcc
ramani_g@yahoo.com
LET KNOW IF THIS HELPED. ENOUGH EXPERTS ARE HERE TO HELP YOU OUT! BEST OF LUCK

 

[TomasDill]

Hi!

I guess it would be:

SELECT t1.id, t1.name, NVL(t2.val,0) FROM t1 LEFT JOIN t2 ;
ON t1.id = t2.id INTO CURSOR crsT

Well, if you need a sum on val field, it will be:
SELECT t1.id, t1.name, sum(NVL(t2.val,0)) FROM t1 LEFT JOIN t2 ;
ON t1.id = t2.id GRUP BY t1.id INTO CURSOR crsT

To ramani: Is it needed to use ORDER BY
t1.id when you already have group by that field? Grouping by field in VFP makes sorting on that field automatically, as far as I know.

Vlad Grynchyshyn
vgryn@softserve.lviv.ua

http://www.softservecom.com

The professional level of programmer could be determined by level of stupidity of his/her bugs