Issue with ALL Keyword
-
How to use ALL keyword in SQL Server
Following is the script I had run
CREATE TABLE Catalog
(
CarName VARCHAR(10)
,CarType VARCHAR(10)
)
GO
INSERT INTO Catalog
VALUES ('Ford','Diesel')
GO
INSERT INTO Catalog
VALUES ('Ford','Petrol')
Go
INSERT INTO Catalog
VALUES ('Skoda','Diesel')
Go
DECLARE @int INT
EXEC sp_xml_preparedocument @int OUTPUT, '<CarType><Type>Diesel</Type><Type>Petrol</Type></CarType>'
SELECT Carname FROM Catalog
WHERE CarType = ALL (
Select Type
FROM OPENXML (@int, '/CarType/Type', 2)
WITH (Type VARHCAR(20) '.') )
EXEC sp_xml_removedocument @int
Actually this should return the value ford which has ALL the values given in xml. But its not returning anything. What might be the problem.
-
I just spent a little time playing with your query, and it is actually working the way it is supposed to work. If you read BOL you will find that using the = in the query is actually evaluating to false.
Here is the excerpt from BOL:
ALL requires the scalar_expression to compare positively to every value that is returned by the subquery. For instance, if the subquery returns values of 2 and 3, scalar_expression <= ALL (subquery) would evaluate as TRUE for a scalar_expression of 2. If the subquery returns values of 2 and 3, scalar_expression = ALL (subquery) would evaluate as FALSE, because some of the values of the subquery (the value of 3) would not meet the criteria of the expression.
For statements that require the scalar_expression to compare positively to only one value that is returned by the subquery, see SOME | ANY (Transact-SQL).
This topic refers to ALL when it is used with a subquery. ALL can also be used with UNION and SELECT.
-
I played with it a little more, if you change the = to >= you will get the results you are expecting.
-
>>>>
DECLARE @int INT
EXEC sp_xml_preparedocument @int OUTPUT, 'DieselPetrol'
SELECT Carname FROM Catalog
WHERE CarType = ALL (
Select Type
FROM OPENXML (@int, '/CarType/Type', 2)
WITH (Type VARHCAR(20) '.') )
EXEC sp_xml_removedocument @int
<<<<
Is there such a car type that is Diesel AND Petrol ?
Sorry for my ignorance but shouldn't it be one OR the other ?
If the answer is *OR* then change "= ALL" to "IN"
Cheers,
* Noel -
Based on the OP, the question being asked is which car has both diesel and Petrol types. The Skoda has only diesel, so fails the criteria where as the ford satisfies both and is selected. If = ALL() [should be >= ALL to work as required) is changed to IN (), then all three would be returned. Same would happen if the = ALL() were changed to = ANY () or = SOME ().
Interesting way to create a query, but I am not sure of a situation where I might use this, but it is good to know it exists.
-
Thank You!!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply