Solved

Filter Data - from a previous post

Posted on 2010-09-21
7
249 Views
Last Modified: 2013-12-24
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
Comment
Question by:diecasthft
  • 3
  • 3
7 Comments
 
LVL 28

Expert Comment

by:Naveen Kumar
Comment Utility
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
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
(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
 

Author Comment

by:diecasthft
Comment Utility
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 52

Expert Comment

by:_agx_
Comment Utility
>> 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
 

Author Comment

by:diecasthft
Comment Utility
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
 

Author Comment

by:diecasthft
Comment Utility
I'm sorry...I mean't the = instead of the LIKE operator.
0
 
LVL 52

Accepted Solution

by:
_agx_ earned 500 total points
Comment Utility
>> 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

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

CFGRID Custom Functionality Series -  Part 1 Hi Guys, I was once asked how it is possible to to add a hyperlink in the cfgrid and open the window to show the data. Now this is quite simple, I have to use the EXT JS library for this and I achiev…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now