PL/SQL combine two columns in one variable [message #302692] |
Tue, 26 February 2008 11:31  |
shree_z
Messages: 75 Registered: February 2008
|
Member |
|
|
Hi
Is there a better way to do this? Like using an array can I get the values in v_status and v_phase combined? How can I do that?
Quote: | select decode (status_code,'I', 'On_Hold'
,'E', 'Error'
,'C', 'Normal'
,'D','Cancelled'
,'X','Terminated'
,'Q','On_Hold'
,'R', 'Normal'
,'Others') into v_status from fnd_concurrent_requests
where request_id = v_request_id
select decode (phase_code, 'P','Inactive'
,'T', 'Terminated'
,'C','Completed'
,'R','Running') into v_phase from fnd_concurrent_requests
where request_id = v_request_id;
|
and later on I need to check the values of status and phase also.. like
Quote: | if status = 'Completed' and phase = 'Normal'
return (1)
else
return (2)
|
|
|
|
|
Re: PL/SQL combine two columns in one variable [message #302698 is a reply to message #302692] |
Tue, 26 February 2008 12:03   |
Ronald Beck
Messages: 121 Registered: February 2003
|
Senior Member |
|
|
Personally, I'd create a translation table consisting of the status code and it's translation, like
create table xlation_codes as
( theCode varchar2(1),
theXlation varchar2(30)
)
insert into xlation_codes (theCode, theXlation)
values ('E','Error');
etc...
Then, change your code...
select b.theXlation
into v_status
from fnd_concurrent_requests a,
xlation_codes b
where a.request_id = v_request_id
and a.status_code = b.theCode
;
That way, if any of the status codes change or you get new status codes, you update the table, not the procedure. But, that's just me, and I'm not an expert by any means. I'm sure others on this list have other recommendations as well.
Ron
|
|
|
Re: PL/SQL combine two columns in one variable [message #302703 is a reply to message #302697] |
Tue, 26 February 2008 12:17   |
shree_z
Messages: 75 Registered: February 2008
|
Member |
|
|
Thanks a lot Michel,
But here I am referring to a DBMS output and so i want to assingn the values of the columns to variables. And then need to compare them.
Like..
if v_phase = 'Normal' and v_status = 'Completed'
then
return(1)
else
return(2).
Can you please give a specific example to do this?
Thanks in advance.
|
|
|
|