Sometimes, we get so busy working on the complex that we forget simple techniques that are always at our fingertips. Here are some handy SQL tips.
1. Use the ESCAPE clause when you need to do a wildcard search that contains a percent sign (%) or underscore (_).
Select * From FILE1 WHERE FLD1 like 'ABC@%XX%' ESCAPE '@'
The @ is the escape character. It tells the pattern search to look for a string that begins with ABC%XX.
Use following syntax when searching for a quotation mark inside a character string:
SELECT * FROM MyFile WHERE LASTNAME like '%''%'
2. Don't write programs to define end of month date, determine day of week, or check for leap year. Each of those tasks can be done in one SQL statement. In the following examples, DUMMY is a one-record file I use to ensure that the initial SELECT returns a single value.
End of Month Calculation
WITH DD AS
(SELECT SUBSTR(CHAR('11/25/2002'),1,3)||'01'||
SUBSTR(CHAR('11/25/2002'),6,5) StrDate
FROM DUMMY)
SELECT DATE(StrDate)+(1 MONTH)-(1 DAY) End_of_Mth
FROM DD
Returns End_of_Mth=11/30/02
Day of Week Calculation
WITH WD AS
(SELECT DAYOFWEEK(DATE('11/01/2002')) DOW# FROM DUMMY)
SELECT (CASE
WHEN DOW#=1 then 'Saturday'
WHEN DOW#=2 then 'Monday'
WHEN DOW#=3 then 'Tuesday'
WHEN DOW#=4 then 'Wednesday'
WHEN DOW#=5 then 'Thursday'
WHEN DOW#=6 then 'Friday'
WHEN DOW#=7 then 'Sunday'
END) AS Day_of_Week
FROM WD
Returns Day_of_Week=Friday
Leap Year Check
WITH LY AS
(SELECT DAYOFYEAR('12/31/2000') LastDay FROM DUMMY)
SELECT (CASE
WHEN LastDay=366 Then 'Leap'
ELSE 'Not leap'
END) AS Leap_Year
FROM LY
Returns Leap_Year=Leap
3. Try the Print SQL Information (PRTSQLINF) command for programs with embedded SQL. It provides a report with useful information, including estimated query run-time.
4. Use the PROCESS(*SYN) parameter on the RUNSQLSTM command to perform syntax checking on a query without running the query.
5. Use caution with the RRN function on tables that contain deleted records. For example, the following SQL queries do not produce the same results if the table contains deleted records.
SELECT Max(RRN(MyFile)) FROM MyFile
SELECT Count(*) FROM MyFile 6. To sort tables with mixed-case data, use the UPPER function to convert mixed-case data to uppercase for sorting. For example:
SELECT UPPER(FLD1), FLD2, FLD3
FROM MyFile
ORDER BY UPPER(FLD1)
7. For OS/400 V5R1 and prior, SQL does not allow you to sort on unselected (non-displayed) columns. If you want to sort on a column, but not display it, you can hide the column by prefixing it with the hexadecimal value x'27'. In the following example, the data is sorted by the uppercased MODEL column, but model is not displayed.
SELECT MODEL, MAKE, MLS1, MLS2, x'27'||UPPER(MODEL) UPMOD
FROM ZD ORDER BY UPMOD | MODEL | MAKE | MLS1 | MLS2 | UPMOD | | Sentra | Nissan | 3 | 5 |
| | Sentra | Nissan | 3 | 6 |
| | Corolla | Toyota | 2 | 8 |
| | Corolla | Toyota | 4 | 7 |
| | Corolla | Toyota | 2 | 2 |
| | F150 | Ford | 3 | 3 |
| | Maxima | Nissan | 10 | 11 |
| | Maxima | Nissan | 3 | 5 |
| | 4Runner | Toyota | 2 | 1 |
|
Once the non-display attribute (x'27') has been used, no subsequent data (columns) will be visible until another displayable hex value (e.g., x'20') is specified for one of the subsequent columns.
If you direct the output of the SQL query to a database file, you will be able to see the hidden data by using the Display Physical File Member (DSPPFM) command. However, Query/400 and SQL won't display the hidden data.
Concatenation is not permitted for numeric columns. To include numeric columns in the column list without displaying them, use the CHAR function to convert numeric data to character format before concatenation.
If you want to allow users to view only certain rows and columns of a table, there are several ways to enforce this limitation. Creating a view is one of them, but it is not flexible enough. You can use the special register USER and table expressions to restrict the records a user can view. User "AB" is used in following examples.
Table ZX below has three columns. DEPT is a 10-byte character field. LS1 and LS2 are two-digit numeric values with no decimal places.
| DEPT | LS1 | LS2 | | A1 | 12 | 5 | | S21 | 4 | 7 | | HR | 3 | 1 | | IT | 12 | 2 | | A1 | 3 | 2 | | HR | 6 | 6 | | IT | 2 | 5 |
The following example shows how to limit user AB to viewing data for the IT department only.
With HD as (Select user usr, dept, ls1, ls2 From ZX)
Select dept, ls1, ls2 FROM HD
Where usr= 'AB' and dept ='IT'
Result : | DEPT | LS1 | LS2 | | IT | 12 | 2 | | IT | 2 | 5 |
You may add users by changing Where usr= 'AB' to WHERE usr in('AB', 'CD'...'ZZ').
The next example shows how to hide a numeric column from user AB, but display it to everyone else.
With HD as (Select user usr, dept, ls1, ls2 From ZX Where dept='IT')
Select usr, dept, ls1, (case when usr='AB' then x'27'||char(ls2)
when usr<>'AB' then char(ls2) end) ls2
From HD
Issak Brender can be reached by email at ukpi1b@mcpressonline.com.
| 
