Eurico,
I can get your results as a subset of a SQL statement but without knowing
more about what you actually want I can't go any further.
If you look at the SQL below you need records 1,2,4,7 and 11 but the
wuestion is WHY?
Can you explain verbally your requirements?
Select All ;
"A" as Rec_Type, ;
" " as OK, ;
SB.Supplier, ;
SB.Client, ;
SB.Card, ;
Sum(SB.qtdB), ;
Sum(SC.qtdC) ;
>From SB ;
Join SC On SC.Supplier=SB.Supplier And SC.Card=SB.Card And
SC.Client=SB.Client ;
Group By 2,3,4 ;
Union All ;
Select All ;
"B" as Rec_Type, ;
" " as OK, ;
SA.Supplier, ;
SB.Client, ;
SB.Card, ;
Sum(SB.qtdB), ;
0 as qtdc ;
>From SA ;
Join SB On SB.Supplier=SA.Supplier And SB.Card=SA.Card ;
Group By 2,3,4 ;
Union All ;
Select All ;
"C" as Rec_Type, ;
" " as OK, ;
SA.Supplier, ;
SC.Client, ;
SC.Card, ;
0 as qtdb, ;
Sum(SC.qtdc) as qtdc ;
>From SA ;
Join SC On SC.Supplier=SA.Supplier And SC.Card=SA.Card ;
Group By 1,2,3,4, 5 ;
Into Cursor curB Readwrite ;
Order By 1,2,3,4,5
Dave Crozier
The secret to staying young is to live honestly, eat slowly, and to lie
about your age
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf
Of Eurico
Sent: 30 October 2006 11:29
To: 'ProFox Email List'
Subject: SQL statement
Hi
I need another way of doing this...
create table sA(supplier I, card I)
create table sB(supplier I,client I, card I, qtdB I) create table
sC(supplier I,client I, card I, qtdC I)
*-----
insert into sA(supplier, card) values(1, 10) insert into sA(supplier, card)
values(1, 20) insert into sA(supplier, card) values(2, 10)
*--------
insert into sB(supplier, client, card, qtdB) values(1,1,10,30) insert into
sB(supplier, client, card, qtdB) values(1,2,10,10) insert into sB(supplier,
client, card, qtdB) values(2,1,10,14) insert into sB(supplier, client, card,
qtdB) values(1,3,20,30) insert into sB(supplier, client, card, qtdB)
values(2,2,10,15)
*--------
insert into sC(supplier, client, card, qtdC) values(1,1,10,20) insert into
sC(supplier, client, card, qtdC) values(1,1,20,10) insert into sC(supplier,
client, card, qtdC) values(2,1,10,15) insert into sC(supplier, client, card,
qtdC) values(2,4,10,30)
*----------
Results
supplier client card qtdB qtdC
1 1 10 30 20
1 2 10 10 0
1 3 20 20 10
2 1 10 14 15
2 2 10 15 0
2 4 10 0 30
TIA, Eurico.
[excessive quoting removed by server]
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
