1 Topic by nmrs

  • nmrs
  • Veteran
  • Offline
  • From: London
  • Registered: 22-11-2006
  • Posts: 387

Topic: WZD reports issue

Interesting issue, I have a predefined WZD report that reports all unverified records WHERE Taxon_Occurrence.Verified < '2' which works well. I want to remove certain species from this report output so I add the following to the WHERE:

AND ITN5.Taxon_List_Item_Key NOT IN ({RTLIK_list})

I reasonably expected the same output minus species in the NOT IN list but to my surprise they are reported. Now, if I change the statement to:

AND ITN5.Taxon_List_Item_Key IN ({RTLIK_list})

I only get records for the listed RTLIKs as expected!

So in effect IN is recognised but NOT IN isn't! Is this by design or a report wizard issue?

Les Evans-Hill
Senior Data Officer
Butterfly Conservation, Butterflies for the New Millennium and National Moth Recording Scheme

nmrs's Website

2 Reply by MikeWeideli

  • MikeWeideli
  • Administrator
  • Offline
  • From: Littlefield Consultancy
  • Registered: 05-07-2006
  • Posts: 2,645

Re: WZD reports issue

The report wizard is only running SQL so I suspect something in the SQL and not the Report Wizard which is causing the issue.  Without seeing the full SQL it is difficult to tell.  Taxon_Occurrence.Verified is a numeric field so the SQL should be Taxon_Occurrence.Verified < 2, but I can see this is having any effect on the IN clause.  Are there any 'OR' clauses  in the where statement ?

Mike Weideli

MikeWeideli's Website

3 Reply by nmrs (edited by nmrs 08-03-2013 09:11:56)

  • nmrs
  • Veteran
  • Offline
  • From: London
  • Registered: 22-11-2006
  • Posts: 387

Re: WZD reports issue

I've unquoted the numeric (silly mistake!) Here's the code:

