Home » Developer & Programmer » Forms » decode in formula column in oracle form
decode in formula column in oracle form [message #603683] Wed, 18 December 2013 07:24 Go to next message
irfankundi786@yahoo.com
Messages: 214
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?
Re: decode in formula column in oracle form [message #603684 is a reply to message #603683] Wed, 18 December 2013 07:44 Go to previous messageGo to next message
Littlefoot
Messages: 19508
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
Re: decode in formula column in oracle form [message #603687 is a reply to message #603684] Wed, 18 December 2013 08:03 Go to previous messageGo to next message
irfankundi786@yahoo.com
Messages: 214
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.
Re: decode in formula column in oracle form [message #603719 is a reply to message #603687] Wed, 18 December 2013 14:39 Go to previous messageGo to next message
Littlefoot
Messages: 19508
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.
Re: decode in formula column in oracle form [message #604958 is a reply to message #603683] Mon, 06 January 2014 09:21 Go to previous messageGo to next message
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
Re: decode in formula column in oracle form [message #604965 is a reply to message #604958] Mon, 06 January 2014 09:59 Go to previous messageGo to next message
cookiemonster
Messages: 10914
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.
Re: decode in formula column in oracle form [message #605671 is a reply to message #604965] Mon, 13 January 2014 05:54 Go to previous message
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
Previous Topic: IMAGE ON FORM 6i
Next Topic: sql Function
Goto Forum:
  


Current Time: Wed Aug 27 10:54:46 CDT 2014

Total time taken to generate the page: 0.10547 seconds