Toad World® Forums

If Statement in Query Builder

I would like to add a calculated column in the query builder tool. I would like to switch the signs on amounts that have related a billing code of 2000 or greater. If the code is less than 2000 I want the sign to remain the same. Is there a way to do this via a calculated column?

Thanks.

You need to make a calculate field.

Case
When Billing_Code < 20000 Then My_Amount_Field
Else ( My_Amount_Field * -1 ) /* will change the amount to a negative assuming the Amt is positive */
End AMT

Yes, just create a calculated column (click the button with the sigma symbol to begin) and use a CASE statement. Something like:

case when table_name.Billing_Code >= 2000 then ( table_name.amount * (-1) ) else table_name.amount end

Thanks…I don’t see a CASE function in the drop down box. Can I just type that statement into the field?

Yes, you just have to write the code.

Yes, click the button to create the field, name it, click the plus button, then click the drop-down in the Field Definition area and type in the function. Choose a table to attach the calculated field to, and it will be in your query.

Thanks…I tried that still getting a lookup error ORA-00936: missing expression.

Here’s my code:

CASE WHEN LINE_ITEM_DETAIL.ILD_LINE_ITEM_ID >= 2000 THEN ( LINE_ITEM_DETAIL.ILD_AMOUNT (*-1)) ELSE LINE_ITEM_DETAIL.ILD_AMOUNT END

Can you try this out?

CASE WHEN LINE_ITEM_DETAIL.ILD_LINE_ITEM_ID >= 2000

THEN NVL(LINE_ITEM_DETAIL.ILD_AMOUNT,0) * -**1 **

ELSE NVL(LINE_ITEM_DETAIL.ILD_AMOUNT,0)

END MyFieldName

Thank you both very much. That worked! I appreciate it!