10. SQL Tips

SQL Tips

Case Sensitivity

To make SQL searches not case sensitive use the UPPER keyword. For example:

SELECT * FROM yourtable WHERE UPPER(lname) LIKE UPPER(TRIM(CAST(? as CHAR(20))))

The field name was a CHAR(20) and we wanted to search for it using any number of known letters in the word. We need to use a TRIM command, because, for example, if you search for all last names starting with an 'S', it will take the 'S' and append zeros to fill up the CHAR(20) space unless it matches it exactly. So, the TRIM takes out these extra spaces and fillers.

Optional Values

To allow optional entry of certain values in a SQL search statement, use the SQL VALUE clause. For example:

SELECT * FROM Lib.yourtable WHERE firstname = VALUE(CAST(? as CHAR(20)), firstname)

AND lastname = VALUE(CAST(? as CHAR(20)), lastname)

In this case, you are searching your table by last name and first name using the VALUE function which basically is a function that returns the first value that is not null. For example, we could input a first name and last name, one separate from the other or none at all and it will search according to that. If you enter none of these, it will then show the entire file. If you only enter the first name, it will search for all of the records that have the same first name and will not use the last name field as a parameter for searching. This allows you to have one search operation that can have many different fields to search by without depending on each other on having a value.

NOTE: Some databases allow usage of COALESCE without casting:

SELECT * from X.Y WHERE field1 = COALESCE ( ?, field1)

TODO: Add details on COALESCE and simplified SQL

NOTE: You may also use the LIKE function with the VALUE function to make the search even more powerful; however, with LIKE, you again may need to use the TRIM command.

Conditional Associations

The SQL CASE keyword can be used to make an association dependent on a columns value. In the following example:

SELECT

CASE WHEN RunStatusID = 0

THEN '<p><span style="background-color:LimeGreen; color:black;">START Processing</span></p>'

ELSE NULL END AS D_Start_Processing, ............

The derived field "D_Start_Processing" has an association but will only show text if RunStatusID = 0.

Tags: Optional selection, Show All Records if no search field