SQL Help required, again

[bebbo]

I wrote the statement below. Basically I trying to sum all transaction for the dates involved. Postr keeps the transaction status etc... Selll keeps the items sold, quantity and discount and payli keeps the amount recieved. I'm trying to check that the totals for selll match the totals for payli, as they should do but sometimes I fear they don't

THANKS!!!!!!

Fromdate = CTOD("01/10/2003&quot
Todate = CTOD("06/11/2003&quot
set default to "C:\3100"
SELECT POSTR.POSID , ;
POSTR.TRANS , ;
SUM(payli.amount) as Amount, ;
SUM((SellL.OpenDeptSP * SellL.Quantity)- SellL.Discount) AS StockAm ;
FROM POSTR ;
` INNER JOIN selll ;
ON PADL(Postr.POSId,2,"0&quot + PADL(Postr.TRANS,10,"0&quot = ;
PADL(Selll.POSId,2,"0&quot + PADL(Selll.TRANS,10,"0&quot ;
LEFT OUTER JOIN Payli ;
ON PADL(Selll.POSId,2,"0&quot + PADL(Selll.TRANS,10,"0&quot = ;
PADL(Payli.POSId,2,"0&quot + PADL(Payli.TRANS,10,"0&quot ;
INTO CURSOR TEMP ;
Where POSTR.STATUS = 0 ;
AND BETWEEN(POSTR.Date, Fromdate, Todate) ;
GROUP BY 1,2

 

[bebbo]

I had a "'" mark in one line, thats why it wouldn't compile!!!

 

[bebbo]

OK this statement nearly works. However it appears that some of my workings (sum) are doubling up. Any ideas???

 

[alvechurchdata]

Try doing the joins in two separate statements so that you can view intermediate results.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[nlohrey]

I am having difficulty with the syntax for a simple "left outer join" select statement. Can anyone give an a quick
example of the exact syntax?
Something like this---

Select a.custno, a.name, b.code;
From custno a, secno b;
Where a.custno = b.custno ;
Into table newcust

I'm not sure where you put the LEFT Outer Join words.
Tried in the "from" clause and keep getting syntax
errors.

Any quick response will be greatly appreciated

 

[nlohrey]

I am having difficulty with the syntax for a simple "left outer join" select statement. Can anyone give an a quick
example of the exact syntax?
Something like this---

Select a.custno, a.name, b.code;
From custno a, secno b;
Where a.custno = b.custno ;
Into table newcust

I'm not sure where you put the LEFT Outer Join words.
Tried in the "from" clause and keep getting syntax
errors.

Any quick response will be greatly appreciated

 

[Mike Gagnon]

nlohrey

Here ia an example:

SELECT Client.adddate, Client.adduser, Client.icid, Custplan.ani;
 FROM  suntel!client LEFT OUTER JOIN suntel!custplan ;
   ON  Client.icid = Custplan.icid

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[ramani]

Hi

SELECT a.custno, a.name, b.code ;
FROM custno a ;
LEFT OUTER JOIN secno b ON a.custno = b.custno ;
INTO DBF newcust



____________________________________________
ramani - (Subramanian.G)

http://winnersoft.coolfreepages.com/

When you ask VFP questions, please add VFP version.