How to multiply variable in cfif

[mdd]

I need to turn this SQL statement into CF syntax:

if :dr_dr_flag='D' then return (:amount*(-1)); else return (:amount); end if

The code that follows is from a simple test page with just enough code to test this.

My query:

<cfquery name=&quot;rsDocid&quot; datasource=&quot;cgofprod&quot;>
SELECT distinct
 fut.doc_cntl_num docid
,fut.dr_cr_flag
,fut.amount
FROM cgof_fund_use_trans fut
WHERE fut.doc_cntl_num  LIKE  upper('2803233tmx004000')
and batch_id = '03188FH6V'
</cfquery>

 <cfoutput query=&quot;rsDocid&quot; startrow=&quot;#StartRow_rsDocid#&quot; maxrows=&quot;#MaxRows_rsDocid#&quot;> 
  <table width=&quot;75%&quot; border=&quot;1&quot;>
    <tr> 
      <td>#rsDocid.DOCID#</td>
	  <cfif #rsDocid.AMOUNT# EQ 'D'>
        <td>  (#rsDocid.AMOUNT#*-1) </td>
	  <cfelse> <td>#rsDocid.AMOUNT#</td>
	  </cfif>
      <td>#rsDocid.DR_CR_FLAG#</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
  </table>

I have tried various ways to get this to work. This code simply returns the values of AMOUNT without making the D negative. THX, MDD

 

[TruthInSatire]

Try this

<cfoutput query=&quot;rsDocid&quot; startrow=&quot;#StartRow_rsDocid#&quot;
maxrows=&quot;#MaxRows_rsDocid#&quot;>
<table width=&quot;75%&quot; border=&quot;1&quot;>
<tr>
<td>#rsDocid.DOCID#</td>
<cfif #rsDocid.AMOUNT# EQ 'D'>
<td> (#evaluate(rsDocid.AMOUNT*-1)#) </td>
<cfelse> <td>#rsDocid.AMOUNT#</td>
</cfif>
<td>#rsDocid.DR_CR_FLAG#</td>
<td> </td>
<td> </td>
</tr>
</table>

http://thereptilian.com/

 

[r937]

making the amount negative can be accomplished in three ways: cosmetically, by appending a minus sign to the front or back of the string which is the displayed amount, arithmetically by CF, or arithmetically by getting the database to do it for ya

let's not try the first method

for the second method, the main thing you have to do is write your CFIF test on the correct field (you're testing the amount instead of the flag!)

however, if you output (#rsDocid.AMOUNT#*-1) then you are in effect doing the first method, appending the characters &quot;*-1&quot; to the end of the displayed amount

i think you could use Eval(), except I wouldn't, i would use the following instead

<CFSET plusminusamount = rsDocid.AMOUNT - 1 >

and then output #plusminusamount#

finally, the simplest method (at least, in my opinion) is simply to return the correct amount from the query, so that you don't have to do the CFIF in the output at all:

[tt]SELECT distinct
fut.doc_cntl_num docid
, case when fut.dr_cr_flag = 'D'
then -1 else 1 end
* fut.amount as amount
FROM ...[/tt]

rudy

http://r937.com/

 

[mdd]

Thank you for your responses. Rudy's suggestion worked perfectly! I simply inserted that small bit of code into my huge select on the real app, and it works perfectly!