1. Use the ESCAPE clause when you need to do a wildcard search that contains a percent sign (%) or underscore (_).
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:
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
(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
(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
(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 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:
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.
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.
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.
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
Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.
IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn:
LATEST COMMENTS
MC Press Online