Depressingly, I'm stuck with using the QoQ due to the design of the page and the business logic built around it.
And thankfully, the variables are all realistically named; I stripped that down just to keep the situation simple in the email. I've yet to try it, but would it make more sense to directly reference the parent queries information vs trying to place it in a variable? So my CFQUERYPARAM would be something like <cfqueryparam cfsqltype="cf_sql_integer" value="#ValueList(Excepts.var, ", ")#" list="yes">)? Matthew R. Nicholson SolTech, Inc. Cell: 770-833-5326 www.soltech.net To find what you seek in the road of life, the best proverb of all is that which says: "Leave no stone unturned." ~Edward Bulwer Lytton -----Original Message----- From: ad...@acfug.org [mailto:ad...@acfug.org] On Behalf Of Cameron Childress Sent: Wednesday, April 13, 2011 1:10 PM To: discussion@acfug.org Subject: Re: [ACFUG Discuss] Handling SQL limitation with CF Two thoughts here: 1) You should use a subquery here too (but I think you know that). 2) I would avoid using "var" as a variable name, or a column name in a select list. It's likely a reserved word. That may not be the solution to your problem, but I'd still avoid using var, value, int, etc as a column name. -Cameron On Wed, Apr 13, 2011 at 1:02 PM, Matthew Nicholson <matthew.nichol...@soltech.net> wrote: > Afternoon All! > > It looks as though the subquery was by far the best option in the previous > situation. > > However, I've run into a similar problem but this one has me confused as a > variable seems to just disappear. > > So, logically, > > <cfstoredproc procedure="name" datasource="#ds#" returncode="yes"> > > </cfstoredproc> > > <cfquery name="Accepts" datasource="#ds#" dbtype="ODBC"> > </cfquery> > > <cfquery name="Excepts" datasource="#ds#" dbtype="ODBC"> > </cfquery> > > <CFIF Accepts.recordCount GT 0> > <CFSET ALoc = ValueList(Accepts.var, ", ")> > <CFELSE> > <CFSET ALoc = 0> > </CFIF> > <CFIF Excepts.recordCount GT 0> > <CFSET ELoc = ValueList(Excepts.var, ", ")> > <CFELSE> > <CFSET ELoc = 0> > </CFIF> > > <cfquery name="qryAutoApproveTickets" dbtype="query"> > SELECT * > FROM uploadWorkOrder --- Stored Proc Results > WHERE > --- All sorts of limiting goodies > AND var IN ( <cfqueryparam cfsqltype="cf_sql_integer" value="#ALoc#" > list="yes"> ) > AND var NOT IN ( <cfqueryparam cfsqltype="cf_sql_integer" > value="#ELoc#" list="yes"> ) --- CFQUERYPARAM crashes saying ELoc is null or > non-existent > </cfquery> > > Now I'm able to display both of these variables and they are obnoxiously > large lists. Am I hitting a ceiling of variable size within the QoQ? (Or > basically the size of the resulting nested SQL statement?) > > I'm just confused over why my variable just seems to disappear once I get in > the QoQ.... > > Thoughts and as always thank you very all of your expertise! > > Matthew R. Nicholson > SolTech, Inc. > Cell: 770-833-5326 > www.soltech.net > To find what you seek in the road of life, the best proverb of all is that > which says: "Leave no stone unturned." > ~Edward Bulwer Lytton > > > -----Original Message----- > From: ad...@acfug.org [mailto:ad...@acfug.org] On Behalf Of Cameron Childress > Sent: Tuesday, April 12, 2011 12:47 PM > To: discussion@acfug.org > Subject: Re: [ACFUG Discuss] Handling SQL limitation with CF > > Typically as a rule of thumb I tend to leave using IN statements and > QoQ as last resorts. There are situations it's useful to use them, > but very often (usually?) there is a better way to do it. > > -Cameron > > On Tue, Apr 12, 2011 at 11:17 AM, Matthew Nicholson > <matthew.nichol...@soltech.net> wrote: >> And that option was staring me in the face... >> >> Thank you very much for the suggestion Cameron. That might certainly do the >> trick! > > -- > Cameron Childress > Sumo Consulting Inc > http://www.sumoc.com > --- > cell: 678.637.5072 > aim: cameroncf > email: camer...@gmail.com > > > ------------------------------------------------------------- > To unsubscribe from this list, manage your profile @ > http://www.acfug.org?fa=gin.edituserform > > For more info, see http://www.acfug.org/mailinglists > Archive @ http://www.mail-archive.com/discussion%40acfug.org/ > List hosted by http://www.fusionlink.com > ------------------------------------------------------------- > > > > > > ------------------------------------------------------------- > To unsubscribe from this list, manage your profile @ > http://www.acfug.org?falogin.edituserform > > For more info, see http://www.acfug.org/mailinglists > Archive @ http://www.mail-archive.com/discussion%40acfug.org/ > List hosted by http://www.fusionlink.com > ------------------------------------------------------------- > > > > -- Cameron Childress Sumo Consulting Inc http://www.sumoc.com --- cell: 678.637.5072 aim: cameroncf email: camer...@gmail.com ------------------------------------------------------------- To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=gin.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by http://www.fusionlink.com ------------------------------------------------------------- ------------------------------------------------------------- To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by http://www.fusionlink.com -------------------------------------------------------------
