Midrange Systems Design - IBM i5 tm Software

Support

Sales

Home

I like LIKE .

The following is an order search selection screen.

If you put in JOHN SMITH then a program could generate the following SQL.

Select * from Order where OrCustNam = ‘JOHN SMITH’

This will display customers with the name John Smith.

Alternatively, the program could generate the SQL statement as follows:

Select * from Order where OrCustNam like ‘JOHN SMITH%’

This will display all of the customers that start with John Smith. Notice the % after the john smith was not keyed; it was supplied by the program. If there is an index over the order file by customer, then this SQL will have sub second response time, just like the first one did. On the above screen, the customer, Purchase Order, Descriptor, buyer and phone number use the SQL like keyword.

If the operator typed in JOHN, it would select all customers whose name starts with john.

Select * from Order where OrCustNam like ‘JOHN%’

Note, the program generated SQL statement (in green) didn’t change. Performance is still fast since the index is used.

The operator could also put in wild cards. Example, type in %SMITH to get all of the customers who’s last name is smith.

Select * from Order where OrCustNam like ‘%SMITH%’

Note the first % was typed by the operator, but the second one was supplied by the program. This query will run slow because all records will have to be interrogated. But it will give the operator the needed information fairly quickly. Look at the index advisor from operations navigator. It will probably recommend you create a special kind of index (non logical file) to speed this up.

You can also put in multiple wild cards. Example, type in 405%793%1234.

Select * from Order where OrPhone like ‘405%793%1234%’

This would find the following phone numbers:

4057931234

405-793-1234

405.793.1234 x22

On the order number, you could use an IN instead of equal. This would allow the operator to key in one or multiple order numbers as follows 40222, 40228, 51711

Select * from Order where OrOrder in (40222, 40228, 51711)

On the above customer search screen, you can type in the customer name or the customer number into the one search field. This saves screen real estate.

Sql = ‘Select * from Order where’;

SqlAnd = *blank;

…

If scrCustomer <> *blank;

If scrCustomer > *zero;

Sql = %trim(Sql) + SqlAnd + ‘ OrCust =’

+ %dec(%Trim(ScrCustomer):7:0);

Else;

Sql = %trim(Sql) + SqlAnd + ‘ OrCustNam like ’

+ quote;

+ ScrCustomer

+ quote;

EndIf;

SqlAnd = ‘ and ‘;

EndIf;

You can store your customers names in mixed case (upper and lower) and have indexes built in upper case. Read about *LangIdShr. You can specify this on Create xx file, SQL defaults by user, RPG compile and even a system value.

Happy SQLs to you, until we meet again.

Steven Easton 7/2007