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 >>> >>> >>> >>> >>> >>> >> >> >> >> >> > > > >
