patwhite002 Posted October 21, 2009 Share Posted October 21, 2009 The where statement can become a very trick. It beccome even more fun when your try to create neg. logica. here a EG.In a program I might write: if ( ( Products.Category = 'A' or Products.Category is Null ) or ( not ( Products.Category = 'A' or Products.Category is Null ) and (Invoices_Products.ProdAmount = 0 or Invoices_Products.ProdQty = 0 ) )...In this case the not ( ) make a true answer falus and a falus answer true. Is this valid syntax for SQL? If so is the valid for SQL 92? Link to comment Share on other sites More sharing options...
justsomeguy Posted October 21, 2009 Share Posted October 21, 2009 Yeah it's valid, you can leave out the second part though. This will do the same thing:if ( ( Products.Category = 'A' or Products.Category is Null ) or (Invoices_Products.ProdAmount = 0 or Invoices_Products.ProdQty = 0 ) ) Link to comment Share on other sites More sharing options...
patwhite002 Posted October 21, 2009 Author Share Posted October 21, 2009 Yeah it's valid, you can leave out the second part though. This will do the same thing:if ( ( Products.Category = 'A' or Products.Category is Null ) or (Invoices_Products.ProdAmount = 0 or Invoices_Products.ProdQty = 0 ) )There no way the make the Invoices_Products.ProdAmount and Invoices_Products.ProdQty be zero if the Products.Category is false? It like this, I want a row if all the answer are true but I want a value of zero for the ProdAmount and ProQty if they not the Category group I'm look for, but I want equal number of matching row no matter what group I'm asking for. Link to comment Share on other sites More sharing options...
justsomeguy Posted October 21, 2009 Share Posted October 21, 2009 If you always want the same number of rows you need to use a join and maybe even remove your WHERE conditions (WHERE conditions filter the record set, if you always want every row there's no reason to use WHERE). It may not be the best idea to try to change the values returned in the SQL code, it might be a better idea to have that logic in your application code and just check if the category is the right value. Link to comment Share on other sites More sharing options...
patwhite002 Posted October 22, 2009 Author Share Posted October 22, 2009 The data is going from the SQL to placed on a EXCEL Speadsheet. There are 15 SQL query, 1(one) for each Category of Products sold. This is a large Dr group within the Dr base. Each query goes on a difference tab in the workbook, then there is a combined tab showing the Dr's coming down and the Products Categories going across the top. It importent to each of the query generate the same number of rows. With equal number of row on each tab, it set up the list, set the first column of each Cateroty, copy it down the column and in 30 min it done. Yea I know it would be best if the report was done in crystal report, But even with that I feel I'd still be pull my hair out.Each query is creating a summary total of each Cateroty by Dr. I just need the query to list a zero for Dr with no sales in the category. Link to comment Share on other sites More sharing options...
patwhite002 Posted October 22, 2009 Author Share Posted October 22, 2009 I was thinking about what you said before, now, Im asking myself is the follow statements giving me the same results? Is this because Null and Zero are the same?( Invoices_Products.ProdAmount <> 0 or Invoices_Products.ProdQty <> 0 ) ( ( Invoices_Products.ProdAmount is not Null and Invoices_Products.ProdAmount <> 0 ) or ( Invoices_Products.ProdQty is not Null and Invoices_Products.ProdQty <> 0 ) ) Link to comment Share on other sites More sharing options...
justsomeguy Posted October 22, 2009 Share Posted October 22, 2009 Null and zero are not the same, a null value is not equal to zero and a value of zero is not equal to null. Null is only equal to itself. Link to comment Share on other sites More sharing options...
patwhite002 Posted October 22, 2009 Author Share Posted October 22, 2009 How does SQL define Null. I'm use to Null meaning hex 00. Link to comment Share on other sites More sharing options...
justsomeguy Posted October 22, 2009 Share Posted October 22, 2009 I don't know how it chooses to store a null value, but null is a distinct value just like 0, 1, 2, etc. Null is a special value which means the field has no value. Zero is a value, so null and zero are not the same. Information about how MySQL uses null values here:http://dev.mysql.com/doc/refman/5.0/en/wor...-with-null.html Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.