Results 1 to 6 of 6

Thread: SQL...9i

  1. #1
    Join Date
    Aug 2008
    Posts
    464

    Unanswered: SQL...9i

    Hi guys

    Hopefully this is very simple for you...

    I have a table with 4 columns (type number)

    In my output I want to exclude all those rows where all the columns are zero for any given datetime.

    Will appreciate some help.

    Regards
    Sheraz Chowdry

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    I'm not sure I understand what you mean with "any given datetime value", but to me your question sounds like the following would return what you want
    Code:
    WHERE col1 = 0 
      AND col2 = 0 
      AND col3 = 0 
      AND col4 = 0
      AND datetime_column = to_date('2010-03-07', 'yyyy-mm-dd')
    If this does work as you expect it, then follow the golden posting rule and:

    1) Post DDL for the table
    2) Post sample data as INSERT statement
    3) Post the desired output

  3. #3
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by shajju View Post
    I have a table with 4 columns (type number)

    In my output I want to exclude all those rows where all the columns are zero for any given datetime.
    What is the relationship between "given datetime" and "4 columns (type number)"? Sorry for my ignorace, I see none. But there probably is some, otherwise the resultset would be the same regardless "given datetime".

    Anyway, it is really simple to answer with the amount of information you gave. Use appropriate WHERE condition. Have a look at AND logical condition.

    [Edit: question mark]

  4. #4
    Join Date
    Aug 2008
    Posts
    464
    Thanks for the replies guys....Seems like I really didn't describe my concern well...

    OK here goes:

    I want to select all those rows where:

    Code:
    where datetime BETWEEN trunc(sysdate)-1 and trunc(sysdate)-1/86400 and COL1<>0 
    
    or datetime BETWEEN trunc(sysdate)-1 and trunc(sysdate)-1/86400 and COL2<>0 
    
    or datetime BETWEEN trunc(sysdate)-1 and trunc(sysdate)-1/86400 and COL3<>0 
    
    or datetime BETWEEN trunc(sysdate)-1 and trunc(sysdate)-1/86400 and COL4<>0
    This looks like a poor query and could do with some efficient code.

    Something like:

    Code:
    where datetime BETWEEN trunc(sysdate)-1 and trunc(sysdate)-1/86400 and (SF<>0 OR ES<>0 OR SES<>0 OR UAS<>0)
    Thanks
    Last edited by shajju; 03-07-10 at 04:32.

  5. #5
    Join Date
    Aug 2008
    Posts
    464
    Looks like I've answered my own question

  6. #6
    Join Date
    Aug 2009
    Posts
    262
    where datetime BETWEEN trunc(sysdate)-1 and trunc(sysdate)-1/86400 and (SF<>0 OR ES<>0 OR SES<>0 OR UAS<>0)


    pardon me if i am wrong , doesnt this AND < > ( or <> or <> or <> ) condition allows that any 1 column can be matched against the 1st part as TRUE ?

Posting Permissions

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