If Statement in Query Builder

Toad Data Point
1

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.

2

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

3

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

4

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

5

Yes, you just have to write the code.

6

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.

7

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

8

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

9

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