Could someone explain to me what's the difference between the two queries below?The second one always returns 0, while the first one returns the correct count.
SyntaxEditor Code Snippet
SELECT COUNT({Item}.[Id]) FROM {Item} WHERE {Item}.[Id] NOT IN (SELECT {WarehouseAreaAisleSideSlot}.[ItemId] FROM {WarehouseAreaAisleSideSlot} WHERE {WarehouseAreaAisleSideSlot}.[ItemId] != 0 )
SELECT COUNT({Item}.[Id]) FROM {Item} WHERE {Item}.[Id] NOT IN (SELECT {WarehouseAreaAisleSideSlot}.[ItemId] FROM {WarehouseAreaAisleSideSlot} )
My bet it is the where clause in de inner select ;)
Daniël Kuhlmann wrote:
This only changes that I won't have a 0 in the list. Why does this affect the result?
Does the second versions inner select have a null value in the result for ItemId?
Hi Daniel.
Only zeros and greater numbers.
Sorry about my first post, couldn't resist having some fun
Hi Denis,
What does this return with your dataset?
SELECT COUNT({Item}.[Id]) FROM {Item} WHERE {Item}.[Id] NOT IN (SELECT {WarehouseAreaAisleSideSlot}.[ItemId] FROM {WarehouseAreaAisleSideSlot} WHERE {WarehouseAreaAisleSideSlot}.[ItemId] IS NOT NULL )
Afonso Carvalho wrote:
Hi Afonso.
It works:
Hi,
https://www.red-gate.com/hub/product-learning/sql-prompt/consider-using-not-exists-instead-not-subquery
contains the following statement about NOT IN:
you need to be cautious when using the NOT IN operator if the subquery’s source data contains NULL values. If so, you should consider using a NOT EXISTS operator instead of NOT IN, or recast the statement as a left outer join.
Maybe this relates to your problem.
Regards,
Daniel
It seems that this is the situation.My question now is: why is it considering zero as null?
I don't no, sorry
https://www.outsystems.com/forums/discussion/41593/convert-to-from-null-value-in-database/
Financial Freedom wrote:
Considering what Eduardo said below, it doesn't make sense to happen this "https://www.red-gate.com/hub/product-learning/sql-prompt/consider-using-not-exists-instead-not-subquery" as we don't have null values in OS.
Eduardo Jauch
"Hi Box,
In OutSystems, the value will never be NULL, even if when you save the data in the external entity, it saves NULL.Inn OutSystems, an integer that in DB is NULL will be read as 0, a NULL string as an empty string "", a NULL boolean as False, and so on.
Cheers.
EDIT. By the way, I don't think the extension is able to make a conversion FROM text to DECIMAL, implicitly (never tried). So, It seems to me that the problem of not saving -99999999 can be the fact that you are defining a default value as text when the original data type is decimal..."