Solved

How can I tell if my CFIF CONTAINS statement returns 0 results?

Posted on 2010-09-09
30
346 Views
Last Modified: 2012-05-10
I have the following CFIF statement:

<CFIF query.notes CONTAINS "meeting">
   #query.notes#
<cfelse>
</cfif>

If any results are returned I want to include a title before the first record and not for the rest.  OR if no results are returned I want to exclude the header. I have no idea how to accomplish this.  I am a very new to ColdFusion so please forgive me if I am missing something obvious.
0
Comment
Question by:dstampfel
  • 17
  • 13
30 Comments
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
As you know, a query can return multiple records.  So when you say

     <CFIF query.notes CONTAINS "meeting">

are you trying to determine if the ANY of the records contain the word "meeting" or just the 1st record?

0
 

Author Comment

by:dstampfel
Comment Utility
I want to determine if ANY of the records contain the word "meeting".  Let me give you a little more color on this.  I have a main query called targets.  This returns 200 records that contain company information.  Within the <output> tag for the targets query I run an activity query on the targets query results.  I want to use the <CFIF> statement to only return the target records that have the keyword "meeting" in the activity notes.  When they are displayed on the page, if I use CurrentRow to see the row number, it varies. Like the first instance of the word meeting could be row 4 of the targets query.  The next might be row 19 etc.  So my simplified code looks like this:

<cfoutput query="targets">

<cfquery name="activity" datasource="databasename">
SELECT act_date, activity_nm, notes FROM activity
WHERE activity.target = #target#
</cfquery>

<CFIF activity.notes CONTAINS "meeting">
#target# #activity.act_date# #activity.activity_nm# #activity.notes#
<cfelse>
</cfif>

</cfoutput>

I may have screwed up the code above I am typing it from memory but the report works fine and displays  the proper information, but I want to run this CFIF CONTAINs statement on a few key words in order to group results in a few categories by keyword, and insert text before the first displayed record for the group header like "Meetings Scheduled" for the ones with the keyword "meeting". Did I just confuse  things further?  
0
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
Well if you only want notes containing "meeting", then just do the filter in the "activity" query itself.  Then the activity query will contain only the stuff you want.

<cfquery name="activity" datasource="databasename">
SELECT act_date, activity_nm, notes FROM activity
WHERE activity.target = #target#
AND      notes LIKE '%meeting%'
</cfquery>


0
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
>>> but I want to run this CFIF CONTAINs statement on a few key words in order to
>> group results in a few categories by keyword,

Oh wait...  I'm not sure if have your requirements straight:

1) How many keywords are you talking about?
2) Are you always displaying results for a single keyword or for MULTIPLE keywords.  An example of multiple keywords might be:

Notes:
1) Blah, blah... meeting ABC
2) Blah, blah... financial report .....
3) Blah, blah... group meeting EFG
4) Blah, blah... financial department .....

Desired results:

Title: Meetings Scheduled       (keyword "meeting")
-- Blah, blah... meeting ABC
-- Blah, blah... group meeting EFG

Title: Finance                           (keyword "financial")
---Blah, blah... financial report .....
---Blah, blah... financial department .....


0
 

Author Comment

by:dstampfel
Comment Utility
Yes thank you - that is exactly what I am trying to accomplish!
0
 

Author Comment

by:dstampfel
Comment Utility
The challenge here (for me) is twofold, 1) if there are results for the particular keyword, getting the title to appear and 2) if there are no results for that keyword have the title not appear..... thanks!
0
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
It's certainly possible. But the tricky part is really ordering the results in such a way that you know when each "group" header starts.  There's different approaches. Some more efficient than others. So first a few questions

1) How many keywords are you dealing with
2) On average, how many activity records exist for each company
3) What database are you using MS SQL, MySQL, etc...


BTW: Querying within a loop is generally frowned upon. But will get to that later.

0
 

Author Comment

