Solved

MySQL query question

Posted on 2006-06-21
12
140 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
[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
  • 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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
 

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

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

Question has a verified solution.

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

Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
What You Need to Know when Searching for a Webhost Provider
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

739 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