How to convert string to lowercase? - URGENT

[jasonwcm]

Hi..

Am trying to convert string to lowercase using LOWER but I was prompted with this error:

Query Manipulation Error Code = 0

Error: LOWER is not a number



This is my query:

<cfquery name=&quot;QryMonoTones&quot; datasource=&quot;#Attributes.dsn#&quot; username=&quot;#Attributes.dbusr#&quot; password=&quot;#Attributes.dbpwd#&quot;>
SELECT ID, name, artist, catID, keyword, status, created, last, webstatus
FROM #Attributes.tblName#
</cfquery>

<cfquery name=&quot;QryList&quot; dbtype=&quot;query&quot;>
SELECT QryMonoTones.ID AS ID, QryMonoTones.name AS name, QryMonoTones.artist AS artist, QryMonoTones.status AS status, QryMonoTones.created AS created, QryMonoTones.last AS last, QryMonoTones.webstatus AS webstatus,
QryMonoCat.name AS catName
FROM QryMonoTones, QryMonoCat
WHERE QryMonoTones.catID = QryMonoCat.ID
<cfset loop = 0>
<cfloop list=&quot;#Trim(Attributes.txtSearch)#&quot; delimiters=&quot;,&quot; index=&quot;searchWord&quot;>
<cfif Len(Trim(searchWord))>
<cfset searchWord = LCase(Trim(searchWord))>
<cfset searchWord = Trim(searchWord)>
<cfset loop = loop + 1>
#IIF ( loop IS 1, DE(&quot;AND (&quot,DE(&quot;OR&quot )#
LOWER(QryMonoTones.name) LIKE '%#searchWord#%'
OR LOWER(QryMonoTones.artist) LIKE '%#searchWord#%'
OR LOWER(QryMonoTones.keyword) LIKE '%#searchWord#%'
OR LOWER(QryMonoCat.name) LIKE '%#searchWord#%'
</cfif>
</cfloop>
<cfif loop IS NOT 0>)</cfif>
ORDER BY #Attributes.sortBy# #Attributes.sortOrder#
</cfquery>

Can't see what is wrong. Seems that I can't put LOWER in the WHERE condition? Yes/No?

Pls help..

Need reply ASAP.

Thanks.

 

[r937]

while LOWER is a perfectly good function in sql server, you aren't running a sql server query, you're running a coldfusion query-of-queries, and should be using LCase

which leads to the question of why it's a query-of-queries, why can't you do the join between QryMonoTones and QryMonoCat in sql server


rudy

http://r937.com/

SQL Consulting

 

[jasonwcm]

Thanks for the fast reply.

I didn't join the 2 queries because both are referring to different datasource name.

LCase didn't seem to work either.

Query Manipulation Error Code = 0

Error: LCASE is not a number

I've also tried putting it inside single and double quotes, surrounded with hash (#) and both didn't work.

Any other idea?

Cheers,

Jason

 

[r937]

probably a syntax error, keyword missing or something

your IIF looks mighty suspicious

instead of CFQUERY to run the query, use CFOUTPUT instead, so you can see the final query as it would be executed

i bet that IIF isn't working

rudy

http://r937.com/

SQL Consulting

 

[jasonwcm]

Nothing seems wrong with the IIF...

Basically it means to include some conditions.. in the following syntax..


SELECT QryMonoTones.ID AS ID, QryMonoTones.name AS name, QryMonoTones.artist AS artist, QryMonoTones.status AS status, QryMonoTones.created AS created, QryMonoTones.last AS last, QryMonoTones.webstatus AS webstatus,
QryMonoCat.name AS catName
FROM QryMonoTones, QryMonoCat
WHERE QryMonoTones.catID = QryMonoCat.ID
AND
(
LOWER(QryMonoTones.name) LIKE '%day%'
OR LOWER(QryMonoTones.artist) LIKE '%day%'
OR LOWER(QryMonoTones.keyword) LIKE '%day%'
OR LOWER(QryMonoCat.name) LIKE '%day%'
)

 

[r937]

yeah, everything looks fine except the LOWER

rudy

http://r937.com/

SQL Consulting

 

[SQLBill]

IIF isn't acceptable in SQL Server's TSQL. It's an Access command.

-SQLBill

 

[r937]

SQLBill, IIF in this particular context is a ColdFusion function

rudy

http://r937.com/

SQL Consulting