help with SELECT grouping

[Judi201]

Hi,

I am trying to create a cursor to show invoices in a grid.
I have a textbox for Customer number and a checkbox for ALL or UNPAID invoices. I tried to put it in an IIF() but couldn't get it to work so I split it up but still don't get what I expected. I guess with all the help I have had, I still don't get the grouping.

Data looks like this:

custno Date invoice# debit credit
200 ----- 10001 500.00
200 ----- 10001 500.00
201 ----- 10002 800.00
201 ----- 10002 200.00
201 ----- 10002 600.00

I thought I could group by the invoice# and compare
debit - credit to see if invoice was paid but I can't get it to work.

Am I wrong about what I should expect here?

LOCAL lnchk, lnCustno
lnCustno = VAL(This.Parent.txtCustno.Value)
lnchk = This.Value
This.Parent.grdTicket.RecordSource =""
IF lnchk
	SELECT custno, invcnumb, invcdate, invcdesc, invcdebt, invccred, invcwght, invctype ;
		FROM ticket ;
		ORDER BY Ticket.custno, ticket.invcnumb ;
		GROUP BY Ticket.custno, ticket.invcnumb	;	
		WHERE ticket.custno = lncustno AND ;
			(ticket.invcdebt - ticket.invccred) > 0.00) ;  
		INTO CURSOR csrTicket
ELSE
	SELECT custno, invcnumb, invcdate, invcdesc, invcdebt, invccred, invcwght, invctype ;
		FROM ticket ;
		ORDER BY Ticket.custno, ticket.invcnumb ;
		WHERE ticket.custno = lncustno ;
		INTO CURSOR csrTicket
ENDIF
This.Parent.grdTicket.RecordSource = "csrTicket"
Thisform.Refresh()

Would someone give me a push in the right direction here?

Appreciate your time and any suggestions.

Judi

 

[TamarGranor]

When you're grouping in a query, you want to put in the field only two kinds of fields:

1) the fields you're going to group on;
2) expressions involving the aggregate functions.

While VFP 7 and earlier let you put other fields, you get random results for them.

In your case, you want to do something like this:

    SELECT custno, invcnumb, SUM(invcdebt-invccred) AS nBalance;
        FROM ticket ;
        WHERE ticket.custno = lncustno ;
        ORDER BY Ticket.custno, ticket.invcnumb ;
        GROUP BY Ticket.custno, ticket.invcnumb    ;    
        INTO CURSOR csrTicket

To list only unpaid invoices, add this line after the GROUP BY:

HAVING nBalance > 0

Tamar

 

[Judi201]

Tamar,

I am sitting here reading your "Taming Visual FOXPRO SQL" and trying to see how I can display the other info while doing as you suggested above.

By the time I released that post I remembered about only grouping on aggregate functions and came back change my post.

The miracle of this site, you were already here with a reply.

I understand what you are showing me but how can I show the other fields in the grid??

Thanks so much for you help.

Judi

 

[JimWWinter]

Judi,

You could add a sum(invcdebt) as debit and sum(invccred) as credit to the field statement and add the other fields to both the field list and the group by statement. Individual debits and credits would be a little beyond the scope of the sql query though.

Regards,
Jim

 

[Judi201]

Jim,

Thanks for the response. I don't need the individual debits as much as I need the date, desc and weight to show in the grid. These will be the same on all tickets.

I'm wondering what putting these fields in the SELECT list and the GROUP BY list might do to the result. Would that be safe for what I need?

Any suggestions appreciated.

Judi

 

[TheRambler]

Try doing it in two steps: First, like Tamar suggested:

SELECT custno, invcnumb, SUM(invcdebt-invccred) AS nBalance ;
    FROM ticket ;
    WHERE ticket.custno = lncustno ;
    ORDER BY Ticket.custno, ticket.invcnumb ;
    GROUP BY Ticket.custno, ticket.invcnumb ;
    HAVING nBalance > 0 ;
    INTO CURSOR csrFirst

* second, JOIN the other fields

SELECT a.custno, a.invcnumb, a.nBalance, ;
       b.invcdate, b.invcdesc, b.invcwght ;
    FROM csrFirst a ;
    JOIN ticket b ;
    ON a.custno = b.custno AND a.invcnumb = b.invcnumb ;
    INTO CURSOR csrTicket

 

[Judi201]

TheRambler,

Thanks so much. That should do it fine. I'll try it as soon as I can and post back.

Judi

 

[Judi201]

TheRambler and all who replied,

I have decided to use another approach on this problem (however TheRambler's code seemed to work fine) but I took that example and solved another much bigger problem I had in another place.

I can look at all these examples but until something solves a problem for me it just doesn't seem to stick.

Thanks to all for your time and help.

Judi