Query Fragment
SELECT PC_PARCEL.P_ID, PC_PARCEL.TAX_MAP, PC_ADDRESS.FORMATED_ADDRESS, PC_PARCEL.EFFECTIVE_YEAR, PC_PARCEL.INACTIVE_YEAR
FROM PC_PARCEL left outer join PC_ADDRESS on PC_PARCEL.P_ID=PC_ADDRESS.P_ID AND PC_ADDRESS.LOC_SEQ=0
WHERE (PC_ADDRESS.CIVIC IS NULL OR (PC_ADDRESS.CIVIC>=@pcfhouseno_0 AND PC_ADDRESS.CIVIC<=@pcthouseno_1))
AND (ISNULL(PC_ADDRESS.STREET,”) = ISNULL(@pcstreetname_2PC_ADDRESS.STREET,”))
AND (ISNULL(PC_ADDRESS.SUFFIX,”) = ISNULL(@pcstreettype_0PC_ADDRESS.SUFFIX,”))
In the above query, the SQL ISNULL() function is used, so that when searching specified columns in the table, should a NULL character be encountered, the ISNULL() function will equate it with zero (0). In the above example a modification in the form of double square brackets “[[ ]]”, have been added to the query. This modification has been made for the DAB. When When the DAB encounters double-brackets it will immediately use the data type of the TABLE.COLUMN combination that are within the brackets to declare the leftmost variable that is outside of the brackets, in our case the ISNULL.
B. NULL Characters Alternate Method
As stated in the previous example, the database entry can have three (3) states, TRUE, FALSE, or NULL. A check box in a user interface can have two (2) states, TRUE (-1), or FALSE (0). We would need to code our query to equate a NULL with a zero (0). The following example demonstrates an alternate method of handling the NULL character.
Query Fragment
AND(PC_PARCEL.NON_PARCEL_FLAG=@pcnonparcel_0 OR ( 0=@pcnonparcel_0 AND PC_PARCEL.NON_PARCEL_FLAG IS NULL))
The above statement is written to handle the NULL by equating NON_PARCEL_FLAG with NULL.
The above example statement can also be recreated with the double brackets method. The statement would be as follows:
AND (PC_PARCEL.NON_PARCEL_FLAG=@pcnonparcel_0PC_PARCEL.NON_PARCEL_FLAG,”)
C. Linking Interface Selections
We would like to present the user with a search option that will include inactive parcels. Unfortunately in the database there is no field in the database that will tell you that a parcel is inactive. To be able to link something that does not exist in the database to something that does, and get a TRUE or FALSE value that we can then manipulate.
The PC Inactive Parcel field does not exist in the database but we want the user to use the check box, it will have to be linked to one that is of the same type. i.e. integer, float, text, small int, etc.
Create a dummy condition
This is a condition that will always return a value of TRUE, this also declares a variable based upon a column.
AND (PC_PARCEL.NON_PARCEL_FLAG=@pcinactiveparce_0 OR 1=1)
To declare the pcInactiveParcel object, we will link it to a field that is of the same type as that required by the checkbox, i.e PC_NON_PARCEL_FLAG. The DAB will now be able declare pcInactiveParcel as valid.
The conditions are now set for the two states of the checkbox option…
AND (( 0=@pcinactiveparce_0 AND PC_PARCEL.EFFECTIVE_YEAR<= year id AND
PC_PARCEL.INACTIVE_YEAR > year id )
OR (-1 = @pcinactiveparce_0 ))
If the Include Inactive Parcel option is not checked, i.e. FALSE (0), or Include Inactive Parcel is checked, TRUE(-1).
Following the above steps, we are able to declare a variable based upon a column. Based upon the fact that the variable does not exist, the Data Access Block (DAB) is being directed not to look directly at the column, but rather it is being told what to expect.
Related Topics
Query Types
Advanced SQL Queries
Keywords
Best Practices for SQL Queries
Special Cases
SQL Syntax rules for GNA – 100-Queries
Loading...