by:dstampfel
Comment Utility
Yes, I am sure I am violating some programming etiquitte - I am not a trained programmer, self-taught by necessity, so I am certain you will find my code offensive. But it works! Ha ha. Anyway, to answer your questions:

1) We are only dealing with three keywords, Meeting, Review and Not Interested. And also I need to return the results if none of the keywords are found, that group would be called "Other Notes".

2) I only want to return the most recent activity for each company which I accounted for in the activity query.

3) Using MS SQL.  

0
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
>> 3) Using MS SQL.  

That's good news!

Since you only have 4 conditions, you could use a simple CASE statement inside the activity query.  Keep in mind there's better ways (like a lookup table, etc...). But I'll keep it simple for now.  Basically you use a CASE to assign a "group" name, based on what keyword is found in the notes.

<cfquery ....>
SELECT  
          CASE
               WHEN notes LIKE '%meeting%' THEN 'Meeting'
               WHEN notes LIKE '%yourkeyword2%' THEN 'Review'
               WHEN notes LIKE '%yourkeyword3%' THEN 'Not Interested'
               ELSE 'Other Notes'  -- ie None of the other keywords were found                        
               END AS GroupName
           , act_date, activity_nm, notes
FROM     activity
WHERE  activity.target = #target#
<!--- it's important to sort by the group 1st --->
ORDER BY GroupName
</cfquery>

When you display the results, use the "group" attribute of cfoutput.  It's a neat trick you can use in cases like this.  But you may need to change your outer loop to a <cfloop> instead of <cfoutput> for this to work


<cfloop query="target">
     <cfoutput>... display stuff from target query </cfoutput>

      ... run activity query ....
      <cfoutput query="activity" group="GroupName">
            <!--- because you're using "group" the header will only be displayed once --->
            #GroupName#
            <!--- then display all of the notes --->
            <!--- IMPORTANT: the nested <cfoutput> is deliberate! --->
            <cfoutput>
            #target# #activity.act_date# #activity.activity_nm# #activity.notes#
            </cfoutput>
       </cfoutput>

</cfloop>

0
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
One quick note about cfoutput "group".  Your query MUST be ordered the exact same way as you're grouping the cfoutput.  Otherwise it won't work properly.
0
 

Author Comment

by:dstampfel
Comment Utility
Having trouble... I am afraid I have no choice but to paste my actual activity query here, its a bit complicated for me.  I am getting errors when trying to insert the CASE statement.  

Here is my actual query:

<cfquery datasource="database" name="act">
            SELECT TOP 1
                          iaa.ACTIVITY_ID [actid], iat.ACTIVITY_TYP_NM [type], iaa.ACTIVITY_DT   [actDT], iaa.SUMMARY, iaa.NOTES
             FROM dbo.INT_AUX_LST_ACTIVITY iala INNER JOIN
dbo.INT_AUX_ACTIVITY iaa ON iaa.ACTIVITY_ID = iala.ACTIVITY_ID INNER JOIN
dbo.INT_ACTIVITY_TYP iat ON iat.ACTIVITY_TYP_ID = iaa.ACTIVITY_TYP_ID
            WHERE iaa.DIRECTORY_ID =#dir_id# AND iala.LISTING_ID =#listing_id#
            ORDER BY  iaa.ACTIVITY_DT DESC        
        </cfquery>

When I try to insert the CASE, it tells me there is an error.... I am guessing TOP 1 is frowned upon too?
0
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
No, there's nothing wrong with using TOP 1. But .. it's not going to work with my code. I was assuming your SELECT was returning multiple records.  So a CASE would be almost pointless there ;-)

Let me see what I can come up with.  You're using MS SQL 2005 right?




0
 

Author Comment

by:dstampfel
Comment Utility
Yes MS SQL 2005... Sorry I didn't paste my query sooner.
0
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
One more thing. I assume you're trying to limit the results to 1 record per ... (what?).  Listing, "Group" name, ...

