13 Replies Latest reply: Oct 12, 2012 1:53 PM by Solomon Yakobson RSS

    simple query

    797013
      I need to take record from the column A
      Condition COLUMN B should not be null or zero
      and column C should be 'SAM'
        • 1. Re: simple query
          jeneesh
          case when nvl(b,0) = 0 and c='SAM' then a else null end
          
          
          or where condition 
          
          where nvl(b,0) = 0 and c='SAM' 
          Please Provide sample data (With CRETAE table and INSERT statements) and expected output.

          Use {noformat}
          {noformat} tags for formatting your codes..
          
          {message:id=9360002}
          It is sad that you are not doing this even after 130 posts...                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
          • 2. Re: simple query
            HuaMin Chen
            oraclehema wrote:
            I need to take record from the column A
            Condition COLUMN B should not be null or zero
            and column C should be 'SAM'
            Try
            select col_a
            from tab1
            where nvl(col_b,0)!=0
            and col_c='SAM';

            Edited by: HuaMin Chen on Oct 12, 2012 3:20 PM
            • 3. Re: simple query
              Purvesh K
              select column_a
                from your_table
              where nvl(column_b, 0) != 0
                 and column_c = 'SAM';
              • 4. Re: simple query
                Purvesh K
                jeneesh wrote:

                where nvl(b,0) = 0 and c='SAM'
                Since OP mentions, column B should not be NULL or 0, I think it should be
                 where nvl(b,0) != 0 and c='SAM' 
                You probably missed the Not.
                • 5. Re: simple query
                  jeneesh
                  Purvesh K wrote:
                  jeneesh wrote:

                  where nvl(b,0) = 0 and c='SAM'
                  Since OP mentions, column B should not be NULL or 0, I think it should be
                  where nvl(b,0) != 0 and c='SAM' 
                  You probably missed the Not.
                  I did notice my mistake after seeing HuaMin Chens post... Did not edit as the correct answer is already there..
                  • 6. Re: simple query
                    797013
                    pLEASE tell me with out nvl function

                    i need it using case function in where clause
                    • 7. Re: simple query
                      Purvesh K
                      oraclehema wrote:
                      pLEASE tell me with out nvl function

                      i need it using case function in where clause
                      What stops you from using NVL in where clause? Logically, you will be achieving the same result with the Case statement.


                      Anyways, this should help you.
                      where column_b is not null and column_b != 0;
                      • 8. Re: simple query
                        797013
                        again u r all making mistakes

                        if u include the column C what will come?
                        where (column B is null  or  column b <>0)  and column c =AB
                        or
                        where column B is null  AND   column b <>0  and column c =AB
                        Pls ans

                        S

                        Edited by: oraclehema on 12 Oct, 2012 3:43 AM

                        Edited by: oraclehema on 12 Oct, 2012 3:44 AM
                        • 9. Re: simple query
                          Purvesh K
                          oraclehema wrote:
                          again u r all making mistakes

                          if u include the column C what will come?
                          Pls ans
                          I never knew it was a job to Spoon feed the OP.

                          Anyways,
                          select column_a
                            from your_table
                          where ( column_b is not null and column_b != 0)
                             and column_c = 'SAM';
                          I hope that this solves your problem and you TRY before you post the next time.
                          • 10. Re: simple query
                            Sven W.
                            oraclehema wrote:
                            pLEASE tell me with out nvl function

                            i need it using case function in where clause
                            There is never a logical need to use case in a where clause.
                            In extremly rare cases there could be a performance effect when you have a function based index in place that also uses case.

                            Otherwise case in where = wrong.
                            • 11. Re: simple query
                              Frank Kulash
                              Hi,
                              Purvesh K wrote:
                              ... Since OP mentions, column B should not be NULL or 0, I think it should be
                              where nvl(b,0) != 0 and c='SAM' 
                              No need for NVL; you can just say
                              WHERE   b != 0
                              AND     c  = 'SAM'
                              If b is NULL, the condition won't be TRUE.
                              • 12. Re: simple query
                                967829
                                I need to take record from the column A
                                Condition COLUMN B should not be null or zero
                                and column C should be 'SAM'
                                Maybe OP wants a very simplified "spelled-out" version? It's probably better to use the nvl function the other users have provided you, but if you're really opposed to it for some reason...
                                SELECT A
                                FROM your_table 
                                WHERE
                                B is not null
                                AND B!=0
                                AND C = 'SAM';
                                Edited by: 964826 on Oct 12, 2012 11:41 AM
                                • 13. Re: simple query
                                  Solomon Yakobson
                                  964826 wrote:
                                  WHERE
                                  B is not null
                                  AND B!=0
                                  ??? Do you think that the above is any different from
                                  WHERE B!=0
                                  B!=0 simply can't result in TRUE if B is null. So condition B is not null makes no difference.

                                  SY.