CODEWITHSUNDEEP
sqlms-access
pja
pja

Reputation: 125

WHERE Is Not Null Query

I am using a very complex UNION ALL query to parse out information from an XML import into an existing Access table. I am running into an issue is when I am attempting to write the query to only populate the table if the field is not null. I have tried using an IIF statement at the beginning and I many different iterations various Null statements but all throw errors except this one which doesn't do anything

 select SiteVisitCode + '-'  AS Q_SiteVisitCode, IIf ([Sample_Collection_Method_ID] = "SED-CORE-C", SiteVisitCode + '-CC / ' + Station_Name,  IIf ([Sample_Collection_Method_ID] = "CHLPHL-1-C", SiteVisitCode + '-CT / ' + Station_Name,  IIf ([Sample_Collection_Method_ID] = "HOOP-C", SiteVisitCode + '-CH / ' + Station_Name, SiteVisitCode + '-C(A) / ' + Station_Name))),  IIf ([Sample_Collection_Method_ID] = "SED-CORE-C", SiteVisitCode + '-CC ', IIf ([Sample_Collection_Method_ID] = "CHLPHL-1-C", SiteVisitCode + '-CT', IIf ([Sample_Collection_Method_ID] = "HOOP-C", SiteVisitCode + '-CH', SiteVisitCode + '-C(A)'))), 'S-ROUTINE' as Activity_Type, IIf ([Sample_Collection_Method_ID] = "SED-CORE", 'Sediment', IIf ([Sample_Collection_Method_ID] = "SED-CORE-C", 'Sediment','other')), IIF ([COMP]="TRUE", Right([TransectA],Len([TransectA])-InStrRev([TransectA],"/"))+'-C', Right([TransectA],Len([TransectA])-InStrRev([TransectA],"/"))) AS Sample_Collection_Method_ID, '' AS Activity_Comment, 'CLPH' as DEQ_SampleTypeID, 'A' as Activity_Transect, Station_Visit_Date as Activity_Start_Date, Time as Activity_Start_Time
 FROM tblSiteVisit
 WHERE [transectA] is Not Null
 UNION ALL 
 select SiteVisitCode + '-'  AS Q_SiteVisitCode, IIf ([Sample_Collection_Method_ID] = "SED-CORE-C", SiteVisitCode + '-CC / ' + Station_Name,  IIf ([Sample_Collection_Method_ID] = "CHLPHL-1-C", SiteVisitCode + '-CT / ' + Station_Name,  IIf ([Sample_Collection_Method_ID] = "HOOP-C", SiteVisitCode + '-CH / ' + Station_Name, SiteVisitCode + '-C(P) / ' + Station_Name))),  IIf ([Sample_Collection_Method_ID] = "SED-CORE-C", SiteVisitCode + '-CC ', IIf ([Sample_Collection_Method_ID] = "CHLPHL-1-C", SiteVisitCode + '-CT', IIf ([Sample_Collection_Method_ID] = "HOOP-C", SiteVisitCode + '-CH', SiteVisitCode + '-C(P)'))), 'S-ROUTINE' as Activity_Type, IIf ([Sample_Collection_Method_ID] = "SED-CORE", 'Sediment', IIf ([Sample_Collection_Method_ID] = "SED-CORE-C", 'Sediment','other')), IIF ([COMP]="TRUE", Right([TransectP],Len([TransectP])-InStrRev([TransectP],"/"))+'-C', Right([TransectP],Len([TransectP])-InStrRev([TransectP],"/"))) AS Sample_Collection_Method_ID, '' AS Activity_Comment, 'CLPH' as Q_SampleTypeID, 'P' as Activity_Transect, Station_Visit_Date as Activity_Start_Date, Time as Activity_Start_Time
 FROM tblSiteVisit
 WHERE [transectP] is Not Null
 ;

In the example below the 2nd entry should not exist as transect P is null:

Q_SiteVisitCode|Sample_ID|Activity_ID|Activity_Type|Medium|Activity_Start_Date|Activity_Start_Time|Sample_Collection_Method_ID|Activity_Transect|DEQ_SampleTypeID|Activity_Comment

 test123-|test123-CT / Fish Hatchery|test123-CT|S-ROUTINE|other|12/26/2013|1058|CHLPHL-1-C|A|CLPH   
 test123-|test123-C(P) / Fish Hatchery|test123-C(P)|S-ROUTINE|other|12/26/2013|1058|-C|P|CLPH

Any assistance would be greatly appreciated

Upvotes: 1

Views: 271

Answers (3)

HansUp
HansUp

Reputation: 97101

If transectA is text datatype and you want your query to ignore rows with Null or zero-length strings in that column ...

WHERE Len([transectA]) > 0

Upvotes: 1

Chains
Chains

Reputation: 13157

You know that one of your SELECTs are pulling the unwanted record in -- run each seperately to find out which one. But otherwise, you can explicitly exclude unwanted records like this:

In your first SELECT:

WHERE [transectA] NOT IN (null,'') AND [transectP] in (null, '')

In your second SELECT:

WHERE [transectP] NOT IN (null,'') AND [transectA] in (null,'')

FWIW -- you might think about using SWITCH statements instead of so many nested IIF's -- easier to read & troubleshoot.

Upvotes: 1

Adam
Adam

Reputation: 480

If the two queries that you're UNIONing together are meant to represent mutually exclusive sets, then you need to indicate that.

In each query you select from the same table, but with a different where clause. In the first query you say where transectA is not null. TrasnectP can still be null here, and those records will be pulled in. Vice versa for the 2nd query.

As others have said - write this as a single query without the union, but use a case statement to check if transecta or tranectp is null and transform that field accordingly.

Upvotes: 1

Related Questions