locked
Compound Conditions RRS feed

  • Question

  • Here is another one I am really confused on.  The following question regarding compound conditions.
    Write a SELECT statement that determines whether the PaymentDate column of the Invoices table has any invalid values. To be valid, PaymentDate must be a null value if there's a balance due and a non-null value if there's no balance due. Code a compound condition in the WHERE clause that tests for these conditions.

    Thanks for any help with this.

    Jason Aguilar
    Monday, February 6, 2012 4:03 PM

Answers

  • Try

    where 1 = case when PaymentDate IS NULL and BalanceDue IS NOT NULL then 1 when BalanceDue IS NOT NULL AND PaymentDate IS NULL then 1 else 0 end
    


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Monday, February 6, 2012 4:10 PM
  •  

    to get invalid records you may try this..(The conditions assume null value in balanceDue means there is no balance due)..

     

    Where (PaymentDate IS NOT NULL and BalanceDue IS NOT NULL) or (  PaymentDate IS NULL and BalanceDue IS NULL)



    Vinay Valeti| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer
    Monday, February 6, 2012 4:42 PM
  • The question asked for a compound condition. Also, why use CASE when it is a simple parenthesis?
    Monday, February 6, 2012 6:18 PM
  • I believe both conditions (written as OR or the way I wrote it) should perform the same. If there are many complex conditions, using case based query may be better and easier to write than OR based conditions.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Monday, February 6, 2012 6:20 PM
  • SELECT
    	PaymentDate
    FROM
    	Invoices
    WHERE
    	(Balance > 0 AND PaymentDate IS NULL)
       OR	(Balance <= 0 AND PaymentDate IS NOT NULL)
    
    


    By having two conditions, each in a set of parentheses, both conditions must be true. Looking at it that way, the logic is simple.

    With that said, i would like to point it that this is very basic stuff. We can give you the answers easily, but you would likely have a harder time learning it yourself. Perhaps you can show us what you are thinking and ask for guidance, so you can work it out for yourself. Soon after that, the answer would likely come naturally.


    • Edited by Brian Tkatch Monday, February 6, 2012 6:24 PM
    • Proposed as answer by Gert-Jan Strik Monday, February 6, 2012 6:26 PM
    • Marked as answer by KJian_ Monday, February 13, 2012 6:31 AM
    Monday, February 6, 2012 6:22 PM
  • I'm just theorizing here: problem is, it doesn't tell the optimizer what you are really trying to do.

    Regardless, the point here was "code a compound condition in the WHERE clause that tests for these conditions." You provided a simple clause, with a complex CASE statement.

    Monday, February 6, 2012 6:26 PM
  • I also think I misread the original requirement. I thought that both Payment Date and BalanceDue are the date fields. Looking at your query I think that Balance Due could have been the actual amount.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Monday, February 6, 2012 6:29 PM
  • Heh. And i'm usually the one who misreads things. :)
    Monday, February 6, 2012 6:51 PM

All replies

  • Try

    where 1 = case when PaymentDate IS NULL and BalanceDue IS NOT NULL then 1 when BalanceDue IS NOT NULL AND PaymentDate IS NULL then 1 else 0 end
    


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Monday, February 6, 2012 4:10 PM
  •  

    to get invalid records you may try this..(The conditions assume null value in balanceDue means there is no balance due)..

     

    Where (PaymentDate IS NOT NULL and BalanceDue IS NOT NULL) or (  PaymentDate IS NULL and BalanceDue IS NULL)



    Vinay Valeti| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer
    Monday, February 6, 2012 4:42 PM
  • The question asked for a compound condition. Also, why use CASE when it is a simple parenthesis?
    Monday, February 6, 2012 6:18 PM
  • I believe both conditions (written as OR or the way I wrote it) should perform the same. If there are many complex conditions, using case based query may be better and easier to write than OR based conditions.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Monday, February 6, 2012 6:20 PM
  • SELECT
    	PaymentDate
    FROM
    	Invoices
    WHERE
    	(Balance > 0 AND PaymentDate IS NULL)
       OR	(Balance <= 0 AND PaymentDate IS NOT NULL)
    
    


    By having two conditions, each in a set of parentheses, both conditions must be true. Looking at it that way, the logic is simple.

    With that said, i would like to point it that this is very basic stuff. We can give you the answers easily, but you would likely have a harder time learning it yourself. Perhaps you can show us what you are thinking and ask for guidance, so you can work it out for yourself. Soon after that, the answer would likely come naturally.


    • Edited by Brian Tkatch Monday, February 6, 2012 6:24 PM
    • Proposed as answer by Gert-Jan Strik Monday, February 6, 2012 6:26 PM
    • Marked as answer by KJian_ Monday, February 13, 2012 6:31 AM
    Monday, February 6, 2012 6:22 PM
  • I'm just theorizing here: problem is, it doesn't tell the optimizer what you are really trying to do.

    Regardless, the point here was "code a compound condition in the WHERE clause that tests for these conditions." You provided a simple clause, with a complex CASE statement.

    Monday, February 6, 2012 6:26 PM
  • I also think I misread the original requirement. I thought that both Payment Date and BalanceDue are the date fields. Looking at your query I think that Balance Due could have been the actual amount.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Monday, February 6, 2012 6:29 PM
  • Heh. And i'm usually the one who misreads things. :)
    Monday, February 6, 2012 6:51 PM