Select statement in DECODE

[kkpareek]

Hi,

Can the gurus tell me how I can use select statemen in decode. Following is a part of create view in Informix and I want to do the same in Oracle view creation.

CASE WHEN EXISTS (
SELECT *
FROM qsd_account
WHERE account_num = lo.cust_num
)
THEN "Y"
ELSE "N"
END,

I am trying something like this & it does not work

create or replace view v1 (tname,fld) as
select table_name,(decode(select nvl(num_rows,0) from user_tables
where table_name= t.table_name,0,'N','Y')) from ods.ods_tables t
/

[kmesser]

select table_name, decode(u.table_name,NULL,'N','Y') from user_tables u, ods.ods_tables t where t.table_name=u.table_name(+);

[kkpareek]

HI,

Sorry for the second part. My main question is about assigning a value to a view column depending on the outcome of select statement. This is possible in informix. I want to do similar Oracle.

[kkpareek]

Hi,

I just did what informix was doing and it works in oracle too. The view below was created.

create or replace view v1 (tname,fld) as

select table_name,case when exists (select * from user_tables
where table_name= t.table_name) then 'Y' else 'N' end
from ods.ods_tables t

[sasmita]

Hello

If you want to use select statement in decode function

Given one example below.

Hope this will help you.

create table emp
(empno number,
ename varchar2(30),
status varchar2(10)
)
create or replace view empview(empno,ename,status,result)
as
select empno,ename,status,
decode((select status from emp a
where empno = b.empno),
'Male','Y',
'Female','N') result
from emp b

[kmesser]

select in decode requires 8i