Home » SQL & PL/SQL » SQL & PL/SQL » SELECT within DECODE
SELECT within DECODE [message #258111] Fri, 10 August 2007 05:11 Go to next message
priyanka.d
Messages: 20
Registered: May 2007
Junior Member
CAN SELECT BE USED WITHIN A DECODE STATEMENT?

SUPPOSE I HAVE A TABLE
CREATE TABLE priyanka(order_id varchar2(1),position_cd varchar2(1),rvrs_cd varchar2(1));


I WANT TO USE A DECODE STATEMENT WHEREIN FOR A 0(ZERO) VALUE OF COUNT(*) A STRING IS RETURNED AND FOR ANY OTHER NON-ZERO VALUE THE position_cd IS TO BE RETURNED CORRESPONDING TO THAT order_id.

THE SIMPLE FORM FOR DECODE WOULD BE :

select decode(count(*),0,'Not in PTS','Exists')
from PRIYANKA 
where position_cd='O' 
and rvrs_cd='O';


I WANT TO REPLACE THE 'Exists' IN THE DECODE WITH THE FOLLOWING STATEMENT
select position_cd from priyanka where order_id='xyz';


can it be done using DECODE?
IF NOT THEN WHAT IS THE SOLUTION IN THAT CASE?
Re: SELECT within DECODE [message #258116 is a reply to message #258111] Fri, 10 August 2007 05:33 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Well, what happened when you tried it?

Things like that work for me:
create table decode_test (col_1  varchar2(10), col_2 varchar2(10));

insert into decode_test values ('A','B');
Insert into decode_test values ('A','A');
insert into decode_test values ('B','C');
insert into decode_test values ('C','D');
insert into decode_test values ('C','B');

select col_1
      ,decode(col_1,'A',(select to_char(count(*)) 
                         from decode_test 
                         where col_2 = 'B'),col_1) decode_select
from   decode_test;

COL_1   DECODE_SELECT
--------------------------------
A	2
A	2
B	B
C	C
C	C
Re: SELECT within DECODE [message #258117 is a reply to message #258116] Fri, 10 August 2007 05:34 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Oh yes - Take your CAPS Lock Off.
Re: SELECT within DECODE [message #258122 is a reply to message #258117] Fri, 10 August 2007 05:47 Go to previous messageGo to next message
priyanka.d
Messages: 20
Registered: May 2007
Junior Member
when i write the decode as such :

select decode(count(*),0,'Not in PTS',
                    (select position_cd 
                     from PRIYANKA 
                     where order_id='1')) DECODE
from PRIYANKA; 


it shows error as "not a single group function" highlighting position_cd.

suppose the data in the table is as follows:

INSERT INTO PRIYANKA 
( ORDER_ID, POSITION_CD, RVRS_CD ) VALUES 
('1', '2', '3'); 
INSERT INTO PRIYANKA ( ORDER_ID, POSITION_CD, RVRS_CD ) VALUES ( '2', '3', '4'); 
COMMIT;

[Updated on: Fri, 10 August 2007 05:49]

Report message to a moderator

Re: SELECT within DECODE [message #258132 is a reply to message #258122] Fri, 10 August 2007 06:24 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Try sonething like this
select decode(cnt,0,'Nope',(select count(*) from decode_test where col_2 = 'B'))
from (
select count(*) cnt
from   decode_test);
Re: SELECT within DECODE [message #258160 is a reply to message #258132] Fri, 10 August 2007 07:23 Go to previous message
priyanka.d
Messages: 20
Registered: May 2007
Junior Member
Hey Thanks.It works.

The new querry looks like:

select decode(cnt,0,'Not in PTS',(select position_cd from PRIYANKA where order_id='1')) DECODE
from (select count(*) cnt
       from priyanka
	   where position_cd='O' 
       and rvrs_cd='O');
Previous Topic: v$tempseg_usage
Next Topic: Create Procedure
Goto Forum:
  


Current Time: Mon Dec 05 05:07:41 CST 2016

Total time taken to generate the page: 0.17089 seconds