I have a weird problem.
EXEC SQL SELECT
AVG(LER)
INTO :LER-MIN
FROM CENSUS.WORLDIMR
* WHERE CENSUS.WORLDIMR.COUNTRY LIKE 'L%'
END-EXEC.
This is giving out 72.2 but when I use
WHERE CENSUS.WORLDIMR.COUNTRY LIKE 'L%'
it gives out 00.0.
However trying this in SPUFI:
select avg(ler)
from census.worldimr
where census.worldimr.country like 'L%';
Is giving out 71.33333333333.
What did I do wrong?
This is the program:
000001 PROCESS SQL
000002 IDENTIFICATION DIVISION.
000003 PROGRAM-ID. CBL7.
000004 *--------------------
000005 ENVIRONMENT DIVISION.
000006 *--------------------
000007 CONFIGURATION SECTION.
000008 INPUT-OUTPUT SECTION.
000009 FILE-CONTROL.
000010 SELECT P3OUT ASSIGN TO UT-S-P3OUT.
000011
000012 DATA DIVISION.
000013 *-------------
000014 FILE SECTION.
000015 FD P3OUT
000016 RECORD CONTAINS 80 CHARACTERS
000017 LABEL RECORDS ARE OMITTED
000018 RECORDING MODE F
000019 DATA RECORD IS PRTREC.
000020
000021 01 PRTREC.
000022 02 LER-PRT PIC 99.9.
000023 02 FILLER PIC X(76).
000024
000025 WORKING-STORAGE SECTION.
000026 01 VARS.
000027 10 LER-MIN PIC S9(5)V9(1) USAGE COMP-3.
000028
000029 EXEC SQL INCLUDE SQLCA END-EXEC.
000030 ******************************************************************
000031 * TABLE(CENSUS.WORLDIMR) *
000032 ******************************************************************
000033 EXEC SQL DECLARE CENSUS.WORLDIMR TABLE
000034 ( REGION CHAR(5) NOT NULL,
000035 COUNTRY CHAR(30) NOT NULL,
000036 YR CHAR(4) NOT NULL,
000037 IMR DECIMAL(5, 1) NOT NULL,
000038 IMRM DECIMAL(5, 1) NOT NULL,
000039 IMRF DECIMAL(5, 1) NOT NULL,
000040 IMR1_4 DECIMAL(5, 1) NOT NULL,
000041 IMR1_4M DECIMAL(5, 1) NOT NULL,
000042 IMR1_4F DECIMAL(5, 1) NOT NULL,
000043 IMR_5 DECIMAL(5, 1) NOT NULL,
000044 IMR_5M DECIMAL(5, 1) NOT NULL,
000045 IMR_5F DECIMAL(5, 1) NOT NULL,
000046 LER DECIMAL(5, 1) NOT NULL,
000047 LERM DECIMAL(5, 1) NOT NULL,
000048 LERF DECIMAL(5, 1) NOT NULL
000049 ) END-EXEC.
000050 ******************************************************************
000051 * COBOL DECLARATION FOR TABLE CENSUS.WORLDIMR *
000052 ******************************************************************
000053 01 WORLDIMR.
000054 10 REGION PIC X(5).
000055 10 COUNTRY PIC X(30).
000056 10 YR PIC X(4).
000057 10 IMR PIC S9(4)V9(1) USAGE COMP-3.
000058 10 IMRM PIC S9(4)V9(1) USAGE COMP-3.
000059 10 IMRF PIC S9(4)V9(1) USAGE COMP-3.
000060 10 IMR1-4 PIC S9(4)V9(1) USAGE COMP-3.
000061 10 IMR1-4M PIC S9(4)V9(1) USAGE COMP-3.
000062 10 IMR1-4F PIC S9(4)V9(1) USAGE COMP-3.
000063 10 IMR-5 PIC S9(4)V9(1) USAGE COMP-3.
000064 10 IMR-5M PIC S9(4)V9(1) USAGE COMP-3.
000065 10 IMR-5F PIC S9(4)V9(1) USAGE COMP-3.
000066 10 LER PIC S9(4)V9(1) USAGE COMP-3.
000067 10 LERM PIC S9(4)V9(1) USAGE COMP-3.
000068 10 LERF PIC S9(4)V9(1) USAGE COMP-3.
000069
000070 PROCEDURE DIVISION.
000071 *------------------
000072 EXEC SQL SELECT
000073 AVG(LER)
000074 INTO :LER-MIN
000075 FROM CENSUS.WORLDIMR
000076 WHERE CENSUS.WORLDIMR.COUNTRY LIKE 'L%'
000077 END-EXEC.
000078 OPEN OUTPUT P3OUT.
000079 MOVE LER-MIN TO LER-PRT.
000080 WRITE PRTREC.
000081 CLOSE P3OUT.
000082 STOP RUN.
Manuals are great things, especially when a search-engine can hit straight into them:
db2 sql like
The first hit should take you to a manual presented to your through the IBM Knowledge Centre. It is for DB2 11.0, so you should make an obvious change if you are not using 11.0.
Reading, you'll come to:
If the pattern is specified in a fixed-length string variable, any trailing blanks are interpreted as part of the pattern. Therefore, it is better to use a varying-length string variable with an actual length that is the same as the length of the pattern. If the host language does not allow varying-length string variables, place the pattern in a fixed-length string variable whose length is the length of the pattern.
For more information about the use of host variables with specific programming languages, see Host variables.
The language you are using, COBOL, does not allow varying-length string variables (one of the things which makes it so fast, so don't complain).
So, your constant (literal in COBOL) for the LIKE needs to be the same length as the column involved, and all the additional positions in the literal need to be % as well (at least up to the end of the actual maximum length of any data which will match the LIKE, strictly).
Or, use a COBOL host-variable of that length, again padded with trailing %s instead of trailing spaces.
Or use a varchar-like host-variable (a COBOL table with items contain the length of data as a two-byte binary, with a value of 2, and then your L%).
01 varchar-host-variable.
05 vhv-length COMP-5 PIC 9(4).
05 vhv-data PIC XX.
Even if the literal worked as you expected (without reading the manual) it is generally a bad idea to have "significant" literals in the PROCEDURE DIVISION. Second reason to use a host-variable.