AndroidIn these days, I needed to manipulate Query statement in Oracle. Because, for database part, we don't want our CF to calculate the amount of our system. That's why we just let Oracle to do as much as database part can do. Because we don't want JRun can be more loaded because of such calculation and minor cases and we are always try to assign less process for our CF. Today, I need to develop some calculation in our projects for the following cases.
2 <!--- ONHAND_QTY + PROJECTED_QTY ARE GREATER THAN MIN_QTY --->
3 <cfset IsReplenish = "Y">
4<cfelse>
5 <!--- ONHAND_QTY + PROJECTED_QTY ARE NOT GREATER THAN MIN_QTY --->
6 <cfset IsReplenish = "N">
7</cfif>
My senior wants me to do above calculation in database part. It's sweet challenge for me to make calculation in database part because I'm very new in Oracle and not have that much experience in Oracle. For this challenge, I think DECODE and CASE WHERE can solve for sure. Among these twos, I've selected DECODE as my boss.
Here is the usage and description for DECODE.
decode(expression , search , result [, search , result]... [, default])
Now, here is solution for my problem to calculate above transaction for database part..
2 SELECT DECODE(TRUNC((ONHAND_QTY + PROJECTED_QTY)/MIN_QTY), 0, 'Y', 1, 'N') REP
3 FROM TBL_TRX)QTY
4 ORDER BY ITEM_CODE
5</CFQUERY>
Top of Page
#1 by beth on 6/24/11 - 6:09 AM
select case when min_qty = 0 then 'n/a'
when TRUNC(ONHAND_QTY + PROJECTED_QTY) = 0 then 'y'
else '0'
end as rep
from tbl_trx_qty
order by item_code
#2 by ppshein on 6/24/11 - 6:21 AM
Thanks for comments and feel it urge me to use CASE WHERE for future.