Dennis, I'm not sure what I did incorrectly before, but that was my thought as well. The error came back saying there was no column named DISTINCT, so I must have "fat fingered" it or something. I just assumed from the error message that this was not supported in R:Base. But your query worked exactly as I wanted, so I must have just typed it wrong.
Thanks!! _____ From: [email protected] [mailto:[email protected]] On Behalf Of Dennis McGrath Sent: Wednesday, April 01, 2009 2:00 PM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: There should never be more than one, but... select count(DISTINCT CID) into vMyCIDCount from ARTRANS where INVOICE# = .vINVOICE# Dennis McGrath _____ From: [email protected] [mailto:[email protected]] On Behalf Of Gray, Damon Sent: Wednesday, April 01, 2009 3:52 PM To: RBASE-L Mailing List Subject: [RBASE-L] - There should never be more than one, but... I'm trying to get a count of customer IDs (CID) tied to an invoice number. There should never be more than one CID per invoice, but these can appear multiple times in the table as customers pay on an invoice and payment entries are made. For example, there are three payments by Client 105 on Invoice 137295. So, I want to run a query to get that count, but the first below is giving me a value of 3, and the 2nd lame attempt doesn't give me anything but an error. How should the syntax read for this? select count(CID) into vMyCIDCount from ARTRANS where INVOICE# = .vINVOICE# (Gives me 3) select count(select distinct CID from ARTRANS where INVOICE# = .vINVOICE#) into vMyCIDCount (Blows chunks) wwwww ( @ @ ) -------oOO---(_)---OOo------- Damon J. Gray Anvil Corporation Business Services (360) 937-0770 oooo0 0oooo ( ) ( ) ----------\ (----) /----------- \__) (__/
