diecasthft
asked on
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.d
SELECT MCA.MCA_BUDGET_MIPRS.MDEP,
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.AMOUN
</cfquery>
<cfelse>
<cfquery name="Recordset1" datasource="#APPLICATION.d
SELECT MCA.MCA_BUDGET_MIPRS.MDEP,
FROM MCA.MCA_BUDGET_MIPRS
ORDER BY MCA.MCA_BUDGET_MIPRS.MDEP,
MCA.MCA_BUDGET_MIPRS.JONO,
MCA.MCA_BUDGET_MIPRS.AMOUN
</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_COMM
from Recordset1
order by AMOUNT_COMMITED
</CFQUERY>
<SCRIPT>
function refreshPage(value, field){
location.replace("filterme
}
</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
<OPTION VALUE="*" SELECTED>RecordType</OPTIO
<CFOUTPUT QUERY="QRecordType">
<OPTION VALUE="#MDEP#">#MDEP#</OPT
</CFOUTPUT>
<OPTION VALUE="*">All</OPTION>
</SELECT></TH>
</FORM>
<FORM ACTION="form" METHOD="post">
<TH VALIGN="bottom">MemberStat
<BR>
<SELECT NAME="JONO" SIZE="1" STYLE="font-size: xx-small;" onChange="refreshPage(this
<OPTION VALUE="*" SELECTED>MemberStatus</OPT
<CFOUTPUT QUERY="QMemberStatus">
<OPTION VALUE="#JONO#">#JONO#</OPT
</CFOUTPUT>
<OPTION VALUE="*">All</OPTION>
</SELECT></TH>
</FORM>
<FORM ACTION="form" METHOD="post">
<TH VALIGN="bottom">FamilyPosi
<BR>
<SELECT NAME="AMOUNT_COMMITED" SIZE="1" STYLE="font-size: xx-small;" onChange="refreshPage(this
<OPTION VALUE="*" SELECTED>FamilyPosition</O
<CFOUTPUT QUERY="QFamilyPosition">
<OPTION VALUE="#AMOUNT_COMMITED#">
</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_COM
</tr>
</cfoutput>
</table>
<CFIF isdefined('url.value') and isdefined('url.field') and url.value NEQ "*">
<cfquery name="Recordset1" datasource="#APPLICATION.d
SELECT MCA.MCA_BUDGET_MIPRS.MDEP,
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.AMOUN
</cfquery>
<cfelse>
<cfquery name="Recordset1" datasource="#APPLICATION.d
SELECT MCA.MCA_BUDGET_MIPRS.MDEP,
FROM MCA.MCA_BUDGET_MIPRS
ORDER BY MCA.MCA_BUDGET_MIPRS.MDEP,
MCA.MCA_BUDGET_MIPRS.JONO,
MCA.MCA_BUDGET_MIPRS.AMOUN
</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_COMM
from Recordset1
order by AMOUNT_COMMITED
</CFQUERY>
<SCRIPT>
function refreshPage(value, field){
location.replace("filterme
}
</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
<OPTION VALUE="*" SELECTED>RecordType</OPTIO
<CFOUTPUT QUERY="QRecordType">
<OPTION VALUE="#MDEP#">#MDEP#</OPT
</CFOUTPUT>
<OPTION VALUE="*">All</OPTION>
</SELECT></TH>
</FORM>
<FORM ACTION="form" METHOD="post">
<TH VALIGN="bottom">MemberStat
<BR>
<SELECT NAME="JONO" SIZE="1" STYLE="font-size: xx-small;" onChange="refreshPage(this
<OPTION VALUE="*" SELECTED>MemberStatus</OPT
<CFOUTPUT QUERY="QMemberStatus">
<OPTION VALUE="#JONO#">#JONO#</OPT
</CFOUTPUT>
<OPTION VALUE="*">All</OPTION>
</SELECT></TH>
</FORM>
<FORM ACTION="form" METHOD="post">
<TH VALIGN="bottom">FamilyPosi
<BR>
<SELECT NAME="AMOUNT_COMMITED" SIZE="1" STYLE="font-size: xx-small;" onChange="refreshPage(this
<OPTION VALUE="*" SELECTED>FamilyPosition</O
<CFOUTPUT QUERY="QFamilyPosition">
<OPTION VALUE="#AMOUNT_COMMITED#">
</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_COM
</tr>
</cfoutput>
</table>
(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
@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"
>> 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
@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"
ASKER
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.
I wasn't aware of the sql injection issues.....I'll need to look at that a little closer. Thanks for the info.
>> 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, ...)?
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, ...)?
ASKER
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.
ASKER
I'm sorry...I mean't the = instead of the LIKE operator.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SELECT MCA.MCA_BUDGET_MIPRS.MDEP,
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.AMOUN
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.