Post reply

Help with SQL statement please!!

  • April 22, 2004 at 9:30 am

    #101977

    Can someone shed some light on this for me?  Each of these conditions in my where clause work individually, however, when I run this entire statement it returns no rows.  Thanks in advance!!

     

    select school_code, grade, NoStudents, FinalSubmit  from spring_2004_data

    where 

     grade='k' and ((NoStudents='N' and FinalSubmit='Y') or NoStudents='Y') and

      grade='1' and ((NoStudents='N' and FinalSubmit='Y') or NoStudents='Y') and

     grade='2' and ((NoStudents='N' and FinalSubmit='Y') or NoStudents='Y') and

     grade='3' and ((NoStudents='N' and FinalSubmit='Y') or NoStudents='Y')

  • April 22, 2004 at 10:03 am

    #503869

    Not sure what you're trying to achieve but don't you really mean this?

    select school_code, grade, NoStudents, FinalSubmit from spring_2004_data

    where

    (grade='k' and ((NoStudents='N' and FinalSubmit='Y') or NoStudents='Y')) or

    (grade='1' and ((NoStudents='N' and FinalSubmit='Y') or NoStudents='Y')) or

    (grade='2' and ((NoStudents='N' and FinalSubmit='Y') or NoStudents='Y')) or

    (grade='3' and ((NoStudents='N' and FinalSubmit='Y') or NoStudents='Y'))

    You won't get nay results from what you are running because the grade column can only have one value at a time i.e it is atomic it cannot be k and 1 and 2 and 3 all at the same time

    Hope this helps

    A rather poorely (feeling sorry for myself)

    Dave

  • April 22, 2004 at 10:22 am

    #503876

    Actually, each row has it's own grade, for example:

     

    School   Grade   NoStudents FinalSubmit

    428         K           N                Y

    428         1           N                Y

    428         2           Y                N

     

  • April 22, 2004 at 3:18 pm

    #503915

    Peg

    That wasn't what I meant. For your query as posted to return a result each Grade entry for each row would need to be K and 1 and 2 and 3 all at the same time which it can't be.

    The predicate's in your where condition are applied to every single row and if a row matches all the conditions in your where clause it will be selected. This is why they work individually but not together.

    I dont' know if what i posted earlier was any use but if you post some sample data and explain what you want to then I'll gladly make a suggestion

    a still poorley (yes still feeeling sorry for myself)

    Dave

  • April 22, 2004 at 11:48 pm

    #503938

    Hi Peg,

    Dave is quit right.

    The statement would be written as

    select school_code, grade, NoStudents, FinalSubmit from spring_2004_data

    where

    (grade='k' OR grade='1' OR grade='2' OR grade='3') AND

    ((NoStudents='N' and FinalSubmit='Y') or NoStudents='Y')

    An easy way of checking this type of query is to copy and paste the query into a view design in Enterprise Manager.

     

    Regards CJ

  • April 23, 2004 at 7:17 am

    #503983

    Thanks for the replies...I got this working!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply