ok
tried brians
Error Diagnostic Information
ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC SQL Server Driver][SQL Server]'instr' is not a recognized
function name.
with
<cfset newOrderList = ",Partner,Principal,Senior Associate,Associate,">
<cfquery name="Employees" datasource="RPH"
cachedwithin="#CreateTimeSpan(0,0,30,0)#">
SELECT LEFT(RTrim(tblEmployee.Lastname),10)+', '+tblEmployee.Firstname as
Fullname, tblEmployee.EMPLOYEE_ID, tblEmployee.EmployeeID,
tblEmployee.StudioID, tblEmployee.StatusID, tblEmployee.TitleID,
tblStudio.StudioID, tblStudio.OfficeID, tblStudio.StudioName,
tblOffice.OfficeID, tblOffice.City, tblPhone.PhoneNumb,
tblPhone.PhoneTypeID, tblFloor.floor, tblJobTitle.TitleID, tblJobTitle.Title
FROM tblEmployee, tblStudio, tblOffice, tblPhone, tblFloor, tblJobTitle
WHERE tblEmployee.studioid = tblStudio.studioid
AND tblEmployee.TitleID = tblJobTitle.TitleID
AND tblOffice.OfficeID = tblStudio.OfficeID
AND tblJobTitle.TitleID IN (25,26,28,4)
AND tblPhone.EmpResID = tblEmployee.EmployeeID
AND tblPhone.PhoneTypeID = '1'
<!--- AND tblEmployee.StudioID in (#FORM.StudioID#) --->
AND tblEmployee.FloorID = tblFloor.floorid
ORDER BY instr('#newOrderList#',',' | Title | ',')
, Fullname;
</cfquery>
-paul
-----Original Message-----
From: Bryan Love [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, May 28, 2002 9:55 PM
To: CF-Talk
Subject: RE: ORDER BY question
Ok, I'm an idiot... I just thought of a MUCH faster way to do this....
<!--- create a list with commas at both ends --->
<cfset newOrderList = ",#orderList#,">
<!--- this query is in ORACLE syntax, but can be done for any DB,
ORDER BY orders by how far in it found the index in your order by
string. --->
<cfquery name="" ...>
SELECT *
FROM myTable
WHERE ID IN (#orderList#) [or whatever here]
ORDER BY instr('#newOrderList#',',' | idColumn | ',')
</cfquery>
+-----------------------------------------------+
Bryan Love
Macromedia Certified Professional
Internet Application Developer
Database Analyst
Telecommunication Systems
[EMAIL PROTECTED]
+-----------------------------------------------+
"...'If there must be trouble, let it be in my day, that my child may have
peace'..."
- Thomas Paine, The American Crisis
-----Original Message-----
From: Bryan Love [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, May 28, 2002 6:45 PM
To: CF-Talk
Subject: RE: ORDER BY question
This will do it, but keep a close eye on the processing time of the server,
and make sure if the list gets long that you are using a properly indexed
database.
customTagName.cfm
------------------------------------------------
<cfparam name="attributes.sortOrder" default="">
<cfset sqlBase = "SELECT * FROM myTable">
<cfif listLen(attributes.sortOrder) GT 1>
<cfset sqlString = sqlBase & " WHERE myColumn =
#replace(attributes.sortOrder,","," UNION ALL #sqlBase# WHERE myColumn =
","all")#">
<cfelseif attributes.sortOrder NEQ "">
<cfset sqlString = sqlBase & " WHERE myColumn =
#attributes.sortOrder#">
<cfelse>
<cfset sqlString = sqlBase>
</cfif>
<cfquery name="" ...>
#preserveSingleQuotes(sqlString)#
</cfquery>
------------------------------------------------
notice that in the above you are simply creating a SELECT statement for each
item in the specified SORTORDER list and UNIONing them all together.
I chose UNION ALL because it is faster than UNION... UNION filters out
duplicate rows...
+-----------------------------------------------+
Bryan Love
Macromedia Certified Professional
Internet Application Developer
Database Analyst
Telecommunication Systems
[EMAIL PROTECTED]
+-----------------------------------------------+
"...'If there must be trouble, let it be in my day, that my child may have
peace'..."
- Thomas Paine, The American Crisis
-----Original Message-----
From: Paul Ihrig [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, May 28, 2002 11:59 AM
To: CF-Talk
Subject: ORDER BY question
ok. ASC & DESC don't work on this one.
i want to be able to ORDER BY in a specific order
such as
ORDER BY tblJobTitle.TitleID='25,26,28,4'
is that possible?
if not how else could i do it...
<!--- tblJobTitle.TitleID
Partner=25
Principal=26
Senior Associate=28
Associate=4 --->
Thanks
-paul
______________________________________________________________________
Signup for the Fusion Authority news alert and keep up with the latest news in
ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
