cfqueryparam decimal datatype issue

I suspect that Decimal may be rounding your number to two decimal places.  So the correct result of 4.134 rounds down and would be returned but 3.937 rounds up, causing it to change to 3.94 and not be returned.  Did you try CF_SQL_FLOAT as the data type?

As a last resort you could forgo using cfqueryparam altogether and just go with:

SELECT DISTINCT machineName
    FROM web_conv_dish_blades
    WHERE od = #arguments.od#
    ORDER BY machineName;

I tend to use CF_SQL_NUMERIC  it avoids the issue of the exact numeric data type and allows correct binding to any numeric data.

You many also want to to actually look at the data in the database. Is it possible that there are additional digits of data, but you are rounding it in the select or display of the data so it looks like 3.937 would match, but in reality the data is 3.9372 or 3.9368?  If this is the case, then matching on 3.973 isn't going to work no matter which bind variable type you pass in...  This seems more likely...

Yes, I tried float, decimal and double and all did the same thing.

I don't know if rounding is the issue. Another value that returns the correct result is 4.156. If it was rounding to 2 decimal places, this value would be 4.16 and should return 0 results. #arguments.od# is being passed to the CFC via a URL var so I want to use cfqueryparam to ensure data integrity.

The data in the DB is in fact correct and matches the display. I was very careful not to do any rounding in the display as this site will be used by engineers who need exact specifications. I went so far as to manually retype the values in the DB thinking that when it was exported from Oracle there was some trailing nonsense like line breaks.

Is it a float in the database? If so, unfortunately, there is always rounding on floats... .1 base 10, when converted to binary as a floating point number is irrational... like 1/3 base 10 being .333333.

.1 (base to) converts to .1100110011001100...... in base2.
This should only be a POTENTIAL problem if the number is floating point.

Have you tried the same select statement directly using the database tools...
 SELECT DISTINCT machineName
    FROM web_conv_dish_blades
    WHERE od = 3.937    ORDER BY machineName;
This will ensure that it isn't actually a database problem.

If that works, then try using just CF_SQL_NUMERIC

Can you turn on CF Debugging and see the actual values CFQUERY PARAM is passing to the query?  That will give us some insight to the problem here.

In regards to not using <cfqueryparam> at all, I would avoid that if at all possible, not just for the editing, you can do that with a simple <CFIF isnumber(url.parmvalue)> or with a <CFPARAM> tag to ensure the URL is numeric, but because passing BIND VARIABLES to the database is a good thing.  It makes life easier on the databases.

I missed your comment on CF_SQL_VARCHAR Working.... Use CF_SQL_NUMERIC. I strongly suspect that will work..
<cfquery name="getOD" datasource="#request.dsn#" username="#request.username#" password="#request.password#">
    SELECT DISTINCT machineName
    FROM web_conv_dish_blades
    WHERE od = <cfqueryparam cfsqltype="cf_sql_NUMERIC" value="#arguments.od#">
    ORDER BY machineName;
</cfquery>

The column is defined as a double. When I ran the query directly on the database using Navicat, it did the same thing: returned nothing. However, I changed the 3.937 to '3.937' and it worked. It seems as if the database isn't interpreting 3.937 as a numeric value.

I suppose a potential answer is to not use cfqueryparam and do manual data verification, but I thought cfqueryparam was supposed to be an easy way around that.

Did you say you were using Oracle?  What if your switch the column type to Number?  This is looking more and more like a DB issue the CF.

Debugging gave me this:

#      CFSQLType      value
1        cf_sql_decimal   3.937

The chain this data went through is oracle (don't know what version - that's handled by an outside engineer) to MS Access 2002 to MySQL 5. Being an oracle person, can you think of any potential problems running the data through a chain like this? I'm grasping at straws at this point.

cf_sql_numeric did not work.

RFCorfman, your CFPARAM suggestion worked (with one small change = attribute is type, not data_type). This will be my work around for now. It's clear that it's not a CF problem but a MySQL problem. Thank you so much for your input.

Doesn't matter what the data chain is if it doesn't work when you call it as close to the mysql data as possible, using their tools... that is where the problem occurs.  Glad there is a reasonable work around. I really would ask the question in the Database-->Mysql TA if you are curious and have points to use.

Sorry about the bad attribute, I misread my documentation... it said
<cfparam name="param_name" type="data_type" devault="value">
When I typed it into EE I had a "brain slip".  ;)

I just posted the question in the MySQL forum. Thanks again!

The MySQL experts came up with a good work around.

https://www.experts-exchange.com/questions/21787678/decimal-data-type-issue.html

Doesn't seem to be a problem in Oracle directly, not that it really matters much...
float in Oracle does round, it is IEEE internally, number is more like they are describing decimal in the mysql post.  In the case of Oracle, it seems to work with either one...

SQL> create table udttemp(x float, n number);  

Table created.

SQL> insert into udttemp
  2  select 3.937,3.937 from dual union all
  3  select 4.134,4.134 from dual;

2 rows created.

SQL> select * from udttemp where x in (3.937,4.134);

         X          N
---------- ----------
     3.937      3.937
     4.134      4.134

SQL> select * from udttemp where n in (3.937,4.134);

         X          N
---------- ----------
     3.937      3.937
     4.134      4.134

SQL>