OraFAQ Forum: Application Express, ORDS & MOD_PLSQL » how to update the column in table based on the value selected in apex4.1

Home » Developer & Programmer » Application Express, ORDS & MOD_PLSQL » how to update the column in table based on the value selected in apex4.1 (apex 4.1)

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

how to update the column in table based on the value selected in apex4.1 [message #555553]

Fri, 25 May 2012 01:33

gurujothi
Messages: 80
Registered: January 2012
Location: Banglore

Member

Hello everyone,
I have the following tables,
leave_type table,it has the following fields,

1.emp_name,
2.sick
3.casual

and it has the values like following,

emp_name                       sick             casual
guru                           10                10
mishra                         10                10

and leave_master table,it has the following fields,

1.emp_name,
2.leave_type
3.no_of_days

I have the form based on the "leave_master" table,
here the leave_type has the LOV which includes,
sick and casual,

when the form is filled and clicked the submit button,
i need to update the leave_type column in leave_type table,
for example,
if
emp_name:guru
leave_type:sick
no_of_days:3

then I want to update the leave_type column as,
sick=sick - no_of_days for the name 'guru'
i.e, sick=10-3=7

then the table leave_type should be,

emp_name                             sick             casual
guru                                  7                10
mishra                                10               10

can anyone tel me what code or method I can use?
Thank you.

Report message to a moderator

Re: how to update the column in table based on the value selected in apex4.1 [message #555554 is a reply to message #555553]

Fri, 25 May 2012 01:36

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

Senior Member
Account Moderator

Looks like a process - that fires when the button is pressed - would do the job. Put your UPDATE statements in there (into a process code, I mean).

Report message to a moderator

Re: how to update the column in table based on the value selected in apex4.1 [message #555561 is a reply to message #555554]

Fri, 25 May 2012 01:58

gurujothi
Messages: 80
Registered: January 2012
Location: Banglore

Member

Hi Littlefoot,
I have created the 'ON-SUBMIT after computation and validation' process and put the following code,

declare
x leave_master.leave_type%type;
begin
if :P1_LEAVE_TYPE='sick' then
select sick into x from leave_type where upper(emp_name)=upper(:APP_USER);
end if;
apex_application.g_print_success_message := 'days: '||x ;
end;

here it should show "days:10", right?
but it showing blank i.e days:
even its not selecting the value based on the IF condition,
but if I remove and try the following,

declare
x leave_master.leave_type%type;
begin

select sick into x from leave_type where upper(emp_name)=upper(:APP_USER);

apex_application.g_print_success_message := 'days: '||x ;
end;

it showing "days:10"

only if condition is the problem, I dont know why.
Thank you.

Report message to a moderator

Re: how to update the column in table based on the value selected in apex4.1 [message #555562 is a reply to message #555561]

Fri, 25 May 2012 02:08

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

Senior Member
Account Moderator

It seems that P1_LEAVE_TYPE is not 'sick' but something else. Display its value (possibly its length, or enclose it into something else, for example '#' character) so that you'd see what is its real value. If column datatype is a CHAR (which is different from VARCHAR2), then it is right-padded with "blanks" so you might need to trim them off.

Report message to a moderator

Re: how to update the column in table based on the value selected in apex4.1 [message #555563 is a reply to message #555562]

Fri, 25 May 2012 02:17

gurujothi
Messages: 80
Registered: January 2012
Location: Banglore

Member

Hi Littlefoot,
No,its varchar2 only,
now I tried this code,


declare
x leave_master.leave_type%type;
begin
if :P9_LEAVE_TYPE  IN ('sick') then
select sick_leave into x from leave_type where upper(emp_name)=upper(:APP_USER);
else
select casual into x from leave_type where upper(emp_name)=upper(:APP_USER);
end if;
apex_application.g_print_success_message := 'ex_days: '||x ;
end;

now its working and displaying correct value for 'x'.
Now I will try to add update code in it.

Report message to a moderator

Previous Topic:	VALIDATION IN APEX
Next Topic:	How to store the Emp_name instead of username in application Item

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Dec 12 07:16:55 CST 2024