Hey all..this is the results of my swearing, coffee, slamming fist on table
and brutally turning the pages of CF WACK :)
This program is supposed to search through a table filled with badly
formatted data. The data cannot be completely cleaned up, but we were tasked
to do so to the best of our ability.
We had to search through the records, and find where a second name began,
and then push that over to a table called JUDGE 2 while also keeping a
record of the original record in Audit table.
We then had to write the new name, which now should contain a single name,
back into the Judge table.
A second program runs through these changed records and switches Last Name
to Firstname.(will be posted at later date).
I am sure this was not the most efficient way to do this, and I am sure the
program can be improved..but for what its worth this is what I did..and it
worked...sorta ;-)
I decided to post it back to the list in case it would be of use to
anyone..maybe provide a starting point to modify their own code.
----------------------------------------------------------------------------
---------------------
<CFSET AlreadyFound = False>
<CFSET IsACompany = False>
<CFSET CompanyList = "Limited|LTD|LTD.|Co.|Company|the|LTD,|BANK"> <!---
This is the list of words to search for--->
<cfloop query="judgement"> <!--- The main loop corresponding to the
appropriate query. This will loop over all the records returned by the
query--->
<cfset tempDefendant = #judgement.defendant#> <!--- This was necessary
because Coldfusion handles nested lists in strange ways.--->
<br>
<cfloop index="x" from="1" to="8"> <!--- AN index loop was used instead of
a collection loop, because the collection loop refused to cycle through the
Company List for some reason when I attempted to do this. Ideally a
Collection List loop should be used, pointing to the CompanyList
variable---->
<CFSET WordToGet=ListGetAt(#CompanyList#,#x#,'|')> <!--- Because an
iterative loop was used, we must now get the Word which we are searching for
from the CompanyList with the ListGet At Statement --->
<CFIF FindNoCase(#WordToGet#,#tempdefendant#) neq 0
AND NOT
AlreadyFound> <!--- If the word is in the string Defendant, i.e. the
Defendant is determined to be a company, and the string has not already
been searched and Found to be a Company..then do this--->
<CFSET IsACompany = True> <!--- Flag
determining it is a company--->
<CFSET AlreadyFound = True><!--- Flag
declaring that it has been
determined that it is a company and further searchign is unncessary--->
</CFIF>
</CFLOOP>
<CFIF IsACompany eq FALSE> <!--- IF it is NOT a company, and
thus a
person's name..perform the operations--->
<!--- Search for a comma in the string ---->
<cfset pos= FindNoCase(",",#judgement.defendant#)>
<!---- If a comma is found, then pull the string up to
the comma and
this will be the first name --->
<CFIF pos gt 0>
<CFSET
Name1=Left(#defendant#,(#pos#-1))>
<CFOUTPUT>
<CFSET
name2=mid(#defendant#,(#pos#+1),Len(defendant))>
</CFOUTPUT>
<!--- Ensure that ALL the values are inserted at
once--->
<CFTRANSACTION>
<!--- THis is standard SQL for inserting a record into
a table, and
updating a record. We want to UPDATE the Judge table with the new name..and
write the remaining names into a new record in the Judge 2 table. There is a
One To many relationship between Judge 1 and Judge 2.---->
<cfquery name="InsertAudit"
datasource="republic" dbtype="ODBC">
Insert INTO Audit
(defendant,plaintiff,amount,sequence,remarks,reg_date,cost,indicator,counter
,new_number,account)
Values(<CFIF #name2# eq ''>'0'<CFELSE>'#judgement.defendant#'</CFIF>,
'#judgement.plaintiff#', #judgement.amount#,
#judgement.sequence#,'#judgement.remarks#',
#CreateODBCDateTime(judgement.reg_date)#,
'#judgement.cost#','#judgement.indicator#',<CFIF judgement.counter is
''>0<CFELSE>#judgement.counter#</cfif>,<CFIF judgement.new_number is
''>0<CFELSE>#judgement.new_number#</CFIF>,'#judgement.account#')
</cfquery>
<cfquery name="InsertJudge2" datasource="republic" dbtype="ODBC">
Insert INTO Judge2
(defendant,plaintiff,sequence,indicator,counter,new_number)
Values(<CFIF #name2# eq ''>'0'<CFELSE>'#name2#'</CFIF>,
'#judgement.plaintiff#',#judgement.sequence#,'#judgement.indicator#',<CFIF
judgement.counter is ''>0<CFELSE>#judgement.counter#</cfif>,<CFIF
judgement.new_number is ''>0<CFELSE>#judgement.new_number#</CFIF>)
</cfquery>
<!--- Update the Name field to correspond to just the first name we
found ---->
<cfquery name="UpdateJudge" datasource="republic" dbtype="ODBC">
Update Judge
Set defendant = '#name1#'
Where Sequence=#judgement.sequence#
</cfquery>
</CFTRANSACTION>
</CFIF> <!---IF Pos Is Greater Than 0 ---->
</cfif><!--- IF IS A Company Is False ---->
<!--- Now reinitialise the variables to false as we are going onto the next
record in the Query that has not yet been checked --->
<CFSET AlreadyFound = False>
<CFSET IsACompany = False>
</CFLOOP> <!---- Loop and go to the next record in the Query
recordset---->
----------------------------------------------------------------------------
-------------------
WElp..thats it..
simple..convoluted..probably kinda ugly.
Now you experts that are watching this and laughing will get no muffins!
*sits and munches on blueberry muffins*
*dips in extra strong coffee*
-Gel
www.carigamer.com
Island Gaming At Its Best (tm)
Now sporting the Mother of all message Forums--FUSETALK! :)
(all hail gullible Investors! yaaay!)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
