Geek asks and answers

How to write the condition "x = y" such that it is true when both x and y are NULL?

I am using the following query:

SELECT tblTxn.Currency, tblTxn.Amount, tblTxn.Desc, tblAccounts.Account, tblTxn.Type
FROM tblAccounts
    INNER JOIN tblTxn ON (tblAccounts.Currency = tblTxn.Currency)
WHERE
    tblTxn.Type=tblAccounts.Type OR 
    (tblAccounts.Type Is Null And tblAccounts.Type Is Null);

Here tblTxn.Typeand tblAccounts.Typecan have values ​​or zero values.
I need both. Zero values ​​indicate the expected result, but the values ​​are not.

How to fix it?

I used the query in MS Access 2007. From the solutions below, I adjusted the query as follows:

SELECT tblTxn.Currency, tblTxn.Amount, tblTxn.Desc, tblAccounts.Account, tblTxn.Type FROM tblAccounts INNER JOIN tblTxn ON tblAccounts.Currency = tblTxn.Currency WHERE Nz(tblTxn.Type,0) = Nz(tblAccounts.Type,0)

Shows the expected result. Thanks for the great support. NB. (The Nz function (value, [ValueIfNull]) is an alternative to COALESCE in Access.)

, , WHERE tblTxn.Type=tblAccounts.Type OR Nz(tblTxn.Type,0) = Nz(tblAccounts.Type,0), . , ?

+3
3

IsNull .

. MSDN: ISNULL (Transact-SQL) ( , SQL Server)

, , .

SELECT  tblTxn.Currency, 
    tblTxn.Amount, 
    tblTxn.Desc, 
    tblAccounts.Account, 
    tblTxn.Type 
FROM    tblAccounts 
        INNER JOIN tblTxn ON (tblAccounts.Currency = tblTxn.Currency) 
WHERE   IsNull(tblTxn.Type, '') =IsNull(tblAccounts.Type,'') 
    OR COALESCE(tblAccounts.Type, tblAccounts.Type) Is Null;
+1

tblAccounts.Type Is Null . , tblAccounts.Type Is Null tblTxn.Type Is Null.

:

DECLARE @FakeString VARCHAR(10)
SET @FakeString = '!@#$%^&'

SELECT tblTxn.Currency, tblTxn.Amount, tblTxn.Desc, tblAccounts.Account, tblTxn.Type
FROM tblAccounts 
    INNER JOIN tblTxn ON tblAccounts.Currency = tblTxn.Currency
WHERE 
    ISNULL(tblTxn.Type, @FakeString) = ISNULL(tblAccounts.Type, @FakeString)

, -

DECLARE @FakeType INT
SET @FakeType = -9999
+1
SELECT tblTxn.Currency
, tblTxn.Amount
, tblTxn.Desc
, tblAccounts.Account
, tblTxn.Type
FROM tblAccounts
INNER JOIN tblTxn ON tblAccounts.Currency = tblTxn.Currency
WHERE ISNULL(tblTxn.Type,'') = ISNULL(tblAccounts.Type,'')
--  This works as well
--  WHERE COALESCE(tblTxn.Type,'') = COALESCE(tblAccounts.Type,'')

Using ISNULLand returning an empty string, you can match your NULLs. The danger is that you are more likely to have more matches than this is actually true. But the request provided must match you anyway.

0
source

More articles:


All Articles