I'm just trying to understand how the new query relates to the original example, if the query's only returning 1 record ...

Original example:

Title: Meetings Scheduled       (keyword "meeting")
-- Blah, blah... meeting ABC
-- Blah, blah... group meeting EFG

Title: Finance                           (keyword "financial")
---Blah, blah... financial report .....
---Blah, blah... financial department .....

0
 

Author Comment

by:dstampfel
Comment Utility
Since the query is in a loop, the activity query is looping through the original target query result companies and searching for the most recent activity for that company.  Then I say to look for the keyword in the CFIF CONTAINS statement. So, in my report it does work. Its not the most elegant code, but it works.  Right now I have the titles hard coded outside the output tags so that is where I come into the problem if there are no results the title of the group is there with nothing under it and it looks silly.  
0
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

 
LVL 52

Expert Comment

by:_agx_
Comment Utility
Right. But I guess what I'm asking is .. do you only want to display 1 activity record per company? No matter what "group" it falls into?

ie
Company A
        Title: Meetings Scheduled       (keyword "meeting")
        -- Blah, blah... meeting ABC
Company B
       (display nothing. no activity records)

Company C
     Title: Finance                           (keyword "financial")
     ---Blah, blah... financial report .....

... etc...

0
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
.. because if your activity query only ever returns 1 record, it's a lot simpler than I was thinking.
0
 

Author Comment

by:dstampfel
Comment Utility
Well what I am going for is more like:

Title: Meetings Scheduled       (keyword "meeting")
        Company A    Blah blah blah notes
        Company B    Blah blah blah notes

Title: Under Review      (keyword "review")
        Company C    Blah blah blah notes
        Company D    Blah blah blah notes

Title: Not Interested       (keyword "no interest")
       (display nothing. no activity records)

Title: Other Notes
        Company E    Blah blah blah notes
        Company F   Blah blah blah notes

There will be only one note per company but I need to show companies and notes with the same keywords together in a group.  
0
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
Ooh!  Well, I'm not sure how you're getting those results with your current code. Unless you're doing some sorting of the companies I can't see.  But let's say for a minute it's working, except for header "with nothing underneath it" problem.    You can use the query.recordCount property to determine if a query returned any records.  The recordCount will be "0" if no records were returned.

<!--- if the query returned 1 or more records ...--->
<cfif activity.recordCount>
        display stuff here
</cfif>

I would probably do it with a single query. Do a JOIN between companies and the activity tables. Then sort the results by the "group" first, company name second.
0
 

Author Comment

by:dstampfel
Comment Utility
Unfortunately my company query already has six joins in it and I can't stomach trying to join the activites in there too.... IThe database I am working with is a beast.  There are other factors involved in why I have to query the company names first. I guess my goal was to use one query for the activites then try group them by keyword using CFIF CONTAINS.  Right now to do what you are suggesting won't work for me because I am still inside that company loop.  If there are no records that works fine, but if there are records it will show up above every row. I guess I will have to rethink this whole report and try to figure out another way.  Thank you so much for spending so much time on this. I am happy to award you all of the points for your effort here.  I apologize, I feel like I wasted your time.  
0
 
LVL 52

Accepted Solution

by:
_agx_ earned 250 total points
Comment Utility
No, you haven't wasted my time at all. If I didn't enjoy puzzles and beastly databases I wouldn't be here ;-) Besides... we've all had to deal with beastly setups at one time or another.  

>> but if there are records it will show up above every row

If the data's already sorted, a simple, but not very elegant solution would be to keep track of headers you've already displayed.  For example, before the outer loop create an empty structure

     <cfset headersAlreadyDisplayed = structNew()>

Before displaying a header, check the structure. Then only display the header if you haven't done so already.

       <cfif activity.recordCount>
               .... figure out what the header text should be ....
               <cfset someHeader = " ... something ...">
               <!--- if you haven't displayed it already ... --->
               <cfif NOT structKeyExists(headersAlreadyDisplayed, someHeader )>
                           show the header #someHeader#
               <!--- then add it to the structure so we don't show it again --->
               <cfset headersAlreadyDisplayed[someHeader ] = true>
       </cfif>




