OraFAQ Forum: Forms » decode in formula column in oracle form

Home » Developer & Programmer » Forms » decode in formula column in oracle form

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

decode in formula column in oracle form [message #603683]

Wed, 18 December 2013 07:24

irfankundi786@yahoo.com
Messages: 269
Registered: February 2009
Location: pakistan

Senior Member

i have a number coloumn need this formula as

decode(:rate,null,dem_amount,:dem_qty * :rate)
how i can write the decode handle in formula column?

Report message to a moderator

Re: decode in formula column in oracle form [message #603684 is a reply to message #603683]

Wed, 18 December 2013 07:44

Littlefoot
Messages: 21809
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

DECODE can be used in SELECT statement; therefore, switch to CASE:

case when :rate is null then :dem_amount
     else :dem_qty * :rate
end

Report message to a moderator

Re: decode in formula column in oracle form [message #603687 is a reply to message #603684]

Wed, 18 December 2013 08:03

irfankundi786@yahoo.com
Messages: 269
Registered: February 2009
Location: pakistan

Senior Member

i return error
pl/sql error at 103 encounter the symbol case etc......
i am using form 6i.

Report message to a moderator

Re: decode in formula column in oracle form [message #603719 is a reply to message #603687]

Wed, 18 December 2013 14:39

Littlefoot
Messages: 21809
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

As formula column accepts PL/SQL code, write a stored (database) function which would accept form items' values as parameters and return the result; call that function from a formula column. It would make possible to use CASE; or, if you choose to create a form function, there's no problem in using DECODE either as you could SELECT DECODE ... INTO in it.

Or, if it doesn't work either, forget about formula column and create a WHEN-VALIDATE-ITEM trigger(s) which would calculate the desired result.

Report message to a moderator

Re: decode in formula column in oracle form [message #604958 is a reply to message #603683]

Mon, 06 January 2014 09:21

sreekumar.nair.it
Messages: 16
Registered: January 2014

Junior Member

Hi,

For this requirement, I will proceed with the following assumptions:

1) User first enter value in DEM_QTY and DEM_AMOUNT field, then tab out to the next item RATE.
2) In KEY-NEXT-ITEM of RATE field, our logic will check the RATE field value
and populate the same with the required value as follows:

KEY-NEXT-ITEM of RATE field
----------------------------
BEGIN
SELECT decode(:BLOCK_NAME.rate,null,:BLOCK_NAME.dem_amount,:BLOCK_NAME.dem_qty * :BLOCK_NAME.rate)
into :BLOCK_NAME.RATE
from dual;
END;

The triggering event may change as per the requirement. As your requirement was not detailed one, I have used KEY-NEXT-ITEM.
Please let me know if you have any query.

Regards,
Sreee

Report message to a moderator

Re: decode in formula column in oracle form [message #604965 is a reply to message #604958]

Mon, 06 January 2014 09:59

cookiemonster
Messages: 13923
Registered: September 2008
Location: Rainy Manchester

Senior Member

@sreekumar.nair.it - welcome to the forum. Please read and follow How to use [code] tags and make your code easier to read?

Since users can use a mouse to navigate from an item (or shift-tab, or up or down) key-next-item is never a good choice for such functionality. When-validate-item (as LF already suggested) would be a better bet.

Report message to a moderator

Re: decode in formula column in oracle form [message #605671 is a reply to message #604965]

Mon, 13 January 2014 05:54

sreekumar.nair.it
Messages: 16
Registered: January 2014

Junior Member

Hi cookiemonster,

Thank you for your kind reply. Going forward I will use the tag mentioned for code formatting.

I know WHEN-VALIDATE-ITEM is the preferred option, just for giving an example I have used KEY-NEXT-ITEM and for that reason, I have already
mentioned in my post that the triggering event may change as per requirement.

Regards,
Sreee

Report message to a moderator

Previous Topic:	Error: To retrieve the Information in this block, use menu option view in oracle apps form
Next Topic:	sql Function

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri May 17 02:08:32 CDT 2024