Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Proper Query syntax using a comma delimited list

Posted on 2008-10-13
11
Medium Priority
?
499 Views
Last Modified: 2010-04-21
I doing a cfquery against a table with a field call "gla". I want to use a listing (session.qmlist) generated from a prior input screen to compare against the table field "gla". If I get a hit from the table against the listing, I want to output the results.

I tried using Valuelist, and a  cfloop to go to the 1-to-many elements in the input listing, and I am not getting a full listing back.


<cfset session.qmlist = #form.qmlist#>
            
 <!---               <cfloop From = "1" To = "#ListLen(session.qmlist)#" index = "Counter">  --->
      
            <cfquery name="qMAgla" datasource="#REQUEST.DSN.Source#">
            select distinct upper(substring(gla,1,4)) gla
            from a_smc
            where upper(substring(gla,1,4))       IN ('#session.qmlist#')
            and date_clsd = '01/01/1900'
            </cfquery>
<!---               </cfloop>       
       --->
            <cfdump var="#qMAgla.gla#">


0
Comment
Question by:mahpog
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
  • 2
  • +1
11 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22702599
you cannot do that "directly". what database are you working with?
0
 

Author Comment

by:mahpog
ID: 22702627
I am using sybase.
0
 
LVL 27

Expert Comment

by:azadisaryev
ID: 22702664
what's the data type of your GLA field and the values in your SESSION.QMLIST? integers? varchar?
can you post sample of data contained in your session.qmlist variable?

meanwhile:
try using
where upper(substring(gla,1,4)) IN (<cfqueryparam value="#session.qmlist#" list="yes">)

instead of your
where upper(substring(gla,1,4))       IN ('#session.qmlist#')

alternatively, IN (#quotedvaluelist(session.qmlist)#) may also do the trick

0
AWS Certified Solutions Architect - Associate

This course has been developed to provide you with the requisite knowledge to not only pass the AWS CSA certification exam but also gain the hands-on experience required to become a qualified AWS Solutions architect working in a real-world environment.

 

Author Comment

by:mahpog
ID: 22702726
Using your snytax or what I have I only get back one 'GLA', when I should get a few more from the original qmlist having successful hits against the sybase table.

the first line following is the qmlist.
the second line is the one "GLA" that goes to the output from the query.

B6DP,E8T3,F177,FA2L,FE4T,FE7W,FE94,FG5H,FN24,FR2C,FR3K,FR40,FR9G,FRHT,FRWT,L34G
B6DP query - <======  query result = qMAgla.gla

Should be:  
B6DP,F177,FE94


 
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22702766
I don't know if CF somehow allows it, but the simple:
  IN ('#session.qmlist#')

will not work, as the list will be interpreted as single string value.

you would have to change every <,> in the value of gmlist by <','>, that would work. but I am no CF developer, so I don't know an easy way in CF to do so.
0
 
LVL 27

Expert Comment

by:azadisaryev
ID: 22702787
wait a minute: do you mean to say tat your GLA column contains comma-delimited list(s)???
please tell me no!!!
if your answer is yes, i have heard good things about a book "Database design for mere mortals"
0
 
LVL 39

Accepted Solution

by:
gdemaria earned 2000 total points
ID: 22703423

As pointed out above, the string you need in the    IN (here)   clause, needs to look like this...
..   in ('B6DP','E8T3','F177','FA2L','FE4T')

To do this in coldfusion, you need to change the delimiter of the list first.

<cfset newList = listChangeDelims(session.qmlist, "','")

here is that last parameter in 'slow motion'...    

      "  '  ,   '   "     (remove the spaces of course)

Now newList will contain..

   B6DP','E8T3','F177','FA2L','FE4T

But notice there are no starting or ending quotes.   So be sure to add those in the sql statement..

 where upper(substring(gla,1,4)) IN ('#newList#')

if you look at your debug output and see that the quotes have been escaped, you can add this function...

 where upper(substring(gla,1,4)) IN ('#preserveSingleQuotes(newList)#')

0
 

Author Comment

by:mahpog
ID: 22703698
gdemaria,

I did your suggestion and as you can see it creates the "IN" properly and there are 7 records that match.

but my output from qMAgla.gla only outputs the first hit and not the additonal 6 other records????

(I added the group by and order by thinking that perhaps that would cause all records to come out, but it only outputs the first hit: B6DP.  I should get the next 6 which includes F177..




Open in new window

qMAgla (Datasource=RQITest, Time=203ms, Records=7) in  D:\webserve\mahrqi\smcdev\home\smcage1.cfm @ 09:15:18.018

		select distinct upper(substring(gla,1,4)) gla
		from a_smc 
		where upper(substring(gla,1,4))	IN ('B6DP','E8T3','F177','FA2L','FE4T','FE7W','FE94','FG5H','FN24','FR2C','FR3K','FR40','FR9G','FRHT','FRWT','L34G')
		and date_clsd = '01/01/1900'
                group by upper(substring(gla,1,4))
                order by upper(substring(gla,1,4))


		

		

Open in new window

0
 
LVL 39

Expert Comment

by:gdemaria
ID: 22703731
You're not showing the code where you try to output the results.  THat would help...

But there are two basic ways, either loop use CFLOOP or CFOUPUT

<cfoutput query="QueryName">
   #queryName.gla#
</cfoutput>

Use that if you have other things to put along side each row..

Or just do this..

 <cfoutput>#valueList(queryName.gla)#</cfoutput>

use this one if you just want the comma delimited list of values

Be sure to swap your query name in where I wrote "queryName"

0
 

Author Comment

by:mahpog
ID: 22703783
gdemaria,

I have to laugh at myself! The minute I read the ValueList, I knew it was the way I did the cfoutput.

I got my data and the result that I wanted.

Your solution is perfect.

Time for more coffee.......

thanks!

0
 

Author Closing Comment

by:mahpog
ID: 31407091
I appreciated his detailed explanation and patience.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi, Even though I have created this Tutorial on My personal Blog, Some people might not able to find my website, So here i am posting it again Today, from the topic it is very clear that i will be showing you here the very basic usage of how we …
Hi. There are several upload tutorials using jquery and coldfusion. I found a very interesting one here Upload Your Files using Jquery & ColdFusion and Preview them (http://www.randhawaworld.com/) . I did keep the main js functions but made sever…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question