Jump to content

Where Conditions


patwhite002
 Share

Recommended Posts

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

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. Edited by Pat Whtie
Link to comment
Share on other sites

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

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

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

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

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...