Query of Query Case Sensative

[Hagster]

Hello all
Got a simple problem, I run a main query:

<CFQUERY name="getprice" datasource="adatabase">
SELECT RTRIM(plpart) AS plpart, MIN(plunitprice) AS plunitprice
FROM pricelist
GROUP BY plpart
</CFQUERY>

now in the table pricelist some parts are entered in lowercase i.e the results could look like below:

Part____Price
ABc, 10
DeF, 11
ghi, 12
JKL, 10

If I query the datasource direct there is no problem with the case being upper or lower, but when I query of query like below it only returns a result if the LIKE '#variable#' is the correct case, how do I get around this?

<CFquery dbtype="query" name="qoq_price">
SELECT plunitprice
From getprice
WHERE plpart LIKE '#variable#'
</CFquery>

for example
if variable was 'ABc' the QOQ returns the correct price of 10
but
if variable was 'ABC' the QOQ does not return a result

Trouble is my variables come from one table where they are all in Upper case but in my price table people have entered them in both cases and sometimes mixed.

Kind Regards Guy

[boughtonp]

Have you tried something like this?

WHERE UCase(plpart) LIKE '#UCase(Variable)#'

[Hagster]

The bold bit throws an error

Query Of Queries syntax error.
Encountered "( plpart ) LIKE" at line 4, column 12. Incorrect conditional expression, Expected one of [like|null|between|in|comparison] condition, Unrecognized comparison operator,

WHERE UCase(plpart) LIKE '#UCase(Variable)#'

This works
WHERE plpart LIKE '#UCase(Variable)#'

But then im turning the wrong side into upper case.
I need a kind of no case or ignore case format so in PSUDO it would be somthing like

WHERE plpart LIKE #ignorecase(#variable#)#

if you see what i mean.

Regards Guy

[boughtonp]

Ooops, you need to use Upper for the SQL side of things:
WHERE Upper(plpart) LIKE '#UCase(variable)#'

That (or Lower/LCase) is the simplest way to ignore case - I'm not aware of any specific case-insensitive flag that is available.

[Hagster · Accepted solution]

Thanks Peter

Completely passed my mind to just grab the SQL in Upper case, was to busy trying to look for a ignore case thingy.

Any way works a treat thanks