Solved

ColdFusion Lists (in SQL queries and listFind)

Posted on 2011-09-02
16
630 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
Independent Software Vendors: 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

This is an updated version of a post made on my blog over 3 years ago. It is unfortunately, still very relevant as we continue to see both SQLi (SQL injection) and XSS (cross site scripting) attacks hitting some of the most recognizable website and …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

628 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