Select statement in DECODE
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Select statement in DECODE

  1. #1
    Join Date
    Feb 2001
    Posts
    75

    Thumbs down

    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
    /

  2. #2
    Join Date
    Aug 2000
    Posts
    462
    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(+);

  3. #3
    Join Date
    Feb 2001
    Posts
    75

    Select statement in decode

    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.

  4. #4
    Join Date
    Feb 2001
    Posts
    75

    Select statement in decode

    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


  5. #5
    Join Date
    Apr 2001
    Posts
    24
    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

  6. #6
    Join Date
    Aug 2000
    Posts
    462
    select in decode requires 8i

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width