Link to home
Start Free TrialLog in
Avatar of dstampfel
dstampfel

asked on

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

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.
Avatar of _agx_
_agx_
Flag of United States of America image

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?

Avatar of dstampfel
dstampfel

ASKER

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?  
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>


>>> 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 .....


Yes thank you - that is exactly what I am trying to accomplish!
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!
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.

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.  

>> 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>

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.
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?
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?




Yes MS SQL 2005... Sorry I didn't paste my query sooner.
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 .....

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.  
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...

.. because if your activity query only ever returns 1 record, it's a lot simpler than I was thinking.
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.  
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.
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.  
ASKER CERTIFIED SOLUTION
Avatar of _agx_
_agx_
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
Sounds good.  BTW: It may be later in the day as I have meetings all afternoon (fun, fun;-)
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.
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.
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.
>> 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

>> 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.
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!  
You're very welcome. Until later :)