Solved

MySQL query question

Posted on 2006-06-21
12
141 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
The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

 

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

Free NetCrunch network monitor licenses!

Only on Experts-Exchange: Sign-up for a free-trial and we'll send you your permanent license!

Here is what you get: 30 Nodes | Unlimited Sensors | No Time Restrictions | Absolutely FREE!

Act now. This offer ends July 14, 2017.

Question has a verified solution.

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

This is a guide to setting up a new WHM/cPanel Server to be used for web hosting accounts. It is intended for web hosting company administrators and dedicated server owners. For under $99 per month (considering normal rate of Big Data Cetnters like …
A web service (http://en.wikipedia.org/wiki/Web_service) is a software related technology that facilitates machine-to-machine interaction over a network. This article helps beginners in creating and consuming a web service using the ColdFusion Ma…
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

688 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