Actually the version of my code presented here was a bit confusing because I described it as within a loop, and the database I'm working with records users who are caught in a "log-in loop". I got a solution from another ColdFusion person. Looks like this, with different column names to prevent confusion ...
SELECT UsrIP, rec_num, LastVisit, UsrReturns, (SELECT COUNT(UsrIP) FROM TableName WHERE UsrIP = '#ListElement#' AND NOT UsrReturns = 0 GROUP BY UsrIP) AS Nmbr_IPaddrs FROM TableName WHERE UsrIP = '#ListElement#' AND NOT UsrReturns = 0 Keith Purtell, Web/Network Administrator VantageMed Operations (Kansas City) Email: [EMAIL PROTECTED] CONFIDENTIALITY NOTICE: This email message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Dunwiddie, Bruce Sent: Monday, January 07, 2002 2:38 PM To: '[EMAIL PROTECTED]' Subject: RE: [KCFusion] Retrieving alias from SQL sub-query if I understand what you're saying, this should give you what you want in one query... select loopip, rec_num, lastvisit, count(loopip) as loop_count from tablename where loopip in (#listqualify(listofips, "'")#) group by loopip -----Original Message----- From: Keith Purtell [mailto:[EMAIL PROTECTED]] Sent: Monday, January 07, 2002 12:20 PM To: KCFusion (E-mail) Subject: [KCFusion] Retrieving alias from SQL sub-query Slightly off topic, but I'm having difficulty retrieving an alias from a sub-query. I start with a list of IP addresses, then loop through the list with this query: SELECT loopIP, rec_num, lastvisit, loop_count FROM TableName WHERE loopIP = '#ListElement#' AND NOT loop_count = 0 AND EXISTS (SELECT COUNT(loopIP) AS Count_IP FROM TableName WHERE loopIP = '#ListElement#' AND NOT loop_count = 0 GROUP BY loopIP) My goal is to CFOUTPUT table rows including one column that shows how many times the IP address in that record appears in the database. But when I try this I get error messages saying the server doesn't recognize Count_IP (my alias for the count). The SQL runs fine; the error occurs when CFOUTPUT is attempted. Any tips? TIA. Keith Purtell, Web/Network Administrator VantageMed Operations (Kansas City) Email: [EMAIL PROTECTED] CONFIDENTIALITY NOTICE: This email message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. ______________________________________________________________________ The KCFusion.org list and website is hosted by Humankind Systems, Inc. List Archives........ http://www.mail-archive.com/cf-list@kcfusion.org Questions, Comments or Glowing Praise.. mailto:[EMAIL PROTECTED] To Subscribe.................... mailto:[EMAIL PROTECTED] To Unsubscribe................ mailto:[EMAIL PROTECTED]