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)
how to update the column in table based on the value selected in apex4.1 [message #555553] Fri, 25 May 2012 01:33 Go to next message
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.
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 Go to previous messageGo to next message
Littlefoot
Messages: 21805
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).
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 Go to previous messageGo to next message
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.
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 Go to previous messageGo to next message
Littlefoot
Messages: 21805
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.
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 Go to previous message
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.
Previous Topic: VALIDATION IN APEX
Next Topic: How to store the Emp_name instead of username in application Item
Goto Forum:
  


Current Time: Tue Mar 19 03:04:19 CDT 2024