If you don't mind waiting, let me give this some more thought.  Just to see if I can come up with something more elegant.  It's been a long day, and my brain is a bit toasty.  So I'm sure a fresher mind tomorrow will do better.

0
 

Author Comment

by:dstampfel
Comment Utility
Thank you for not giving up on me :)

I am happy to wait, there is no deadline looming here. I appreciate all of your effort and speedy replies.

I should probably stop working too. I will try your suggestion and wait to hear from you tomorrow.
0
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
Sounds good.  BTW: It may be later in the day as I have meetings all afternoon (fun, fun;-)
0
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
Thinking about it a bit more, the structure idea is probably the simplest workaround. Obviously doing within SQL is probably the best way. But since that's not possible, the structure hack _should_ do the trick.
0
 

Author Comment

by:dstampfel
Comment Utility
OK, when I tried that I got an error. I guess I will just have to mess around with it some more. Thanks for all of your help. I really appreciate it.
0
 

Author Comment

by:dstampfel
Comment Utility
I wanted to let you know I successfully joined the company and activity tables (I can't believe it) and now I have one additional question for you regarding my query.  If  I open a new question can you grab it since you are familiar with my tables? Or can we just continue this thread? It has to do with selecting the first activity for each company that comes up in the query.  If I use TOP 1, it only takes the top 1 from the whole query which returns only one company and one note.  I want the first result from each company so do I use MAX(act_dt) or something? Let me know if you want me to open a new question. Thanks.
0
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
>> so do I use MAX(act_dt) or something

You could .. or use something like rank().  Either way, you'd need to use a derived table.  Since I don't know the structure of your company table, here's a stand-alone example that shows what I mean.  





--- simulate "Company" table

declare @company table

(

companyID int identity,

name varchar(50)

)

--- sample data

insert into @company (name)

select 'CompanyABC' union

select 'CompanyEFG' union

select 'CompanyHIJ' 



--- simulate "Activity" table

declare @activity table

(

activityID int identity,

companyID int,

notes varchar(200),

activityDate datetime

)



--- sample data

insert into @activity (companyID, Notes, activityDate) 

select 1, 'Blah, blah... meeting ABC', '2010-09-01' union

select 1, 'Blah, blah... financial report', '2010-09-03' union

select 3, 'Blah, blah... financial department', '2010-09-02' union

select 3, 'Blah, blah... group meeting EFG', '2010-09-05' 



--- Simulate final query

SELECT	

    c.CompanyID, 

    c.Name AS CompanyName,

    a.ActivityID, 

    a.ActivityDate, 

    a.Notes 

FROM	@company c	LEFT JOIN 

    	(

    	--- Rank activity notes "By Company"

    	SELECT	CompanyID, ActivityID, ActivityDate, Notes,

        	RANK() OVER (PARTITION BY CompanyID ORDER BY activityDate DESC) AS NoteRank

    	FROM    @activity 

    	)

        --- Join the results by company AND only retrieve the TOP 1 activity note

    	a ON c.CompanyID = a.CompanyID AND a.NoteRank = 1

Open in new window

0
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
>> I wanted to let you know I successfully joined the company and activity tables (I can't believe it)

BTW: The stand-alone example is just to demonstrate "how" it would work.  If isn't clear what you should do from here, feel free to post your new query. I'll see if I can give you an example of how to modify it.
0
 

Author Comment

by:dstampfel
Comment Utility
Thanks for following up.  I changed my report a bit and came up with a work around that is sufficient. You would probably cringe if you saw it, but it’s producing the result I want.  I can't thank you enough for your persistence and effort here. Until next time!  
0
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
You're very welcome. Until later :)
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

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 …
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…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

762 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

9 Experts available now in Live!

Get 1:1 Help Now