Solved

MySQL query question

Posted on 2006-06-21
12
135 Views
Last Modified: 2013-12-24
I'm using this in a query:

select
concat(COALESCE(p.FirstName, ' '),' ',COALESCE(p.MiddleName, ' '),' ',COALESCE(p.LastName, ' '), ' ',COALESCE(m.Suffix, ' ')) as name

I need to combine these elements into the variable "name" for use in a report. Not all records have a suffix. I'd like to be able to include a comma after the last name for only those records that do have a suffix. Is there a way to do this?
0
Comment
Question by:brucecrabtree
  • 5
  • 4
12 Comments
 
LVL 7

Expert Comment

by:aseusainc
ID: 16956535
So you can really only wind up with 2 different values for name:

John Bob Doe Jr. (which you want as "John Bob Doe, Jr.")

or

John Bob Doe

So lets treat name as a space delimited list...This is real quick and dirty but should work provided you ALWAYS have a 1st, middle, and last name.  Which I suppose if you didn't, you could end up with a double space between first and last name.

<cfif listlen(name," ") EQ 4>
  <cfset name = "#listgetat(name,1," ")# #listgetat(name,2," ")# #listgetat(name,3," ")#, #listgetat(name,4," ")#">
</cfif>
0
 
LVL 7

Expert Comment

by:aseusainc
ID: 16956549
Actually if you dont have a suffix, you'll have an extra space at the end of the name, so lets trim that as well.

<cfif listlen(name," ") EQ 4>
  <cfset name = "#listgetat(name,1," ")# #listgetat(name,2," ")# #listgetat(name,3," ")#, #listgetat(name,4," ")#">
<cfelse>
  <cfset name = trim(name)>
</cfif>
0
 

Author Comment

by:brucecrabtree
ID: 16956610
Most records don't have a middle name. Thanks for the suggestion but I don't think it would work for my situation (I'm using Report Builder). I'd have to loop over the query to combine these elements and then somehow append it to the rest of the query results for each record, instead of concatenating them within the query itself. (This was just the first line of the query -- there's a lot more to it.) That's probably more complicated than it's worth, if it's even possible, I'm not sure.
0
 

Author Comment

by:brucecrabtree
ID: 16956652
Maybe I misunderstood. . . .Is there a way to loop over a query and update it in memory?
0
 
LVL 7

Expert Comment

by:aseusainc
ID: 16956678
I'm not sure I understand what you are asking.  Either of these will loop over the result set of a query:

<cfloop query="you_query_name">

or

<cfoutput query="your_query_name">
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:brucecrabtree
ID: 16956701
Right, but I need to pass a query to Report Builder in the cfreport tag

<cfreport template="../cfreports/member_directory.cfr" format="pdf" query="getMembers" />

So if my query "getMembers" returns "name" plus 10 other variables (Address, City, State, Zip, etc.), and I loop over it using your code to add a comma to "name" where it's needed, then how do I get the updated "name" variable back into a query result that I can pass in the cfreport tag?
0
 
LVL 7

Expert Comment

by:aseusainc
ID: 16956738
Pass it in with <cfreportparam> call it newname so it doesn't conflict w/ any queries

<cfif listlen(name," ") EQ 4>
  <cfset newname = "#listgetat(name,1," ")# #listgetat(name,2," ")# #listgetat(name,3," ")#, #listgetat(name,4," ")#">
<cfelse>
  <cfset newname = trim(name)>
</cfif>

<cfreport template="../cfreports/member_directory.cfr" format="pdf" query="getMembers" />
  <cfreportparam NAME="newname" VALUE="#newname#">
</cfreport>
0
 

Author Comment

by:brucecrabtree
ID: 16964178
Thanks. I ran your code and it works, as you said, unless there is no middle name. In those cases it does not add the comma (it doesn't give me a double space in the middle name position). So I guess I need to use a different approach besides treating "name" as a list.
0
 

Author Comment

by:brucecrabtree
ID: 16964420
This code gets me the results I need:

<cfloop query="getMembers">
<cfif getMembers.MiddleName NEQ "" and getMembers.Suffix NEQ "">
<cfset name = getMembers.FirstName & " " & getMembers.MiddleName & " " & getMembers.LastName & "," & " " & getMembers.Suffix>
<cfelseif getMembers.MiddleName NEQ "" and getMembers.Suffix EQ "">
<cfset name = getMembers.FirstName & " " & getMembers.MiddleName & " " & getMembers.LastName>
<cfelseif getMembers.MiddleName EQ "" and getMembers.Suffix NEQ "">
<cfset name = getMembers.FirstName & " " & getMembers.LastName & "," & " " & getMembers.Suffix>
<cfelseif getMembers.MiddleName EQ "" and getMembers.Suffix EQ "">
<cfset name = getMembers.FirstName & " " & getMembers.LastName>
</cfif>
</cfloop>

But this code doesn't work to pass the value to the report:

<cfreport template="../cfreports/member_directory.cfr" format="pdf" query="getMembers">
<cfreportparam NAME="name" VALUE="#name#">
</cfreport>

The query returns over 500 records, and so I need to update the variable "name" for each record in the query before passing it to the report. It doesn't appear that cfreportparam does that.
0
 
LVL 1

Accepted Solution

by:
DarthMod earned 0 total points
ID: 17435860
PAQed with points refunded (500)

DarthMod
Community Support Moderator
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…
Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

705 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