5 Replies Latest reply: Nov 14, 2012 5:32 AM by BluShadow RSS

    Select data if a value has some value?

    Xavi
      Hello, I'm trying to do this..


      In pseucode it looks like..

      If this field has a value then Select a, b, c, .. from table
      else "do not perform anything".

      How could I do this, I was trying ..

      select decode ( LKP2.EXCLUDED_BOOK_CD, not null,
      ( select 1 from dual),
      'NOTNULL','WHATEVER')
      FROM fcl_chunk_pid_correspondence LKP2
      WHERE LKP2.CHUNK_ID =: p_chunk_id
      AND LKP2.GCDS_ENTITY = 'TRANSACTION'
      AND LKP2.EXCLUDED_BOOK_CD IS NOT NULL
      AND LKP2.UBR = 'ELSE';

      But it doesn't work if there isn't any row for the where.

      Thanks in advance!!
        • 1. Re: Select data if a value has some value?
          BluShadow
          Your question does not make sense.

          Please provide sample data and expected output, and format your code/date as per the FAQ: {message:id=9360002}... as you should already know by now being a long time member of the forums.
          • 2. Re: Select data if a value has some value?
            971895
            write case statement as like

            select case when sal is not null then sal end from emp;
            • 3. Re: Select data if a value has some value?
              BluShadow
              The only thing I can see that's blatently wrong in your code is:
              select decode ( LKP2.EXCLUDED_BOOK_CD, not null, 
              ( select 1 from dual),
              'NOTNULL','WHATEVER') 
              which would be better turned into a CASE statement so you can see the logic properly...
              select case when LKP2.EXCLUDED_BOOK_CD is not null then '1'
                          when LKP2.EXCLUDED_BOOK_CD = 'NOTNULL' then 'WHATEVER'
                     else null
                     end as result
              • 4. Re: Select data if a value has some value?
                Keith Jamieson
                SELECT 
                CASE WHEN DUMMY = 'XY'
                THEN 
                    DUMMY
                ELSE 
                    NULL
                END selectvalue
                from dual
                Returns 'X'
                SELECT 
                CASE WHEN DUMMY = 'X'
                THEN 
                    DUMMY
                ELSE 
                    NULL
                END selectvalue
                from dual
                returns Null

                Edited by: Keith Jamieson on Nov 14, 2012 11:27 AM
                • 5. Re: Select data if a value has some value?
                  BluShadow
                  Keith Jamieson wrote:
                  SELECT 
                  CASE WHEN DUMMY = 'XY'
                  THEN 
                  DUMMY
                  ELSE 
                  NULL
                  END selectvalue
                  from dual
                  Returns 'X'
                  SELECT 
                  CASE WHEN DUMMY = 'X'
                  THEN 
                  DUMMY
                  ELSE 
                  NULL
                  END selectvalue
                  from dual
                  returns Null

                  Edited by: Keith Jamieson on Nov 14, 2012 11:27 AM
                  Or the other way around perhaps?
                  SQL> SELECT
                    2  CASE WHEN DUMMY = 'X'
                    3  THEN
                    4      DUMMY
                    5  ELSE
                    6      NULL
                    7  END selectvalue
                    8  from dual
                    9
                  SQL> /
                  
                  S
                  -
                  X
                  
                  SQL> SELECT
                    2  CASE WHEN DUMMY = 'XY'
                    3  THEN
                    4      DUMMY
                    5  ELSE
                    6      NULL
                    7  END selectvalue
                    8  from dual
                    9
                  SQL> /
                  
                  S
                  -