This discussion is archived
13 Replies Latest reply: Oct 12, 2012 11:53 AM by Solomon Yakobson RSS

simple query

797013 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    select column_a
      from your_table
    where nvl(column_b, 0) != 0
       and column_c = 'SAM';
  • 4. Re: simple query
    Purvesh K Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    pLEASE tell me with out nvl function

    i need it using case function in where clause
  • 7. Re: simple query
    Purvesh K Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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. Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points