Where tag (Advanced)

<Where> tag (Advanced)

When the report is run, contents within the <Where> tag are evaluated and added to any SQL which is within the <SQL> tag, but not within a <Where> tag. The important point is that SQL within a <Where> tag does not have to be simply a WHERE clause. They can contain a range of SQL including creating and populating temporary tables. If <Condition> tags are placed within this SQL they will be evaluated where they are placed. This means that <Condition> tags can be used to derive the WHERE clauses used to populate or update temporary tables. They can also be coerced into populating temporary tables with user entered parameters which can then be used in UDF'.s (see UDF' and Parameters)

A new feature in V6.13 allows the <Where> clause to be used more than once. This is done by placing {WHERE} at the points where the statement is required. It is mainly intended for Batch Updates and would only be of value in XML reports in a situation where a temporary table and the final Select statement needed the same WHERE clause.

Example 1 - Standard Construction

In the example the SQL statement ( SELECT Item_Name FROM Location_Name ) is outside of the <Where> tags. Which of the <Where> tags is executed depends on the way in which the user opens the report. If they use the main report menu then the default <Where> is used. If they enter via one of the data entry screens (in this example Location) then the appropriate <Where> tag is executed and the key of the currently selected record passed to the tag as a variable %s. Example 1a and Example 1b below execute exactly the same even though the SQL statement in the second example has been positioned after the <Where> tags.

Example 1a

<?xml version='1.0' ?>

         <Condition field= "Location_key" operator="equal" type="Location" entrycount="-1" name="Location" />
     </Where>
     <Where keytype="Location">
          WHERE
           Location_Key = ' %s'
     </Where>

</SQL>
</CustomReport>

Example 1b

<?xml version='1.0' ?>

SELECT Item_Name FROM Location_Name

</SQL>
</CustomReport>

Example 2 - Different SQL within each <Where> tag

When constructing the report any SQL which is common to all the <Where> tags can be placed outside of the <Where> tags, but within the SQL tag. In the following example what is being selected (in red) is common to both <Where> tags and is outside of the tags. The FROM clause and WHERE clause is different depending on the <Where> tag.

<?xml version='1.0' ?>

<CustomReport title="Ex1 ExampleReport" menupath="LC\Example reports"
description="" >

<SQL>
    SELECT LN.Item_Name
     <Where keytype="Default">
          FROM Location_Name  LN
          INNER JOIN Location L ON L.Location_Key = LN.Location_Key
         INNER JOIN Location_Type LT ON LT.Location_type_key = L.Location_Type_key
          WHERE
          LT.Short_Name = 'Vice-county'
          AND
        <Condition field= "LN.Item_Name" operator="like" type="text" entrycount="1" name="Location Name(wild card)" />
     </Where>
     <Where keytype="Location">
          FROM Location_Name  LN
          WHERE
           Location_Key = ' %s'
     </Where>

</SQL>
</CustomReport>

Example 3 - Using with Temporary tables

When creating temporary tables it is most likely that these will need a <Condition> tag to populate them. The <Condition> tag is child tag of the <Where> tag so must be within it. For this reason the creation of the temporary table can be within the <SQL> tags, but the populating of the temporary table(s) from the first <Condition> tag onwards has to be within the <Where> tags. This is illustrated in Example 3a, If a second temporary table is required then the creation of this has to be in every <Where> tag. This is illustrated in example 3b.where the creation of temporary table #MainTemp and populating it is repeated in both <Where> tags. (in pink). For clarity it is sometimes easier to put all the SQL within each <Where> tag, which is perfectly acceptable, though it does mean maintaining more code.

Please note that all these examples are to illustrate various points and although they will work they are not particularly useful.

Example 3a

<?xml version='1.0' ?>

     <Where keytype="Default">
      <Condition field= "LN.Item_Name" operator="like" type="text" entrycount="1" name="Location Name(wild card)" />
      SELECT * FROM #TempL

</Where>
     <Where keytype="Location">
           LN.Location_Key = ' %s'
           SELECT * FROM #TempL
     </Where>

</SQL>
</CustomReport>

Example 3b

<?xml version='1.0' ?>

     <Where keytype="Default">
      <Condition field= "LN.Item_Name" operator="like" type="text" entrycount="1" name="Location Name(wild card)" />
     CREATE TABLE #MainTemp (Location_Name VARCHAR (100))
     INSERT INTO #MainTemp (Location_Name)
     SELECT LN.Item_name FROM
     Location_Name LN
     INNER JOIN #TempL
     ON #TempL.Location_key = LN.Location_Key

SELECT * FROM #MainTemp

DROP TABLE #TempL
DROP TABLE #MainTemp

</Where>

<Where keytype="Location">
           LN. Location_Key = ' %s'
           CREATE TABLE #MainTemp (Location_Name VARCHAR (100))
           INSERT INTO #MainTemp (Location_Name)
          SELECT LN.Item_name FROM
          Location_Name LN
          INNER JOIN #TempL
          ON #TempL.Location_key = LN.Location_Key

SELECT * FROM #MainTemp

DROP TABLE #TempL
DROP TABLE #MainTemp

</Where>

</SQL>
</CustomReport>