<?xml version="1.0"?>
<Report>
<SQL>
SELECT Distinct Taxon_Occurrence.Taxon_Occurrence_Key AS Occurrence_Key, 'T' AS Type 
FROM ((((((((
Taxon_Occurrence
LEFT JOIN Sample ON Taxon_Occurrence.sample_Key = Sample.Sample_Key )
LEFT JOIN Taxon_Determination ON Taxon_Determination.Taxon_Occurrence_Key = Taxon_Occurrence.Taxon_Occurrence_Key and Taxon_Determination.Preferred = 1 )
LEFT JOIN Survey_Event ON Survey_Event.Survey_Event_Key = Sample.Survey_Event_Key )
LEFT JOIN Index_Taxon_Name ITN ON (ITN.Taxon_List_Item_Key = Taxon_Determination.Taxon_List_Item_Key and ITN.System_Supplied_Data = 1) )
LEFT JOIN Survey ON Survey.Survey_Key = Survey_Event.Survey_Key )
LEFT JOIN Index_Taxon_Name ITN2 ON ITN.Recommended_Taxon_List_Item_Key=ITN2.Recommended_Taxon_List_Item_Key )
LEFT JOIN Index_Taxon_Group ITG2 ON ITG2.Contained_List_Item_Key = ITN2.Taxon_List_Item_Key )
LEFT JOIN Index_Taxon_Name ITN4 ON ITN4.Taxon_List_Item_Key = ITG2.Taxon_List_Item_Key )
LEFT JOIN Index_Taxon_Name ITN5 ON ITN5.Recommended_Taxon_List_Item_Key = ITN4.Recommended_Taxon_List_Item_Key 
WHERE Survey.Survey_Key NOT IN (SELECT Survey_Key FROM User_Survey_Restriction USR WHERE  USR.Name_Key = 'BB00000400000002')
AND ITN5.Taxon_List_Item_Key NOT IN ('NHMSYS0000495147')
AND Taxon_Occurrence.Verified &#60; 2
</SQL>
<additional_filters>
<confidential state="1"/>
<unchecked state="1"/>
<invalid state="1"/>
<zero_abundance state="1"/>
</additional_filters>
<Attributes>
<Attribute visible="True" type="Standard" key="NBNSYS0000000053" position="23"/>
<Attribute visible="True" type="Standard" key="NBNSYS0000000031" position="22"/>
<Attribute visible="True" type="Standard" key="NBNSYS0000000018" position="21"/>
<Attribute visible="True" type="Standard" key="JNCCDEV100000005" position="20"/>
<Attribute visible="True" type="Standard" key="NBNSYS0000000050" position="19"/>
<Attribute visible="True" type="Standard" key="JNCCDEV100000014" position="18"/>
<Attribute visible="True" type="Standard" key="NBNSYS0000000048" position="17"/>
<Attribute visible="True" type="Standard" key="NBNSYS0000000032" position="16"/>
<Attribute visible="True" type="Standard" key="JNCCDEV100000007" position="15"/>
<Attribute visible="True" type="Standard" key="NBNSYS0000000063" position="14"/>
<Attribute visible="True" type="Standard" key="NBNSYS0000000049" position="13"/>
<Attribute visible="True" type="Standard" key="BB00000400000002" position="12"/>
<Attribute visible="True" type="Standard" key="BB00000400000001" position="11"/>
<Attribute visible="True" type="Standard" key="SRA0000400000021" position="10"/>
<Attribute visible="True" type="Standard" key="SRA0000400000020" position="9"/>
<Attribute visible="True" type="Standard" key="NBNSYS0000000030" position="8"/>
<Attribute visible="True" type="Standard" key="SRA0000400000022" position="7"/>
<Attribute visible="True" type="Standard" key="NBNSYS0000000029" position="6"/>
<Attribute visible="True" type="Standard" key="NBNSYS0000000027" position="5"/>
<Attribute visible="True" type="Standard" key="NBNSYS00000000BD" position="4"/>
<Attribute visible="True" type="Standard" key="NBNSYS0000000040" position="3"/>
<Attribute visible="True" type="Standard" key="NBNSYS0000000038" position="2"/>
<Attribute visible="True" type="Standard" key="NBNSYS0000000070" position="1"/>
<Attribute visible="True" type="Standard" key="NBNSYS0000000071" position="0"/>
</Attributes>
</Report>

This should list all unverified records but exclude all unverified Grey Dagger (Acronicta psi) records but it ignores the NOT IN clause and lists all unverified records irrespective of the NOT IN clause. Changing NOT IN to IN lists unverified records for that species only which is correct.

Les Evans-Hill
Senior Data Officer
Butterfly Conservation, Butterflies for the New Millennium and National Moth Recording Scheme

nmrs's Website

4 Reply by MikeWeideli

  • MikeWeideli
  • Administrator
  • Offline
  • From: Littlefield Consultancy
  • Registered: 05-07-2006
  • Posts: 2,645

Re: WZD reports issue

I think that this happens because ITN5 contains all possible Taxon_List_Item keys which could relate to the Taxon_List_Item_Key of the determination . When you use IN  only those which relate to the keys are selected, when you use  NOT IN  you are still left with all the other TLI keys which relate to the taxa, even though you will not have used them.   It might require a bit of playing about with to get the result you require as it will depend a bit on the lists you use for input.

Try ITN.Taxon_List_Item_Key NOT IN ('NHMSYS0000495147') or ITN5.Recommended_Taxon_List_Item_Key NOT IN ('NHMSYS0000495147')


Mike

Mike Weideli

MikeWeideli's Website

5 Reply by nmrs

  • nmrs
  • Veteran
  • Offline
  • From: London
  • Registered: 22-11-2006
  • Posts: 387

Re: WZD reports issue

Many thanks Mike! I'll give this a go and will report back.

Les Evans-Hill
Senior Data Officer
Butterfly Conservation, Butterflies for the New Millennium and National Moth Recording Scheme

nmrs's Website