Bill, I just had to step back and look at this again. The DISTINCTness is really at the CustPO level. So SELECT COUNT(DISTINCT CustPO) INTO vCountWOBILLID FROM Invoiceing WHERE Status = 'D' AND BillingType = 'G' provides the correct answer.
Thanks for giving me some ideas. Jan -----Original Message----- From: Bill Downall <[email protected]> To: [email protected] (RBASE-L Mailing List) Date: Mon, 7 Dec 2009 14:54:31 -0500 Subject: [RBASE-L] - Re: SELECT DISTINCT I'm sorry. Forget the distinct. Just do the where clause and the group by, after a plain and simple SELECT COUNT (*). Bill On Mon, Dec 7, 2009 at 2:51 PM, jan johansen <[email protected]> wrote: Bill, At first glance your suggestion should work but I get Too Many Rows returned so something else is going on. I am digging and will let you know. Jan -----Original Message----- From: Bill Downall <[email protected]> To: [email protected] (RBASE-L Mailing List) Date: Mon, 7 Dec 2009 13:01:58 -0500 Subject: [RBASE-L] - Re: SELECT DISTINCT Whoa. Disappeared again, but I looked at the email original/source, and found the column names. SELECT COUNT (DISTINCT colname) is different from SELECT DISTINCT columnlist, in that COUNT works like the aggregate functions MIN, MAX, AVG, and SUM: it requires a single column What I think you want in your last command is: SELECT COUNT (*) INTO vCountWOBILLID + FROM Invoiceing + WHERE Status = 'D' AND BillingType = 'G' + GROUP BY WoBillID, CustPO At least, that will give you the count of the number of distinct combinations of BillID and CustPO. Also, your original was missing an = sign between BillingType and 'G'. Bill On Mon, Dec 7, 2009 at 12:51 PM, jan johansen < [email protected]> wrote: Bill, You are absolutely right! That was weird. I just copied the command and the column(s) disappeared. Here they are again SELECT (DISTINCT ) INTO vCountWOBILLID FROM Invoiceing + WHERE Status = 'D' AND BillingType = 'G' DECLARE Invoice CURSOR FOR SELECT + DISTINCT WOBILLID , CustPO FROM Invoiceing + WHERE Status = 'D' AND BillingType = 'G' COUNT WOBILLID So if I change count to SELECT COUNT(DISTINCT ) INTO vCountWOBILLID FROM Invoiceing + WHERE Status = 'D' AND BillingType 'G'it throws an error WOBILLID,CustPO Jan -----Original Message----- From: Bill Downall < [email protected]> To: [email protected] (RBASE-L Mailing List) Date: Mon, 7 Dec 2009 12:43:17 -0500 Subject: [RBASE-L] - Re: SELECT DISTINCT Jan, DISTINCT needs a column name after it. Bill On Mon, Dec 7, 2009 at 12:39 PM, jan johansen < [email protected]> wrote: Group, This morning as I was troubleshooting a process for generating invoices, I ran into challenge I have a cursor that works fine and generated 39 invoices. The problem was that my method to determine how many invoices would print calculated 33 invoices. While a minor issue, I need a better to count. My cursor has the following; DECLARE + DISTINCT WOBILLID , CustPO FROM Invoiceing + WHERE Status = 'D' AND BillingType = 'G' Invoice CURSOR FOR SELECT My calculation has; SELECT (DISTINCT ) INTO vCountWOBILLID FROM Invoiceing + WHERE Status = 'D' AND BillingType = 'G' COUNT WOBILLID I know, I know. They are different. However when I tried to change my SELECT to SELECT COUNT(DISTINCT ) INTO vCountWOBILLID FROM Invoiceing + WHERE Status = 'D' AND BillingType = 'G' WOBILLID,CustPO I get an error. My suspicion is that the aggregate COUNT doesn't like it. I just found it interesting that I could declare a distinct cursor on 2 columns but not count distinct on 2 columns. The reason for the distinct is that a customer could send several different po's during a billing cycle Also the possibility exists that 2 different customers could use the same PO. Any suggestions are appreciated. Jan -----Original Message----- From: Lawrence Lustig <[email protected]> To: [email protected] (RBASE-L Mailing List) Date: Mon, 7 Dec 2009 09:22:30 -0800 (PST) Subject: [RBASE-L] - Re: 7.6 Entry/Edit form << I do not want the user to be able to add additional rows when in EDIT mode. >> Try (in the AFTER START EEP): IF RBTI_FORM_MODE = 'EDIT' THEN PROPERTY TABLE YourTableName 'DISABLE_ADD_NEW_ROWS' ENDIF -- Larry
