Solved

MySQL query question

Posted on 2006-06-21
12
139 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

In our day to day coding, how many times have we come across a necessity to check whether a URL is a broken link or not? For those of you that answered countless and are using ColdFusion like myself, then this article is for you.  It will show yo…
One of the typical problems I have experienced is when you have to move a web server from one hosting site to another. You normally prepare all on the new host, transfer the site, change DNS and cross your fingers hoping all will be ok on new server…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

830 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