Solved

ColdFusion Lists (in SQL queries and listFind)

Posted on 2011-09-02
16
621 Views
Last Modified: 2012-05-12
I have a list of items (variable name: unitList) I get from this:

<cfquery name="qry_getOrgs" datasource="#request.DSN#">
		SELECT distinct(tblPerson.personUnit) FROM tblActivityAuthors
		LEFT JOIN 
		tblPerson ON tblPerson.personID = tblActivityAuthors.personID 
		WHERE activityID = #activityID#
        ORDER BY personUnit 
		</cfquery>
        <cfparam name="unitList" default="">
        <!---<cfset unitList = listAppend(unitList, qry_getOrgs.personUnit & "<br>")>--->
      	<cfset unitList = listAppend(unitList, qry_getOrgs.personUnit, chr(10))>
		<cfset unitList = replace(unitList, chr(10), "<br>", "all")>

Open in new window


Then I query a group of people who need to be notified if their unit is within this list:

 
<cfquery name="qry_getQAEmails" datasource="#request.DSN#">
    	SELECT empl_id, roleOrgs, tblPerson_RTIDetail.personEmail as personEmail 
        FROM authdb_users 
        LEFT JOIN tblPerson_RTIDetail ON tblPerson_RTIDetail.personRTIID = authdb_users.empl_id 
        	WHERE userRoleID = '5'
  	</cfquery>

Open in new window


So somewhere on this line:

WHERE userRoleID = '5'

I need to say and roleOrgs is in the list unitList.

Hope this makes sense!
0
Comment
Question by:traport
[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
  • 8
  • 8
16 Comments
 
LVL 52

Expert Comment

by:_agx_
ID: 36473862
I don't think you can use the existing list because you'll have delimiter problems. But if you've got a CSV list of unit numbers like this:

<cfset unitList = "1,2,5,9,91,10,22">

Try

WHERE userRoleID IN
(

      <cfqueryparam value="#unitList#" cfsqltype="cf_sql_integer" list="True">
)
0
 

Author Comment

by:traport
ID: 36473983
Okay, thanks!

It is the way I've set up the list that is giving me problems. How would I set up the list from the query so I could use the IN statement bearing in mind that these units might have hyphens in them as well...

<cfquery name="qry_getOrgs" datasource="#request.DSN#">
            SELECT distinct(tblPerson.personUnit) FROM tblActivityAuthors
            LEFT JOIN
            tblPerson ON tblPerson.personID = tblActivityAuthors.personID
            WHERE activityID = #activityID#
        ORDER BY personUnit
</cfquery>

So it might be something like, IN(SSES-SCS-SRD,SSES-SCS-SRD,SSES-SCS-SRD)
0
 
LVL 52

Expert Comment

by:_agx_
ID: 36474057
Hyphen's don't matter.
>> So it might be something like, IN(SSES-SCS-SRD,SSES-SCS-SRD,SSES-SCS-SRD)

If you mean the query results are:
         PersonUnit
         SSES-SCS-SRD
         SSES-SCS-SRD
         SSES-SCS-SRD

Use valueList to convert them into a single csv list

       <cfset unitList = valueList( yourQUeryName.PersonUnit)>

like this;

         SSES-SCS-SRD,SSES-SCS-SRD,SSES-SCS-SRD

Then use it in your query. You may need to change the data type to cf_sql_varchar

WHERE userRoleID IN
(

      <cfqueryparam value="#unitList#" cfsqltype="cf_sql_varchar" list="True">
)


0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 52

Expert Comment

by:_agx_
ID: 36474130
If you meant each record contains a list of values:

ie
         PersonUnit
         SSES-SCS-SRD, SSES-SCS-SRD
         SSES-SCS-SRD, SSES-SCS-SRD, SSES-SCS-SRD
         SSES-SCS-SRD
         SSES-SCS-SRD, SSES-SCS-SRD

ValueList will still work. But from a db perspective storing lists isn't a great idea.
0
 

Author Comment

by:traport
ID: 36474208
Sorry to keep asking more questions - what if I want to know if it is just LIKE or is contained within one of the items in the list?

WHERE roleOrgs LIKE <cfqueryparam value="#unitList#" cfsqltype="cf_sql_varchar" list="True">
or
LIKE <cfqueryparam value="%#unitList#%" cfsqltype="cf_sql_varchar" list="True">


doesn't work.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 36474234
No worries.  So your table IS storing lists?

I'd need to see full example of the values you're comparing.  For example, if

       unitList    =  xxx-yyyy, aaa-bbb

What's an example of the roleOrgs record value you're trying match?
0
 

Author Comment

by:traport
ID: 36474318
I think it's the hyphens that are screwing this up... I could have like SSES and it works fine. But SSES- and it breaks.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 36474326
Actually just show the raw table values, like this

Row, tblPerson.personUnit
#1,   aaaa
#2,   bbb-xx,yyy,ccc                  <== multiple values in a single column


Row, roleOrgs
#1,    ???
#2,    ???
0
 

Author Comment

by:traport
ID: 36474356
Okay...

A person's roleOrg might  = BHCJ,0200206312,0200232312,0200233312,0200234312,0200235312,0200236312,0200332312,0200237312,0200253312,0200253350,SPHERE,0200193312,0200195312,DHSSPR,0200141350,0200217312,0200219312,0200220312,0200222312,0200224312,0200224350,0200226312,0200312312,0200426312,PHE,0200227312,0200228312,0200229312,0200231312,0200200312,0200218312,PHB,0200230312,0200230350

and the personUnits I might be searching could be = SSES-SCS-SRD
0
 

Author Comment

by:traport
ID: 36474365
Or a roleOrg that would actually contain a match:

SCS,0200065312,CAHIT,0200408312,DRS,0200303312,0200093312,0200357312,0200367312,0200405312,0200406312,ESD,0200064312,RCD,0200092312,SRD,0200080312,0200341312,0200343312,0200344312,0200345312,0200346312,0200347312,0200348312,0200355312,0200356312

with personUnit = SSES-SCS-SRD
0
 
LVL 52

Expert Comment

by:_agx_
ID: 36474383
Oh boy.. You mean both columns in both tables can contain multiple values?

Which db type are you using?
0
 

Author Comment

by:traport
ID: 36474393
Yes. BOTH. SQL 2005.
0
 
LVL 52

Assisted Solution

by:_agx_
_agx_ earned 500 total points
ID: 36474631
Honestly using a list to search a list .. in SQL... is a lot harder.  The difficulty you're having now is one of the prime reasons db experts recommend against storing lists like this.  

Anyway, how many records are you talking about? If it's only a few you might just bite the bullet and pull all records and use CF list functions.  Not a great solution. But it's going to be tough to find any elegant solutions that work with denormalized tables.  How comfortable are you with stored procedures or UDF's?
0
 

Accepted Solution

by:
traport earned 0 total points
ID: 36474688
aqx I agree with you. I think I'm back to the drawing board with how I store this information. I need a 1 to 1 match here. I'm going to close this out and reward you the points for sticking with me. THANK YOU.
0
 

Author Closing Comment

by:traport
ID: 36494075
Thanks.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 36475110
If you need any help w/the table design, feel free to open another question.  If you flag it under the CF zone, as well as MS SQL 2005, we'd be happy to help.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone 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

CFGRID Custom Functionality Series -  Part 1 Hi Guys, I was once asked how it is possible to to add a hyperlink in the cfgrid and open the window to show the data. Now this is quite simple, I have to use the EXT JS library for this and I achiev…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…
Suggested Courses

751 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