SQL Question 1

[bebbo]

I have a sql statement as below. Cardtran or cardt could both hold more than one entry for the same posid and postrans number. Postranh/Postr would only have those numbers once. My sql statement appears to only select one entry from cardt or cardtran unless the trantype number is different. I require all the cardt/cartran entries which meet the criteria. Can anyone see where I've gone wrong or give me general comments on my SQL statement. Thanks


Fromdate = ctod("05/12/2002")
Todate = Fromdate
sCURFILTER = ".AND. CLIENT.SYSREF = 111 .AND. BETWEEN(POSTRANH.DATE, FromDate, ToDate)"
sARCFILTER = ".AND. CLIENT.SYSREF = 111 .AND. BETWEEN(POSTR.DATE, FromDate, ToDate)"
set default to c:\3100

SELECT CARDTRAN.POSID, CARDTRAN.POSTRAN,CARDTRAN.DATE,CARDTRAN.TIME,Client.sysref,;
Title, Firstname, Surname, Cardnum AS Number, Cardtran.amount, Cardtran.Openbal, Trantype.name ;
into cursor temp;
from cardtran ;
LEFT OUTER JOIN POSTRANH ;
ON PADL(CARDTRAN.POSID, 2, '0') + PADL(CARDTRAN.POSTRAN, 10, '0') ;
= PADL(POSTRANH.POSID, 2, '0') + PADL(POSTRANH.TRANS, 10, '0') ;
LEFT OUTER JOIN TRANTYPE ;
ON CARDTRAN.TYPE = TRANTYPE.SYSREF ;
INNER JOIN CARD ;
ON CARDTRAN.CARDNUM = CARD.NUMBER ;
INNER JOIN CLIENT ;
ON CARD.CLIENT = CLIENT.SYSREF ;
WHERE POSTRANH.STATUS = 0;
&sCURFILTER;
GROUP BY 12 ;
UNION ALL ;
SELECT CARDT.POSID, CARDT.POSTRAN AS TRANS ,CARDT.DATE,CARDT.TIME,Client.sysref,;
Title, Firstname, Surname, Cardnum AS Number, Cardt.amount, Cardt.Openbal, Trantype.name ;
from cardt ;
LEFT OUTER JOIN POSTR ;
ON PADL(CARDT.POSID, 2, '0') + PADL(CARDT.POSTRAN, 10, '0') ;
= PADL(POSTR.POSID, 2, '0') + PADL(POSTR.TRANS, 10, '0') ;
LEFT OUTER JOIN TRANTYPE ;
ON CARDT.TYPE = TRANTYPE.SYSREF ;
INNER JOIN CARD ;
ON CARDT.CARDNUm = CARD.NUMBER ;
INNER JOIN CLIENT ;
ON CARD.CLIENT = CLIENT.SYSREF ;
WHERE POSTR.STATUS = 0;
&sARCFILTER ;
GROUP BY 12

*, POSTR.POSID, POSTR.TRANS
* GROUP BY CardNum, POSTRANH.POSID, POSTRANH.TRANS;

SELECT * FROM TEMP ;
INTO CURSOR TEMP2;
ORDER BY SURNAME, FIRSTNAME, DATE, TIME

browse

 

[BPeisch]

I don't see anything that pops out as being wrong, but it's hard to tell without having any data to run it against. Is it possible your filter expression(s) could be filtering more records than you expect?


-BP (Barbara Peisch)

http://www.peisch.com

 

[ramani]

Hi Bebbo

Your GROUP BY 12 is the cause for your answer. You have to change it suitably.




____________________________________________
ramani - (Subramanian.G)

http://winnersoft.coolfreepages.com/

 

[BPeisch]

Good catch Ramani! I missed that group by!


-BP (Barbara Peisch)

http://www.peisch.com

 

[bebbo]

Thanks

I tried the group by this morning. How does group by work. If I do group by 1,2,12. Posid could be repeated along with trans. The transaction type can be reated but I want the total for each transaction type for each transaction. Therefore if I have say the following "part" records:

posid = 1
trans = 2
amount = 1.55
trantype.name = "CASH"

posid = 1
trans = 2
amount = 3.55
trantype.name = "card"

Should my sql result include both transactions? I'll give it ago anyway. Thanks!!!

 

[BPeisch]

You shouldn't be using a GROUP BY at all. GROUP BY will remove all duplicates for the fields you're grouping on. Generally, you only use GROUP BY when you use aggregate function in your field list ( like SUM(), MAX(), MIN(), AVG() ). If you use aggregate functions and you don't use a GROUP BY, you get a single record in your result.


-BP (Barbara Peisch)

http://www.peisch.com