Question
jaweher89 on Fri, 18 Mar 2016 09:41:36
I have the following query
SELECT top 10 case
when exists (SELECT t1.[Code Article]
FROM [Promotion]
where t1.datecol between
[Date Debut Promo] and [Date Fin Promo])
then 1
else 0
end as test1
, Case when [Code Article] in (SELECT [Code Article]
FROM [Promotion]
where datecol between
[Date Debut Promo] and [Date Fin Promo])then 1 else 0 end as test2
FROM [QlikDataWarehouse].[dbo].[Vente]t1Results
Why I get test1<>test2 ? what is the diffrence between test1 and test2 ? It is not the same ?
Replies
Olaf Helper on Fri, 18 Mar 2016 09:46:41
The sub-query of the second case, does it return a NULL value? Then the IN Operation will always result in "undefined" and your query returns 0.
Dan Guzman on Fri, 18 Mar 2016 10:29:44
The sub-query of the second case, does it return a NULL value? Then the IN Operation will always result in "undefined" and your query returns 0.
Olaf, perhaps you thinking of NOT IN. NOT IN will return unknown rather than true or false when the subquery returns NULL but IN will return only true or false.
Eric__Zhang on Tue, 22 Mar 2016 06:16:07
Hi jaweher89,
Let's put the NOT IN or EXISTS aside, I think your problem is a typo in your query, in the first CASE expression the where condition is "t1.datecol between" and in the second CASE it is "datecol between", they refer to different columns from two tables.
Check a simple demo below.
DECLARE @Vente TABLE([Code Article] varchar(20),datecol date)
DECLARE @Promotion TABLE([Code Article] varchar(20),datecol date,[Date Debut Promo] DATE,[Date Fin Promo] DATE)
INSERT INTO @Vente VALUES('Code1','20150101')
INSERT INTO @Promotion VALUES('Code1','20150601','20150101','20150102')
SELECT TOP 10 CASE
WHEN EXISTS (
SELECT t1.[Code Article]
FROM @Promotion
WHERE t1.datecol BETWEEN [Date Debut Promo]
AND [Date Fin Promo]
)
THEN 1
ELSE 0
END AS test1
,CASE
WHEN [Code Article] IN (
SELECT [Code Article]
FROM @Promotion
WHERE t1.datecol BETWEEN [Date Debut Promo]
AND [Date Fin Promo]
)
THEN 1
ELSE 0
END AS test2
FROM @Vente t1
If you have any feedback on our support, you can click here.
Eric Zhang
TechNet Community Support
Russ Loski on Tue, 22 Mar 2016 08:30:17
In order to confirm or remove Eric's answer from consideration we need the DDL for the tables. Otherwise we have to guess at the source issue.