Answered
Query of Query Case Sensative
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
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
AdChoices