Why i am getting nulls as result in query? (1 Viewer)

jaryszek

Registered User.
Local time
Today, 07:49
Joined
Aug 25, 2016
Messages
756
Hi,

i would like to check corresponding fields from 2 seperated tables:

tblVolumeNames
VolumeNameID VolumeName
1 Vol1
2 Vol2
3 Null (blank)
4 Vol3


Temp_VolumeChanges
ID VolumeName Name
1 Vol1 Paul
2 Null (blank) Jacek
3 Null (blank) John

I want to check which volumeName is present in Temp_VolumeChanges and is NOT present in tblVolumeNames table.

So i created sql like here:
Code:
SELECT t2.VolumeName
FROM Temp_VolumeChanges AS t2 LEFT JOIN tblVolumeNames AS t1 ON Nz(t2.VolumeName,"Null") = Nz(t1.VolumeName,"Null")
WHERE t1.VolumeName) Is Null;

This result should show nothing (because null exists also in tblVolumeNames) but i am getting:


Why is that?
What can i do to avoid this?

Please help,
Jacek
 

Attachments

  • Screenshot_22.png
    Screenshot_22.png
    54 KB · Views: 337
  • Database24.accdb
    412 KB · Views: 315

Gasman

Enthusiastic Amateur
Local time
Today, 14:49
Joined
Sep 21, 2011
Messages
14,054
Because yoiu are setting Null to "Null"? :confused:
 

isladogs

MVP / VIP
Local time
Today, 14:49
Joined
Jan 14, 2017
Messages
18,186
Null is never equal to anything not even another null.
You cannot assign a string value "Null" in your Nz function.
That isn't a null string.

Try changing you query to design view. Its not allowed.
 

jaryszek

Registered User.
Local time
Today, 07:49
Joined
Aug 25, 2016
Messages
756
Yes this is true.

But Nz Function is returning any string if Variant field is null:

You can use the Nz function to return zero, a zero-length string (" "), or another specified value when a Variant is Null. For example, you can use this function to convert a Null value to another value and prevent it from propagating through an expression


In this case it means when i will create select query and use Nz function i will get Null there as string (as Nz result)
And this string i want to join.

Best,
Jacek
 

jaryszek

Registered User.
Local time
Today, 07:49
Joined
Aug 25, 2016
Messages
756
BTW.

I am using this sql also:

Code:
SELECT DISTINCTROW t2.* 
FROM   tblvolumes AS t2 
       LEFT JOIN qryim_sourcevolumes AS t1 
              ON ( Nz(t2.linuxmountpassno, "null") = 
                   Nz(t1.linuxmountpassno, "null") ) 
                 AND ( Nz(t2.linuxmountfreq, "null") = 
                       Nz(t1.linuxmountfreq, "null") ) 
                 AND 
       ( Nz(t2.mountoptions, "null") = Nz(t1.mountoptions, "null") ) 
                 AND ( Nz(t2.lvmstripesize, "null") = 
                       Nz(t1.lvmstripesize, "null") ) 
                 AND ( Nz(t2.volumegroup, "null") = Nz(t1.volumegroup, "null") ) 
                 AND ( Nz(t2.providerid, "null") = Nz(t1.providerid, "null") ) 
                 AND ( Nz(t2.volumeid, "null") = Nz(t1.volumeid, "null") ) 
                 AND ( Nz(t2.earlymount, "null") = Nz(t1.earlymount, "null") ) 
                 AND 
       ( Nz(t2.servicelevel, "null") = Nz(t1.servicelevel, "null") ) 
                 AND ( Nz(t2.providerfulfillment, "null") = 
                       Nz(t1.providerfulfillment, "null") ) 
                 AND ( Nz(t2.consumerfulfillment, "null") = 
                       Nz(t1.consumerfulfillment, "null") ) 
                 AND ( Nz(t2.writeacceleratorenabled, "null") = 
                       Nz(t1.writeacceleratorenabled, "null") ) 
                 AND ( Nz(t2.caching, "null") = Nz(t1.caching, "null") ) 
                 AND ( Nz(t2.function, "null") = Nz(t1.function, "null") ) 
                 AND ( Nz(t2.description, "null") = Nz(t1.description, "null") ) 
                 AND ( Nz(t2.formatcommand, "null") = 
                       Nz(t1.formatcommand, "null") ) 
                 AND ( Nz(t2.sharemode, "null") = Nz(t1.sharemode, "null") ) 
                 AND 
                 ( Nz(t2.percentsnapshotspace, "null") = 
                   Nz(t1.percentsnapshotspace, "null") ) 
                 AND ( Nz(t2.filesystemtype, "null") = 
                       Nz(t1.filesystemtype, "null") ) 
                 AND ( Nz(t2.disknumber, "null") = Nz(t1.disknumber, "null") ) 
                 AND ( Nz(t2.physicaldiskmodelidfk, "null") = 
                       Nz(t1.physicaldiskmodelidfk, "null") ) 
                 AND ( Nz(t2.tierid, "null") = Nz(t1.tierid, "null") ) 
                 AND ( Nz(t2.volumenameidfk, "null") = 
                       Nz(t1.volumenameidfk, "null") ) 
                 AND ( Nz(t2.environmentid, "null") = 
                       Nz(t1.environmentid, "null") ) 
                 AND ( Nz(t2.ordinal, "null") = Nz(t1.ordinal, "null") ) 
