Solved

ColdFusion Lists (in SQL queries and listFind)

Posted on 2011-09-02
16
596 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
  • 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
 
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Sometimes databases have MILLIONS of records and we need a way to quickly query that table to return the results me need. Sure you could use CFQUERY but it takes too long when there are millions of records. That is why SOLR was invented. Please …
Recently while working on a project I got a very annoying cfdocument has no body error message. I had never seen this error before. So I checked the code. The code was pretty simple; it was Just showing me the cfdocumnt tag and inside that tag a …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video discusses moving either the default database or any database to a new volume.

708 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now