4 Replies Latest reply on Apr 27, 2006 4:55 AM by Hagster

    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