WHERE  t1.ordinal IS NULL 
       AND t1.environmentid IS NULL 
       AND t1.volumenameidfk IS NULL 
       AND t1.tierid IS NULL 
       AND t1.physicaldiskmodelidfk IS NULL 
       AND t1.disknumber IS NULL 
       AND t1.filesystemtype IS NULL 
       AND t1.percentsnapshotspace IS NULL 
       AND t1.sharemode IS NULL 
       AND t1.subjectcomponent IS NULL 
       AND t1.formatcommand IS NULL 
       AND t1.description IS NULL 
       AND t1.function IS NULL 
       AND t1.caching IS NULL 
       AND t1.writeacceleratorenabled IS NULL 
       AND t1.consumerfulfillment IS NULL 
       AND t1.providerfulfillment IS NULL 
       AND t1.servicelevel IS NULL 
       AND t1.earlymount IS NULL 
       AND t1.volumeid IS NULL 
       AND t1.providerid IS NULL 
       AND t1.volumegroup IS NULL 
       AND t1.lvmstripesize IS NULL 
       AND t1.mountoptions IS NULL 
       AND t1.linuxmountfreq IS NULL 
       AND t1.linuxmountpassno IS NULL;

and have fields with nulls and this is working like a charm and the strange thing is that in the first case this is not working...

Jacek
 

jaryszek

Registered User.
Local time
Today, 07:49
Joined
Aug 25, 2016
Messages
756
I have just replaced statement like this:

Code:
( t2.percentsnapshotspace = t1.percentsnapshotspace 
                    OR ( t2.percentsnapshotspace IS NULL 
                         AND t1.percentsnapshotspace IS NULL ) )

with statement like this:

Code:
 Nz(t2.percentsnapshotspace,"NULL") = Nz(t1.percentsnapshotspace,"NULL")
 

isladogs

MVP / VIP
Local time
Today, 14:49
Joined
Jan 14, 2017
Messages
18,186
In your query SQL you are specifying a Null value for one field (in RED) and trying to join that same field to another field (in BLUE). Therefore you are trying to check a situation where null=null ...which is meaningless/impossible

Code:
SELECT t2.VolumeName
FROM Temp_VolumeChanges AS t2 LEFT JOIN tblVolumeNames AS t1 ON [B][COLOR="Blue"]Nz(t2.VolumeName,"Null") = Nz(t1.VolumeName,"Null"[/COLOR])[/B]
WHERE [B][COLOR="DarkRed"]t1.VolumeName) Is Null[/COLOR][/B];

Its a totally meaningless query
 

jaryszek

Registered User.
Local time
Today, 07:49
Joined
Aug 25, 2016
Messages
756
Hmm thank you, i do not udenrstand it.

This is a left join. If in one table i have "Null" and in second table i have "Null" it should check that in first table there is "Null" in second is also "Null" so we do not matching fields. So table1 has the same values as in table2...So if there is no match i should get true null as result and this is why i used whre clause...

So how can i write this query?
 

jaryszek

Registered User.
Local time
Today, 07:49
Joined
Aug 25, 2016
Messages
756
and Colin so why my big sql (the same mechanism) is working?
I checked twice. If i am deleting something from Temp table and want to see all fields which should be deleted from database table - i have empty field now- i see query result with 1 row. So this is working.
Why?

Jacek
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 14:49
Joined
Jan 14, 2017
Messages
18,186
I'm not totally clear why result you are expecting
This will give ID=3 as the Volume Name is null in both tables

Code:
SELECT Temp_VolumeChanges.ID, Temp_VolumeChanges.VolumeName, tblVolumeNames.VolumeName
FROM Temp_VolumeChanges INNER JOIN tblVolumeNames ON Temp_VolumeChanges.ID = tblVolumeNames.VolumeNameID
WHERE (((Temp_VolumeChanges.VolumeName) Is Null) AND ((tblVolumeNames.VolumeName) Is Null));

If that's not the required result then you will need to specify what output you would expect. Alternatively go back to your lengthy 17 join thread and study that again including the approach suggested by MajP

You big sql is far too long to go through. Perhaps it gives a result but only using part of the criteria specified?
 

jaryszek

Registered User.
Local time
Today, 07:49
Joined
Aug 25, 2016
Messages
756
Hi,

thank you very much !

I investigated further and my SQL do not have sense indeed.

But issue occurs only in this specific situation.
I just retrive ID field (not all table) and it is working.

If i will take my long SQL it will work like a charm - to reproduce situation to get nulls in big sql all fields after WHERE clause should be nulls and have corresponding nulls in 2 tables. Only then i would get not expected results. In my case this is safe.

For others you can use MajP method with VBA if you can have situation : comparing whole null record to another null record (i think it is very rare).
But statement with Nz is perfectly safe:

Nz(t2.percentsnapshotspace,"NULL") = Nz(t1.percentsnapshotspace,"NULL")

If you can have nulls like i have in first post here - you can retrive another field (like ID in my case) or try another method.

Best wishes,
Jacek
 

Users who are viewing this thread

Top Bottom