Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Filter Data - from a previous post

Posted on 2010-09-21
7
Medium Priority
?
255 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 33730649
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_
ID: 33733569
(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
ID: 33733981
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 your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 52

Expert Comment

by:_agx_
ID: 33734516
>> 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
ID: 33777955
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
ID: 33777961
I'm sorry...I mean't the = instead of the LIKE operator.
0
 
LVL 52

Accepted Solution

by:
_agx_ earned 2000 total points
ID: 33802090
>> 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database

721 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