SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Analysis Server and Reporting Services (2008)
 How does -1 work in SQL Statement?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

viperbyte
Posting Yak Master

USA
132 Posts

Posted - 06/26/2013 :  09:02:32  Show Profile  Reply with Quote
Good morning all,

In query designer if I supply negative one when prompted with the following code I get all categories. But when I run the code in regular SQL Server Management stuido in the query designer by removing the categorID parts and hard coding -1 in the where clause I get 0 records.

How is this -1 being translated to mean ALL in the Reporting Tool's Query Designer? Or am I missing some fundemental SQL understanding?

SELECT
Production.ProductCategory.Name AS Category
,Production.ProductSubcategory.Name AS SubCategory
,Production.Product.Name AS Product
,Production.Product.Color
,Production.Product.ListPrice
FROM
Production.Product INNER JOIN Production.ProductSubcategory ON
Production.Product.ProductSubCategoryID=
Production.ProductSubcategory.ProductSubcategoryID
INNER JOIN Production.ProductCategory ON
Production.ProductSubCategory.ProductCategoryID=
Production.ProductCategory.ProductCategoryID
WHERE
ProductCategory.ProductCategoryID = @CategoryID OR @CategoryID = -1
ORDER BY
Category, SubCategory, Product

James K
Flowing Fount of Yak Knowledge

3707 Posts

Posted - 06/26/2013 :  09:05:27  Show Profile  Reply with Quote
SQL Server might be getting a NULL, although I don't know which step along the way causes that to happen. You can test that theory by replacing the "@CategoryID = -1" with "@CategoryID IS NULL" and running the query in SSMS.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/26/2013 :  09:48:37  Show Profile  Reply with Quote
I think you've set -1 as value for CategoryID in Reporting Tool's Query Designer prompt which is causing it to bypass the filter due to OR condition and give you full categories data

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

viperbyte
Posting Yak Master

USA
132 Posts

Posted - 06/26/2013 :  09:50:50  Show Profile  Reply with Quote
Still 0 records after the experiment with the following code. I'd really like to know how this -1 is working cuz I like knowing what I'm doing and just because the books says do this and all records will display. As far as I know this behaviour is undocumented. But that can't be right.

SELECT
Production.ProductCategory.Name AS Category
,Production.ProductSubcategory.Name AS SubCategory
,Production.Product.Name AS Product
,Production.Product.Color
,Production.Product.ListPrice
FROM
Production.Product INNER JOIN Production.ProductSubcategory ON
Production.Product.ProductSubCategoryID=
Production.ProductSubcategory.ProductSubcategoryID
INNER JOIN Production.ProductCategory ON
Production.ProductSubCategory.ProductCategoryID=
Production.ProductCategory.ProductCategoryID
WHERE
ProductCategory.ProductCategoryID IS NULL
ORDER BY
Category, SubCategory, Product
Go to Top of Page

viperbyte
Posting Yak Master

USA
132 Posts

Posted - 06/26/2013 :  09:53:25  Show Profile  Reply with Quote
Visakh6,

yes in the desinger I have this code:

SELECT -1 AS ProductCategoryID, '(All Categories)' AS NAME
FROM Production.ProductCategory

UNION

SELECT ProductCategoryID, Name
FROM Production.ProductCategory
ORDER BY NAME


But I don't see the connection on how -1 means All.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/26/2013 :  09:57:51  Show Profile  Reply with Quote
See this where condition

WHERE 
ProductCategory.ProductCategoryID = @CategoryID OR @CategoryID = -1


when -1 is passed as value the blue part becomes true so it will ignore the other part which is where the filteration happens
so it literally bypasses filter and bring you full result set

see this to understand this method

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

viperbyte
Posting Yak Master

USA
132 Posts

Posted - 06/26/2013 :  10:12:19  Show Profile  Reply with Quote
Oh, ok,

I tried doing this:
WHERE
ProductCategory.ProductCategoryID = @CategoryID OR TRUE

But that's not legit SQL. So basicaly so if that type of code would be supported then it should work I suppose. I didn't know that Where clauses ultimately ended up be translated as where true or false.

Thanks for the scoop.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/26/2013 :  10:18:45  Show Profile  Reply with Quote
quote:
Originally posted by viperbyte

Oh, ok,

I tried doing this:
WHERE
ProductCategory.ProductCategoryID = @CategoryID OR TRUE

But that's not legit SQL. So basicaly so if that type of code would be supported then it should work I suppose. I didn't know that Where clauses ultimately ended up be translated as where true or false.

Thanks for the scoop.



you're welcome

the above where is not valid as you need a condition on either sides not just a boolean value

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000