6 Replies Latest reply: Jan 10, 2013 11:28 AM by user346369 RSS

    View return a value when return is null

    794727
      I need a view that returns the following situation:
      ID   VAL
      1    A
      2    B
      Select VAL from my_view where ID = (?)
      If the ID is different from 1 or 2 then the value should be "C" else "A" or "B" as ID

      Any tips on how I can do this?

      Regards
        • 1. Re: View return a value when return is null
          rp0428
          .posted in error
          • 2. Re: View return a value when return is null
            Solomon Yakobson
            Assuming id is not null:
            SQL> variable id number
            SQL> exec :id := 1;
            
            PL/SQL procedure successfully completed.
            
            SQL>  select  val
              2     from  tbl
              3     where id = :id
              4  union all
              5   select  'C'
              6     from  dual
              7     where :id not in (
              8                       select  id
              9                         from  tbl
             10                      )
             11  /
            
            V
            -
            A
            
            SQL> exec :id := 2;
            
            PL/SQL procedure successfully completed.
            
            SQL>  select  val
              2     from  tbl
              3     where id = :id
              4  union all
              5   select  'C'
              6     from  dual
              7     where :id not in (
              8                       select  id
              9                         from  tbl
             10                      )
             11  /
            
            V
            -
            B
            
            SQL> exec :id := 3;
            
            PL/SQL procedure successfully completed.
            
            SQL>  select  val
              2     from  tbl
              3     where id = :id
              4  union all
              5   select  'C'
              6     from  dual
              7     where :id not in (
              8                       select  id
              9                         from  tbl
             10                      )
             11  /
            
            V
            -
            C
            
            SQL>
            SY.
            • 3. Re: View return a value when return is null
              ranit B
              Try this... a guess
              select 
                decode(&x, 1, 'A', 2, 'B', 'C') val
              from
                dual;
              • 4. Re: View return a value when return is null
                user503635
                Can also be done in this way
                create table mytest
                (id NUMBER, 
                 val VARCHAR2(10))
                /
                
                insert into mytest values (1, 'A')
                /
                
                insert into mytest values (2, 'B')
                /
                
                select a.val
                  from 
                (select t.id, 
                        t.val
                  from mytest t
                union all 
                select -1, 'C'
                  from dual) a
                 where a.id = NVL((select t1.id
                                 from mytest t1
                                where t1.id = &your_id), -1)
                /
                • 5. Re: View return a value when return is null
                  794727
                  Thank you Solomon! Regards
                  • 6. Re: View return a value when return is null
                    user346369
                    791724 wrote:
                    I need a view that returns the following situation:
                    ID   VAL
                    1    A
                    2    B
                    
                    Select VAL from my_view where ID = (?)
                    If the ID is different from 1 or 2 then the value should be "C" else "A" or "B" as ID
                    I am confused. Why would someone need a view to do that, when normally "C" could be returned when no_data_found is raised?