Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 261
  • Last Modified:

Filter Data - from a previous post

Good Evening!! I have the following code that I got from here, and it works very well for what I need to do...however there's one little problem I was hoping someone could help me with. The filter/drop down for AMOUNT_COMMITED is a dollar amount. That is a field in my Oracle database. When I select a dollar amount from the filter/drop down everything works as long as the dollar amount in the database as dollars and cents...like 1234.23 however if the dollar amount has no cents, like $12345 then the result does not return after sselecting the amount. The filter/drop down shows 12345.00 but won't return the value. Is there a way to force it to recognize the number, even if there are no cents with the dollars?? Thanks Alot!!

<CFIF isdefined('url.value') and isdefined('url.field') and url.value NEQ "*">
      <cfquery name="Recordset1" datasource="#APPLICATION.datasource#">
      SELECT MCA.MCA_BUDGET_MIPRS.MDEP, MCA.MCA_BUDGET_MIPRS.JONO, MCA.MCA_BUDGET_MIPRS.AMOUNT_COMMITED, MCA.MCA_BUDGET_MIPRS.FY
      FROM MCA.MCA_BUDGET_MIPRS
      WHERE upper(#url.field#) LIKE upper('%#url.value#%')
      ORDER BY MCA.MCA_BUDGET_MIPRS.MDEP,
MCA.MCA_BUDGET_MIPRS.JONO,
MCA.MCA_BUDGET_MIPRS.AMOUNT_COMMITED
      </cfquery>
<cfelse>
      <cfquery name="Recordset1" datasource="#APPLICATION.datasource#">
      SELECT MCA.MCA_BUDGET_MIPRS.MDEP, MCA.MCA_BUDGET_MIPRS.JONO, MCA.MCA_BUDGET_MIPRS.AMOUNT_COMMITED, MCA.MCA_BUDGET_MIPRS.FY
      FROM MCA.MCA_BUDGET_MIPRS
      ORDER BY MCA.MCA_BUDGET_MIPRS.MDEP,
MCA.MCA_BUDGET_MIPRS.JONO,
MCA.MCA_BUDGET_MIPRS.AMOUNT_COMMITED
      </cfquery>
</CFIF>

<CFQUERY NAME="QRecordType" DBTYPE="query">
SELECT distinct(upper(MDEP)) MDEP
from Recordset1
</CFQUERY>

<CFQUERY NAME="QMemberStatus" DBTYPE="query">
SELECT distinct(upper(JONO)) JONO
from Recordset1      
order by JONO
</CFQUERY>

<CFQUERY NAME="QFamilyPosition" DBTYPE="query">
SELECT distinct(upper(AMOUNT_COMMITED)) AMOUNT_COMMITED
from Recordset1      
order by AMOUNT_COMMITED
</CFQUERY>

<SCRIPT>
function refreshPage(value, field){
      location.replace("filtermenu.cfm?value="+value+"&field="+field);
}
</SCRIPT>

<body>
<table border="0" cellpadding="0" cellspacing="0">
  <tr><FORM ACTION="form" METHOD="post">
      <TH VALIGN="bottom">RecordType
<BR>
      <SELECT NAME="MDEP" SIZE="1" STYLE="font-size: xx-small;" onChange="refreshPage(this.value, this.name);">
            <OPTION VALUE="*" SELECTED>RecordType</OPTION>
      <CFOUTPUT QUERY="QRecordType">
            <OPTION VALUE="#MDEP#">#MDEP#</OPTION>
      </CFOUTPUT>
            <OPTION VALUE="*">All</OPTION>
      </SELECT></TH>
      </FORM>
      <FORM ACTION="form" METHOD="post">
      <TH VALIGN="bottom">MemberStatus
<BR>
      <SELECT NAME="JONO" SIZE="1" STYLE="font-size: xx-small;" onChange="refreshPage(this.value, this.name);">
            <OPTION VALUE="*" SELECTED>MemberStatus</OPTION>
      <CFOUTPUT QUERY="QMemberStatus">
            <OPTION VALUE="#JONO#">#JONO#</OPTION>
      </CFOUTPUT>
            <OPTION VALUE="*">All</OPTION>
      </SELECT></TH>
      </FORM>
      <FORM ACTION="form" METHOD="post">
      <TH VALIGN="bottom">FamilyPosition
<BR>
      <SELECT NAME="AMOUNT_COMMITED" SIZE="1" STYLE="font-size: xx-small;" onChange="refreshPage(this.value, this.name);">
            <OPTION VALUE="*" SELECTED>FamilyPosition</OPTION>
      <CFOUTPUT QUERY="QFamilyPosition">
            <OPTION VALUE="#AMOUNT_COMMITED#">#AMOUNT_COMMITED#</OPTION>
      </CFOUTPUT>
            <OPTION VALUE="*">All</OPTION>
      </SELECT></TH>
      </FORM>
    <td>FirstName</td>
    <td>LastName</td>
    <td>FGender</td>
  </tr>
  <cfoutput query="Recordset1">
    <tr>
      <td>#Recordset1.MDEP#</td>
      <td>#Recordset1.JONO#</td>
      <td>#Recordset1.AMOUNT_COMMITED#</td>
    </tr>
  </cfoutput>
</table>
0
diecasthft
Asked:
diecasthft
  • 3
  • 3
1 Solution
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
i can see this query in your code...

  SELECT MCA.MCA_BUDGET_MIPRS.MDEP, MCA.MCA_BUDGET_MIPRS.JONO, MCA.MCA_BUDGET_MIPRS.AMOUNT_COMMITED, MCA.MCA_BUDGET_MIPRS.FY
      FROM MCA.MCA_BUDGET_MIPRS
      WHERE upper(#url.field#) LIKE upper('%#url.value#%') --> is the dollar amount filter this one...
      ORDER BY MCA.MCA_BUDGET_MIPRS.MDEP,
MCA.MCA_BUDGET_MIPRS.JONO,
MCA.MCA_BUDGET_MIPRS.AMOUNT_COMMITED

what does #url.field# and what does #url.value# will have as sample value so that i can think off how your sql will look like and see whether it looks ok or not.

0
 
_agx_Commented:
(no points please ...)

>> WHERE upper(#url.field#) LIKE upper('%#url.value#%')
As an aside:

1)  The sql you're using is unsafe. If your db supports multiple statements, that query is and vulnerable to sql injection
2)  Using LIKE for a numeric value may be part of the problem.  String comparison shouldn't be used for numeric columns because they don't always return the same results. They're also less efficient.
 

>>> <SELECT NAME="AMOUNT_COMMITED" onChange="refreshPage(this.value, this.name);" ...>

@nav_kum_v  -  Just looking at the javascript code #url.field# should be the name of the form element ie "AMOUNT_COMMITED" and #url.value# is one of the list elements.  From what the asker said, and example would be "12345"

0
 
diecasthftAuthor Commented:
Is there a different way to get what I need to do here....other than the LIKE operator?? My initial thought was to change the database to a varchar type, versus Number, but if I do that, I'm afraid my calculations that I do on those dollar fields in other parts of my application won't work.

I wasn't aware of the sql injection issues.....I'll need to look at that a little closer. Thanks for the info.
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
_agx_Commented:
>> I wasn't aware of the sql injection issues
It only applies to db's that support the execution of multiple statements. BUT ... even still, it's considered a bad practice to execute raw user-supplied values against your db.  


>> other than the LIKE operator??
Security issues aside, the sql you should use depends on the column data type and results you want.  If you're looking for an exact match on a string or numeric column you should use equals "=".  LIKE is only appropriate when you're looking for a partial match on a string column. Example, find all records starting with "AB%" OR records ending with "%EFG"

So what are the data types of your columns (varchar2, numeric, ...) and what results are you looking for (exact match, partial match, numeric range - between x and y, ...)?

0
 
diecasthftAuthor Commented:
I have a mixed bag of data types. Most are varchar2, but the dollar amount fields are number types, with a scale of 2 in an Oracle database. I have to do calculations on those numbers, and don't think I can do that correctly if I made them varchar2 as well. I tried to do the = versus the % but that didn't seem to work either.
0
 
diecasthftAuthor Commented:
I'm sorry...I mean't the = instead of the LIKE operator.
0
 
_agx_Commented:
>> don't think I can do that correctly if I made them varchar2 as well

      Yes, they definitely should NOT be varchar2's.  

>> I tried to do the = versus the % but that didn't seem to work either.

      I see no reason it shouldn't.  I don't use Oracle, but the basics should be the same
      as w/other db types.  Create some hard coded queries that give you the desired
      results when run directly against your db

      ie    WHERE  SomeVarCharColumn = 'some string value'
             ....
             WHERE  NumericColumn = 1500.75
             ....
             WHERE  NumericColumn BETWEEN 1500.00 AND 2000.00

Then convert them to CF code.  Since you only have 3 fields to match on, you could use a simple cfif or a cfswitch to construct the WHERE clause.  For example

      <cfif url.field eq "SomeVarCharColumn" AND trim(url.value) neq "*">
           WHERE  SomeVarCharColumn = '#url.value#'
      </cfif>

... or for numeric fields

      <cfif url.field eq "SomeVarCharColumn" AND trim(url.value) neq "*">
           WHERE  NumericColumn = #url.value#
      </cfif>

Once you've got it working wrap all of the #url.value#'s in cfqueryparam. Change the cfsqltype's to match the data types of your columns

ie
           WHERE  SomeVarCharColumn = <cfqueryparam value="#url.value#" cfsqltype="cf_sql_varchar">


http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-7f6f.html




0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now