Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL combine two columns in one variable
PL/SQL combine two columns in one variable [message #302692] Tue, 26 February 2008 11:31 Go to next message
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 #302697 is a reply to message #302692] Tue, 26 February 2008 12:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
A select can return more than one column.
A select expression can also contains CASE that can calculate an AND.

Regards
Michel

[Updated on: Tue, 26 February 2008 12:04]

Report message to a moderator

Re: PL/SQL combine two columns in one variable [message #302698 is a reply to message #302692] Tue, 26 February 2008 12:03 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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.
Re: PL/SQL combine two columns in one variable [message #302709 is a reply to message #302703] Tue, 26 February 2008 12:51 Go to previous message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
select
case when status="a value" and phase="another value" then 1 else 2 end
from something
where what i want

Regards
Michel

Previous Topic: difference between decode Vs case...when
Next Topic: URGENT:Problem with API call in stored procedure
Goto Forum:
  


Current Time: Sat Feb 15 12:19:42 CST 2025